课程 › 第 10 章 › 实验二
第 10 章 · 实验二 · 在第四节之后

导出和导入文本文件

用 SELECT … INTO OUTFILE 把学生表导出为文本文件，再用 LOAD DATA INFILE 导入一张新表。

环　境

MySQL 8.0 和 MySQL Workbench，机房电脑已装好。所有语句在 Workbench 的 SQL 编辑区中执行。

执　行

Ctrl+Enter 执行光标所在的一句，Ctrl+Shift+Enter 执行全部。Mac 上把 Ctrl 换成 ⌘。

注　意

输入法切到英文。表名、字段名不加引号，字符串值才用单引号。

本次实验的顺序　后一个实验用到前一个的结果

  1. 实验 2-1查看允许导出的文件夹
  2. 实验 2-2导出学生表
  3. 实验 2-3指定分隔符导出
  4. 实验 2-4导入到新表
  5. 实验 2-5重复导入与行结束符
第一步

数据准备（必做）

与实验一相同的脚本，执行后 teaching 库回到初始状态。

整段复制，全部执行下载 .sql
-- 第 10 章实验数据：重建教材 teaching 库，删除本章实验建的库
-- 在 Workbench 中用 root 连接整段执行。四次实验共用，想从头再做时重新执行即可。
DROP DATABASE IF EXISTS teaching_copy;
DROP DATABASE IF EXISTS teaching_empty;
DROP DATABASE IF EXISTS teaching;
CREATE DATABASE teaching DEFAULT CHARACTER SET utf8mb4;
USE teaching;

-- 课程表
CREATE TABLE c (
  cno CHAR(4) NOT NULL,
  cn VARCHAR(30) NOT NULL,
  ct INT,
  PRIMARY KEY (cno)
);
-- 学生表
CREATE TABLE s (
  sno CHAR(4) NOT NULL,
  sn VARCHAR(20) NOT NULL,
  sex CHAR(2),
  age INT,
  maj VARCHAR(30),
  dept VARCHAR(30),
  PRIMARY KEY (sno)
);
-- 教师表
CREATE TABLE t (
  tno CHAR(4) NOT NULL,
  tn VARCHAR(20) NOT NULL,
  sex CHAR(2),
  age INT,
  prof VARCHAR(20),
  maj VARCHAR(30),
  dept VARCHAR(30),
  sal DECIMAL(8,2),
  PRIMARY KEY (tno)
);
-- 选课表
CREATE TABLE sc (
  sno CHAR(4) NOT NULL,
  cno CHAR(4) NOT NULL,
  score DECIMAL(5,2),
  PRIMARY KEY (sno, cno),
  FOREIGN KEY (sno) REFERENCES s (sno),
  FOREIGN KEY (cno) REFERENCES c (cno)
);
-- 授课表
CREATE TABLE tc (
  tno CHAR(4) NOT NULL,
  cno CHAR(4) NOT NULL,
  tcdate DATE,
  PRIMARY KEY (tno, cno),
  FOREIGN KEY (tno) REFERENCES t (tno),
  FOREIGN KEY (cno) REFERENCES c (cno)
);

INSERT INTO c VALUES
('c1','Java程序设计',40), ('c2','程序设计基础',48), ('c3','线性代数',48),
('c4','数据结构',64), ('c5','数据库系统',56), ('c6','数据挖掘',32),
('c7','高等数学',60), ('c8','控制理论',32);

INSERT INTO s VALUES
('s1','王彤','女',18,'计算机','信息学院'),
('s2','苏乐','男',20,'信息','信息学院'),
('s3','林欣怡','女',19,'信息','信息学院'),
('s4','陶然','男',19,'自动化','工学院'),
('s5','魏立','男',17,'数学','理学院'),
('s6','何欣荣','女',21,'计算机','信息学院'),
('s7','赵琳琳','女',19,'数学','理学院'),
('s8','李轩','男',18,'自动化','工学院');

INSERT INTO t VALUES
('t1','刘杨','男',38,'教授','计算机','信息学院',3800.00),
('t2','石丽','女',28,'讲师','计算机','信息学院',2800.00),
('t3','顾伟','男',35,'副教授','计算机','信息学院',3200.00),
('t4','赵礼','男',45,'教授','自动化','工学院',4267.90),
('t5','赵希希','女',33,'副教授','数学','理学院',3332.67),
('t6','张刚','男',30,'讲师','计算机','工学院',2600.00);

INSERT INTO sc VALUES
('s1','c1',90.50), ('s1','c2',88.00),
('s2','c4',70.00), ('s2','c5',57.00), ('s2','c6',81.50), ('s2','c7',NULL),
('s3','c1',75.00), ('s3','c2',76.50), ('s3','c4',85.00),
('s4','c1',93.00), ('s4','c2',82.00), ('s4','c6',NULL), ('s4','c7',68.00),
('s5','c2',79.00), ('s5','c7',84.00),
('s7','c3',77.00), ('s7','c5',62.00), ('s7','c7',100.00),
('s8','c2',66.00), ('s8','c3',96.00);

INSERT INTO tc VALUES
('t1','c1','2021-09-03'), ('t1','c2','2021-09-04'),
('t2','c5','2021-03-06'), ('t2','c6','2021-09-06'),
('t3','c2','2021-03-08'), ('t3','c4','2021-09-08'),
('t4','c3','2021-03-10'),
('t5','c7','2021-09-12'), ('t5','c8','2021-03-15');

SHOW TABLES;
2-1

查看允许导出的文件夹

讲义 · 用 SELECT … INTO OUTFILE 导出
目的
查出变量 secure_file_priv 指定的文件夹，导出和导入文件都只能用这个文件夹。
前提
已执行数据准备，当前库是 teaching。

ProgramData 是隐藏文件夹。按 Win+R，粘贴 C:\ProgramData\MySQL\MySQL Server 8.0\Uploads，按 Enter 键即可打开。

  1. 查看变量 secure_file_priv 的值
  2. 在资源管理器中打开这个文件夹

参考答案

可以直接复制到 Workbench 执行
SHOW VARIABLES LIKE 'secure_file_priv';

语句解释

secure_file_priv服务器只允许在这个文件夹中读写文件。它在服务启动时设定，SET 语句不能修改
值为空不限制文件夹
值为 NULL不允许导出和导入文件
执行后看：值为 C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\，以本机的结果为准，后面的实验都用这个文件夹。
2-2

导出学生表

讲义 · 用 SELECT … INTO OUTFILE 导出
目的
用 SELECT … INTO OUTFILE 把 s 表导出为文本文件，比较导出到 D 盘和导出到 Uploads 文件夹的结果。
前提
已完成实验 2-1。

有报错的语句要逐句执行：光标放在这一句上，按 Ctrl+Enter。

  1. 把 s 表导出到 D:/teaching_s.txt
  2. 把 s 表导出到 Uploads 文件夹中的 s_1.txt
  3. 用记事本打开 s_1.txt

参考答案

可以直接复制到 Workbench 执行
-- 导出到 D 盘，报错 1290
SELECT * FROM s INTO OUTFILE 'D:/teaching_s.txt';
-- 导出到 secure_file_priv 指定的文件夹，路径中的斜线写成 /
SELECT * FROM s
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/s_1.txt';

语句解释

INTO OUTFILE查询结果不显示，而是写入服务器上的一个文件
报错 1290文件不在 secure_file_priv 指定的文件夹中，服务器拒绝执行
C:/ProgramData/…Windows 的路径写在 SQL 语句中时，\ 改为 /
执行后看：第一句报错 1290。第二句显示 8 row(s) affected。s_1.txt 中一个学生一行，字段之间用制表符分隔，没有列名，也没有建表语句。
2-3

指定分隔符导出

讲义 · 用 SELECT … INTO OUTFILE 导出
目的
按例 10-13 的要求导出：字段之间用逗号分隔，字符串用双引号括起来。再用同一个文件名导出一次。
前提
已完成实验 2-2。
  1. 把 s 表导出到 s_2.txt，字段用逗号分隔，字符串加双引号
  2. 用记事本打开 s_2.txt
  3. 再执行一次同样的导出语句

参考答案

可以直接复制到 Workbench 执行
SELECT * FROM s
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/s_2.txt'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"';
-- 同一个文件名再导出一次，报错 1086
SELECT * FROM s
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/s_2.txt'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"';

语句解释

FIELDS TERMINATED BY ','字段之间用逗号分隔，不写时用制表符
OPTIONALLY ENCLOSED BY '"'字符串两边加双引号，数字不加
报错 1086文件已经存在。导出不会覆盖已有的文件，要换一个文件名
执行后看：s_2.txt 的第一行是 "s1","王彤","女",18,"计算机","信息学院"，年龄没有引号。第二次导出报错 1086。
2-4

导入到新表

讲义 · 用 LOAD DATA INFILE 导入
目的
建一张与 s 结构相同的空表 s_copy，用 LOAD DATA INFILE 把 s_2.txt 导入。
前提
已完成实验 2-3，Uploads 中有 s_2.txt。
  1. 用 CREATE TABLE … LIKE 建立空表 s_copy
  2. 把 s_2.txt 导入 s_copy，分隔符与导出时相同
  3. 查询 s_copy

参考答案

可以直接复制到 Workbench 执行
-- 建一张结构与 s 相同的空表
CREATE TABLE s_copy LIKE s;
LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/s_2.txt'
INTO TABLE s_copy
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"';
SELECT * FROM s_copy;

语句解释

CREATE TABLE s_copy LIKE s复制 s 的表结构，不复制数据
LOAD DATA INFILE读取文件中的每一行，插入 s_copy
FIELDS …与导出时的写法相同，字段才能正确拆开
执行后看：显示 8 row(s) affected。s_copy 中的 8 行与 s 相同，双引号已经去掉。
2-5

重复导入与行结束符

讲义 · 用 LOAD DATA INFILE 导入
目的
执行下面的语句，看结果，理解重复主键的处理和 LINES 子句的作用。
前提
已完成实验 2-4，s_copy 中有 8 行。

逐句执行，每执行一句，看一下输出区中的结果。

要执行的语句
-- 第 1 句  再导入一次
LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/s_2.txt'
INTO TABLE s_copy
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';
-- 第 2 句  先改一行，再加 REPLACE 导入
UPDATE s_copy SET age = 30 WHERE sno = 's1';
LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/s_2.txt'
REPLACE INTO TABLE s_copy
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';
SELECT * FROM s_copy WHERE sno = 's1';
-- 第 3 句  清空后另外指定行结束符导入
TRUNCATE TABLE s_copy;
LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/s_2.txt'
INTO TABLE s_copy
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';
SELECT * FROM s_copy;

结果与原因

语句结果原因
第 1 句报错 1062主键 s1 已经存在。不写 REPLACE 或 IGNORE 时，遇到重复的主键就报错
第 2 句s1 的年龄变回 18REPLACE 用文件中的行替换主键相同的行。写 IGNORE 则跳过这些行，保留表中原有的
第 3 句只导入了 1 行文件中每行以 \n 结束，按 \r\n 找不到行尾，整个文件被当成一行。s1 的 dept 中混进了后面的内容
执行后看：导出时没写 LINES，导入时也不要写。行结束符与文件不一致，整个文件被当成一行。
保山学院·人工智能教研室·曹鼎鼎