【MySQL】使用文件进行交互

目录

准备工作

1.从文本文件中读取数据（导入）

1.1.CSV 文件

1.2.设置导入导出的路径

1.3.导入文件

1.4.将数据写入文本文件（导出）

2.从文件中读取并执行SQL命令

2.1.通过mysql监视器执行编写在文件里面的SQL语句

2.2.通过命令提示符执行编写在文件里的SQL命令

 3.将SQL的执行结果保存到文件中

3.1通过重定向将SQL语句的执行结果输出到文本文件中

3.2. 使用tee命令将SQL语句的执行结果保存到文件中

4.备份和恢复数据库

4.1.备份和恢复的方法

4.2.使用mysqldump导出

4.3.恢复转储文件

---

准备工作

首先我们知道有下面这个数据库即可

 

然后我们重点关注db和db1这两个表即可 

1.从文本文件中读取数据（导入）

当把大量数据输入到表中时，如果打开 MySQL 监视器用键盘手动输入所有数据，就会耗费非常多的时间和精力。

在需要输入成千上万条数据的情况下，我们可以使用 CSV（Comma Separated Values，逗号分隔值）格式的文本文件进行输入，这种读取文件的方式称为导入（import）。

1.1.CSV 文件

正如逗号分隔值这个名字所表达的那样，在 CSV 文件中，数据是用逗号隔开的，文件内容仅包含文本。我们看一下CSV格式的文件

每条记录都通过换行符用单独的一行表示

1.2.设置导入导出的路径

我们先看一个东西

select @@global.secure_file_priv;

@@global是用于引用MySQL系统全局变量的关键字。

连接到这个MySQL服务器的所有客户端都会引用系统全局变量的值，secure_file_priv用于指定允许导入、导出文件的路径。

如果指定为空字符串""，那么任何路径都可以导人或导出文件。如果指定为NULL,则不能导人或导出文件。

我们去这个目录看看

可以看到什么都没有啊 

---

secure_file_priv 这个变量被用于限制导入和导出的数据目录，比如 LOAD DATA 和 SELECT ... INTO OUTFILE 语句，以及 LOAD_FILE() 函数。这些操作限制了哪些用户拥有文件操作权限。

secure_file_priv 有些设置选项:

• 如果为空，不做目录限制，即任何目录均可以。
• 如果指定了目录，MySQL 会限制只能从该目录导入、或导出到该目录。目录必须已存在，MySQL 不会自动创建该目录。
• 如果设置为 NULL，MySQL 服务器禁止导入与导出功能。

该变量的默认值，是由编译时的 CMake 选项而定。

MySQL 服务器在启动时，会检查 secure_file_priv 变量值，如果值不安全会在错误日志中写一个 WARNING 级别的日志。以下情况属于不安全的设置：

1. 值为空
2. 值为--datadir目录或其子目录
3. 所有用户均有权限访问的目录

1.3.导入文件

我们可以使用LOAD DATA INFILE命令从文件导人数据，具体语句如下所示。

LOAD DATA INFILE '文件名' INTO TABLE 表名选项的描述；

除了CSV格式的文件以外，不用逗号分隔的文本文件也能被读取。我们可以指定读取的数据的格式，比如指定数据之间的分隔符、换行符，以及从第几行开始读取等。

在这种情况下，“选项的描述”部分需要按照下面的格式编写。

• 格式LOAD DATA INFILE命令中指定数据格式的选项

FIELDS TERMINATED BY 分隔符（默认是'\t': Tab）
LINES TERMINATED BY 换行符（默认是'\n':换行）
IGNORE 最开始跳过的行 LINES(默认是0)

LOAD DATA INFILE 是 MySQL 中用于从文本文件导入数据到数据库表的命令。通过指定不同的格式选项，可以灵活适配多种文件格式（如 CSV、TSV 或其他自定义分隔符的文本文件）。以下是三个关键格式选项的作用和用法说明：

---

• 1. FIELDS TERMINATED BY（字段分隔符）

默认情况下，MySQL 使用制表符 \t 作为字段分隔符。若文件中的字段由其他字符分隔（如逗号 ,、竖线 | 或特殊符号），需显式指定。例如，对于 CSV 文件，通常设置为 FIELDS TERMINATED BY ','。若分隔符是特殊字符（如转义字符），需用引号包裹或转义，例如 FIELDS TERMINATED BY '\t' 表示制表符。此选项直接影响如何解析每行中的字段。

---

• 2. LINES TERMINATED BY（行终止符）

默认行终止符为换行符 \n，适用于 Unix/Linux 系统生成的文件。若文件来自 Windows 系统（行终止符为 \r\n），需设置为 LINES TERMINATED BY '\r\n'。此选项用于标识文件中每行的结束位置，确保数据按行正确分割。若设置错误，可能导致整段文本被误认为单行数据。

---

• 3. IGNORE n LINES（忽略起始行数）

默认值为 0，即从文件第一行开始读取。若文件开头包含标题行或注释行，可通过此选项跳过指定行数。例如，IGNORE 1 LINES 会跳过第一行（常用于 CSV 文件跳过表头）。需注意，n 必须是整数，且仅作用于文件起始位置，无法跳过中间或末尾的行。

---

•  示例完整命令

假设有一个 CSV 文件 data.csv，内容如下：

id,name,age
1,Alice,30
2,Bob,25

若需导入到表 users 并跳过标题行，命令为：

LOAD DATA INFILE 'data.csv' INTO TABLE users
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

此配置会以逗号分隔字段，按换行符分拆行，并忽略首行标题。

• 一个小例子

接下来我们自己来使用一下

下面这个和表tb1的结构相同。这次，我们试着将这个文件命名为t.csv,并将其导入到结构与表tb1相同的表tb1K中。

t.csv

N551,佐佐木,37
N552,伊藤,41
N553,齐藤,31
N554,井上,43
N555,阿倍,31

• 导入文件

CSV文件t.csv使用的分隔符是“，”，所以我们只要通过FIELDS  TERMINATED BY ',' 进
行指定即可。

另外，当指定保存CSV文件的文件夹路径时，即使在Windows的情况下也不要使用
“\”,而要使用“/”。（例如：C:\data\t.csv→C:/data/t.csv）

下面来实际操作一下吧。

load data infile '/var/lib/mysql-files/t.csv' into table tb1 fields terminated by ',';

• LOAD DATA INFILE：MySQL 中用于从文本文件（如 CSV）导入数据到数据库表的命令。

• '/var/lib/mysql-files/t.csv'：指定要导入的 CSV 文件路径。

• INTO TABLE tb1：将数据导入到名为 tb1 的表中。

• FIELDS TERMINATED BY ','：声明 CSV 文件中字段的分隔符为逗号（,）。

即使数据库的重量不同，只要文件是CSV格式就可以执行导入。

1.4.将数据写入文本文件（导出）

与导入相反，我们可以将表中的数据提取到 CSV 文件等文本文件中（见图 14-3），这种把数据提取到文件中的操作称为导出（export）。

导出的文件可以在其他的数据库和系统中使用，也可以在紧急情况下作为备份使用。我们可以通过如下命令执行导出操作。

注意：将数据写入到文本文件

SELECT * INTO OUTFILE '文件名' 选项的描述 FROM 表格；

参数“选项的描述”用于指定导出的文本文件的格式。具体的描述方法与导入时完全相同。

 除了CSV格式的文件以外，不用逗号分隔的文本文件也能被读取。我们可以指定读取的数据的格式，比如指定数据之间的分隔符、换行符，以及从第几行开始读取等。

在这种情况下，“选项的描述”部分需要按照下面的格式编写。

• 指定数据格式的选项

FIELDS TERMINATED BY 分隔符（默认是'\t': Tab）
LINES TERMINATED BY 换行符（默认是'\n':换行）
IGNORE 最开始跳过的行 LINES(默认是0)

接着我们就导出我们的tb1表

select * into outfile '/var/lib/mysql-files/out.csv' fields terminated by ',' from tb1;

 

执行上面这个命令后，mysql会在 下面这个文件夹中，创建 CSV 格式的文本文件 out.csv 用于导出表 th1 中的数据。

回刚刚那个目录看看

我们可以查看一下这个新增的文件

sudo vim /var/lib/mysql-files/out.csv

 

和我们的tb1完全对的上

2.从文件中读取并执行SQL命令

2.1.通过mysql监视器执行编写在文件里面的SQL语句

当执行复杂且冗长的 SQL语句时，如果每次都在 MySQL 监视器上编写就会很麻烦。在执行复杂的 SQL语句的情况下，我们可以将其创建为文本文件，然后执行保存的文件。

这种方法可以进一步提高工作效率，还可以显示创建成文本文件的 SOL语句，并将其复制、粘贴到MySOL 监视器上执行。

如果将 SOL语句保存为文本文件，就可以反复使用它，还可以轻松对其进行改善。

在记事本等文本编辑器中事先准备好 SOL语句，然后在 MySOL 监视器上执行 SOURCE 命令。
格式 执行包含 SQL 语句的文本文件

SOURCE 文本文件名

如果执行的 SOL语句中包含中文，就需要注意作为参数的文本文件的字符编码了。

使用SOURCE 命令执行 SQL语句的方式与在 MySQL 监视器上执行的方式相同。

下面来实际操作一下。

试着在下面这个文件夹中创建包含“use scott”“SELBCT * FROMtb;”和“SELECT *FROM tb1;”这3行语句的文本文件 test.sql，读取并执行该文本文件。

接着我们打开mysql，执行下面这个命令

source /home/zs_108/test.sql

source不是SQL命令，所以可以不加逗号 

将常用表的内容保存到文件中

使用MySQL监视器创建“需要多次创建的表”或者“复杂的表”效率会很低。在这种情况下，建议在文本中编写处理步骤，然后执行SOURCE命令来创建表。

创建好文本文件后，就可以根据需要反复地使用它，并且可以不断地对其进行改善。最重要的是，与直接执行不同，我们可以静下心来编写，由此也可以减少输入方面的错误。

例如，下面是是创建员工信息表tb1的SQL语句。

tb1_make.sql

DROP TABLE IF EXISTS tb1;
CREATE TABLE tb1 (empid VARCHAR(10),name VARCHAR(10),age INT);
INSERT INTO tb1 VALUES("A101","佐藤",40);
INSERT INTO tb1 VALUES("A102","高桥",28);
INSERT INTO tb1 VALUES("A103","中川",20);
INSERT INTO tb1 VALUES("A104","渡边",23);
INSERT INTO tb1 VALUES("A105","西泽",35);

我们打开mysql，执行下面这些命令

怎么样，是不是很好用？

2.2.通过命令提示符执行编写在文件里的SQL命令

之前执行SQL命令的时候会先启动MySQL监视器并在mysql>之后输入SQL语句执行。实
际上，我们也可以不启动MySQL监视器，直接通过命令提示符来执行SQL语句。

也就是说，即使不启动MySQL监视器，也可以执行包含SQL语句的文本文件。

这里我们试着使用SOURCE命令执行一下我们上一节创建的test.sql。

test.sql

use scott
SELECT * FROM tb;
SELECT * FROM tb1;

通过命令提示符执行SQL语句时需要使用如下命令。

• 通过命令提示符执行SQL语句

mysql 数据库名 -u 用户名 -p密码 -e "MySQL监视器的命令"

添加-e选项，然后将后面的命令用""括起来。

注意要用"（双引号）将命令括起来，而不是用  '（单引号）。另外，请在-p密码、-e和"MySQL监视器的命令"之间加上半角空格。

如果不需要指定数据库名，则可以省略“数据库名”部分。因为在test.sql中有use scott的描述，所以可以省略“数据库名”。

当然，如果你觉得把密码写在中间太暴露了，我们也可以像下面这样子

mysql 数据库名 -u 用户名 -e "MySQL监视器的命令" -p

我们来实际操作一下。

mysql -u root -e "source /home/zs_108/test.sql" -p

即使不启动mysql监视器，也可以直接通过命令提示符直接执行文本文件中编写的SQL命令。

 3.将SQL的执行结果保存到文件中

在MySQL监视器上输入的命令会显示在屏幕上，执行结果也会显示在屏幕上。

用户可以通过 查看屏幕上显示的执行结果来获取信息，也可以将这些执行结果作为数据使用。

那么，如何将SQL的执行结果保存到文件中呢？

这里我们将介绍“在MySQL监视器上执行 tee命令”和“使用重定向将结果输出到标准输出”这两种方法。

3.1通过重定向将SQL语句的执行结果输出到文本文件中

我们可以在计算机上输入一些数据并让计算机输出处理结果。这时通常会使用键盘进行“输 入”，然后把结果“输出”到显示器上。

像键盘这种一开始就配备好的输入设备称为“标准输入”， 像显示器这种一开始就配备好的输出设备称为“标准输出”。

“标准输入”和“标准输出”可以更改。这个更改操作称为重定向 (redirect)。Windows 和 Linux等操作系统都具有这项功能。当进行重定向操作，也就是更改输入、输出目标时，需要使用 “>”等符号。

例如，下面这个就是一种重定向

 这样一来，echo的执行结果就写入了文本文件a.txt中。之所以会写人文本文件a.txt中， 是因为加上了>a.txt。 保存了输出结果的文件a.txt会保存在执行echo命令的当前路径中。

• 通过mysql命令使用重定向

下面试着在MySQL中使用重定向功能。假设在启动MySQL监视器的时候执行了以下命令。

mysql -u root -p

试着使用重定向把提取结果输入到文件中。请按照下面的方式启动MySQL监视器。刚开始会 显示“Warning:Using a password...”的警告，大家不必在意。

mysql -u root > log.txt -p

如果按照这种方式启动MySQL监视器，SQL语句的执行结果将不会显示在屏幕上，而是会输 出到重定向指定的log.txt中。注意，不要因为没有显示任何结果而胡乱操作。

下面让我们静下心来慢慢操作。先指定要使用的数据库，然后使用SELECT命令显示表tb1的 内容，最后通过exit退出MySQL监视器。执行结果同样不会显示出来。

use scott
SELECT * FROM tb1;
exit

回到原来的命令提示符界面了吗？

屏幕上没有显示任何内容，确实有点不方便。

那么我们就来确认一下log.txt的内容。log.txt应该保存在执行命令的当前路径中。

使用记事本等文本编辑器打开log.txt,或者在命令提示符下输入type log. txt。

你会发现，以前显示在MySQL监视器上的内容变成了文本文件。

这样，“SELECT *FROM tb1;”的结果应该就能显示出来了。该文件只是一个文本文件， 因此可以在其他系统和应用软件中自由使用。

如果需要让结果显示在屏幕上，则可以结合上一节中介绍的方法使用。

从命令提示符上执行 SQL语句文本文件test.sql，然后使用重定向将结果直接写入文本文件log.txt中。

mysql -u root -e "SOURCE /home/zs_108/test.sql" > log.txt -p

test.sql的内容 

use scott
select * form tb;
select * from tb1;

• 使用重定向输入SQL语句，并通过重定向将结果输出到文件

下面介绍使用重定向进行输入、输出的示例。
使用重定向输入文件时需要使用“<”。这样就可以使用重定向输入并执行文本文件的SQL语句(test.sql)，然后再通过重定向将结果输出到文本文件(log2.txt)。

mysql -u root < /home/zs_108/test.sql > /home/zs_108/log2.txt -p

即使看不到过程也没有问题。但是，在使用这种方法的情况下，就算SQL语句报错，我们也只能通过查看输出的文本文件得知。

3.2. 使用tee命令将SQL语句的执行结果保存到文件中

在MySQL监视器上使用tee命令，可以与上一节的重定向一样将结果写入文件。

如果在MySQL监视器上按照如下方式执行tee命令，执行结果就会保存在指定的文件中

tee 输出文件的名称

• 将执行结果保存到文件中

当把执行结果导出到文件log3.txt时，假设在MySQL监视器的状态下执行了如下操作。

这样就创建出一个空的文本文件log3.txt,以后输出结果不仅会显示在屏幕上，还会写入log3. txt中。另外，如果log3.txt会保存在执行命令的当前路径中。

执行tee命令后，我们执行下面这些命令

use scott
select * from tb;
select * from tb1;

我们发现这个执行结果会像往常一样正常显示出来，同时这些内容也会被写入log3.txt

• 停止向文件中输出执行结果

这个很简单，我们可以使用notee命令停止 向文件中输出执行结果

notee

很好！！

4.备份和恢复数据库

4.1.备份和恢复的方法

我们可以将数据库的设置、表和列的定义、数据等数据库的所有信息作为文件导出。

• 转储

对数据库的所有内容执行导出的操作称为转储(dump)。

如果使用转储文件，就可以在其他服务器上创建内容相同的数据库，也可以备份以应对紧急情况的发生。

我们可以在命令提示符上执行 mysqldump 命令来转储 MySQL 数据库

mysqldump命令会将数据库的配置和数据本身作为SOL语句写出来，也就是通过“CREATE TABLE……”创建表，然后写出“INSERT INTO ……”这样的 SOL语句。

通过转储导出的信息是由 SOL语句生成的文本。通过这些文本，我们可以读取数据库的所有信息，可以说“转储输出就是数据库本身”。从安全方面考虑，我们需要谨慎对待这些信息。

• 恢复

把通过 mysqlāump 命令导出的数据还原到数据库中的操作称为恢复(restore )。

恢复意味着从头创建数据库，其实就是将包含 SOL语句集合的文本文件用 MySOL, 命令进行重定向。

4.2.使用mysqldump导出

试着使用 mysqlāump 命令对前面创建的数据库 db1的所有信息进行转储，并通过重定向将mysqldump 命令的执行结果写人文件中。

• 转储数据库

mysqldump -u 用户名 -p密码 数据库名 > 输出文件的名称

下面来实际操作一下。

我们先看db1里面有什么东西，

将数据库 db1 的信息转储到名为 db1_out.txt的文件中

mysqldump -u root db1>db1_out.txt -p

我们可以看看这个文件里面有什么东西

 行的开头是“-_”的部分是注释，用“/*”和“*/”括起来的部分也是注释，注释不是命令而是单纯的“说明”。上面的执行结果中有命令“CREATE TABLE…”,它用于创建表。

4.3.恢复转储文件

接下来恢复转储文件。在命令提示符上使用重定向将文件还原到数据库。

• 恢复转储文件格式

mysqldump -u 用户名 -p密码 数据库名 < 转储文件的名称

这次是在同一台计算机上对数据库进行复制，当然我们也可以将数据库恢复到其他的 MySOL环境。

当恢复数据库时，我们必须提前准备一个用于填充的数据库。如果没有数据库，就需要提前创建一个 。
下面来恢复转储文件。创建数据库 db2，然后将转储文件 db1_out.txt 恢复到该数据库中。

我们可以在命令行下输入这个命令即可创建数据库db2

mysql -u root -e "create database db2;" -p

接下来我们就将转储文件还原回来

mysql -u root db2 < db1_out.txt -p

这个时候我们回到mysql看看

 

我们看到，这个和db1完全一样。