MySQL数据的导出

有时需要将MySQL数据库中的数据导出到外部存储文件中，MySQL数据库中的数据可以导出成sql文本文件、xml文件或者html文件。本节将介绍数据导出的常用方法。

11.4.1 使用SELECT…INTO OUTFILE导出文本文件

MySQL数据库导出数据时，允许使用包含导出定义的SELECT语句进行数据的导出操作。该文件被创建到服务器主机上，因此必须拥有文件写入权限（FILE权限）才能使用此语法。“SELECT...INTO OUTFILE 'filename'”形式的SELECT语句可以把被选择的行写入一个文件中，并且filename不能是一个已经存在的文件。SELECT...INTO OUTFILE语句的基本格式如下：

SELECT columnlist  FROM table WHERE condition  INTO OUTFILE 'filename'  [OPTIONS]

可以看到SELECT columnlist FROM table WHERE condition为一个查询语句，查询结果返回满足指定条件的一条或多条记录；INTO OUTFILE语句的作用就是把前面SELECT语句查询出来的结果，导出到名称为“filename”的外部文件中；[OPTIONS]为可选参数选项，OPTIONS部分的语法包括FIELDS和LINES子句，其可能的取值如下：

FIELDS TERMINATED BY 'value'：设置字段之间的分隔字符，可以为单个或多个字符，默认情况下为制表符（\t）。

FIELDS [OPTIONALLY] ENCLOSED BY 'value'：设置字段的包围字符，只能为单个字符，若使用了OPTIONALLY关键字，则只有CHAR和VERCHAR等字符数据字段被包围。

FIELDS ESCAPED BY 'value'：设置如何写入或读取特殊字符，只能为单个字符，即设置转义字符，默认值为“\”。

LINES STARTING BY 'value'：设置每行数据开头的字符，可以为单个或多个字符，默认情况下不使用任何字符。

LINES TERMINATED BY 'value'：设置每行数据结尾的字符，可以为单个或多个字符，默认值为“\n”。

FIELDS和LINES两个子句都是自选的，但是如果两个都被指定了，则FIELDS必须位于LINES的前面。

使用SELECT...INTO OUTFILE语句，可以非常快速地把一张表转储到服务器上。如果想要在服务器主机之外的部分客户主机上创建结果文件，不能使用SELECT...INTO OUTFILE，应该使用“MySQL –e "SELECT ..." > file_name”这类的命令来生成文件。

SELECT...INTO OUTFILE是LOAD DATA INFILE的补语，用于语句的OPTIONS部分的语法包括部分FIELDS和LINES子句，这些子句与LOAD DATA INFILE语句同时使用。

【例11.10】使用SELECT...INTO OUTFILE将test_db数据库中的person表中的记录导出到文本文件，SQL语句如下：

mysql> SELECT *  FROM test_db.person INTO OUTFILE 'D:/person0.txt';

语句执行后报错信息如下：

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

这是因为MySQL默认对导出的目录有权限限制，也就是说使用命令行进行导出的时候，需要指定目录。那么指定的目录是什么呢？

查询指定目录的命令如下：

show global variables like '%secure%';

执行结果如下：

+-------------------------+-----------------------------------------------+

| Variable_name             | Value                                                  |

+-------------------------+-----------------------------------------------+

|require_secure_transport | OFF                                                    |

|secure_file_priv           | D:\ProgramData\MySQL\MySQL Server 9.0\Uploads\|

+-------------------------+-----------------------------------------------+

因为secure_file_priv配置的关系，所以必须导出到D:\ProgramData\MySQL\MySQL Server 9.0\Uploads\目录下，该目录就是指定目录。如果想自定义导出路径，需要修改my.ini配置文件。打开路径D:\ProgramData\MySQL\MySQL Server 9.0，用记事本打开my.ini文件，然后搜索以下代码：

secure-file-priv="D:/ProgramData/MySQL/MySQL Server 9.0/Uploads"

在上述代码前添加#注释掉，然后添加以下内容：

secure-file-priv="D:/"

结果如图11.1所示。

图11.1 设置数据表的导出路径

重启MySQL服务器后，再次使用SELECT...INTO OUTFILE将test_db数据库中的person表中的记录导出到文本文件，SQL语句如下：

mysql>SELECT *  FROM test_db.person INTO OUTFILE 'D:/person0.txt';

Query OK, 1 row affected (0.01 sec)

由于指定了INTO OUTFILE子句，因此SELECT会将查询出来的3个字段值保存到C:\person0.txt文件中。打开该文件，内容如下：

1   Green       21  Lawyer

2   Suse         22  dancer

3   Mary         24  Musician

4   Willam      20  sports man

5   Laura       25  \N

6   Evans       27  secretary

7   Dale          22  cook

8   Edison      28  singer

9   Harry       21  magician

10  Harriet     19  pianist

默认情况下，MySQL使用制表符（\t）分隔不同的字段，字段没有被其他字符包围。另外，第5行中有一个字段值为“\N”，表示该字段的值为NULL。默认情况下，当遇到NULL时，会返回“\N”，代表空值，其中的反斜线（\）表示转义字符；如果使用ESCAPED BY选项，则N前面为指定的转义字符。

【例11.11】使用SELECT...INTO OUTFILE语句，将test_db数据库person表中的记录导出到文本文件，使用FIELDS选项和LINES选项，要求字段之间使用逗号分隔，所有字段值用双引号引起来，定义转义字符为单引号“\'”，SQL语句如下：

SELECT * FROM  test_db.person INTO OUTFILE "D:/person1.txt"

FIELDS

TERMINATED BY ','

ENCLOSED BY '\"'

ESCAPED BY '\''

LINES

TERMINATED BY '\r\n';

该语句将把person表中所有记录导入D盘目录下的person1.txt文本文件中。

“FIELDS TERMINATED BY ','”表示字段之间用逗号分隔；“ENCLOSED BY '\"'”表示每个字段用双引号引起来；“ESCAPED BY '\'”表示将系统默认的转义字符替换为单引号；“LINES TERMINATED BY '\r\n'”表示每行以回车换行符结尾，保证每一条记录占一行。

执行成功后，在D盘下生成一个person1.txt文件。打开文件，内容如下：

"1","Green","21","Lawyer"

"2","Suse","22","dancer"

"3","Mary","24","Musician"

"4","Willam","20","sports man"

"5","Laura","25",'N'

"6","Evans","27","secretary"

"7","Dale","22","cook"

"8","Edison","28","singer"

"9","Harry","21","magician"

"10","Harriet","19","pianist"

可以看到，所有的字段值都被双引号引起来；第5条记录中空值的表示形式为“N”，即使用单引号替换了反斜线转义字符。

【例11.12】使用SELECT...INTO OUTFILE语句，将test_db数据库person表中的记录导出到文本文件，使用LINES选项，要求每行记录以字符串“>”开始、以字符串“<end>”结尾，SQL语句如下：

SELECT * FROM  test_db.person INTO OUTFILE "D:/person2.txt"

LINES

STARTING BY '> '

TERMINATED BY '<end>';

语句执行成功后，在D盘下生成一个person2.txt文件。打开该文件，内容如下：

> 1 Green   21  Lawyer <end>> 2  Suse 22  dancer <end>> 3  Mary 24  Musician <end>> 4 Willam  20  sports man <end>> 5  Laura   25  \N <end>> 6 Evans   27    secretary <end>> 7   Dale 22  cook <end>> 8    Edison  28  singer <end>> 9    Harry   21  magician <end>> 10   Harriet 19  pianist <end>

可以看到，虽然将所有的字段值导出到文本文件中，但是所有的记录没有分行，出现这种情况是因为TERMINATED BY选项替换了系统默认的换行符。如果希望换行显示，则需要修改导出语句：

SELECT * FROM  test_db.person INTO OUTFILE "D:/person3.txt"

LINES

STARTING BY '> '

TERMINATED BY '<end>\r\n';

执行完语句之后，换行显示每条记录，结果如下：

> 1 Green       21  Lawyer <end>

> 2 Suse         22  dancer <end>

> 3 Mary         24  Musician <end>

> 4 Willam      20  sports man <end>

> 5 Laura       25  \N <end>

> 6 Evans       27  secretary <end>

> 7 Dale         22  cook <end>

> 8 Edison      28  singer <end>

> 9 Harry       21  magician <end>

> 10 Harriet     19  pianist <end>

11.4.2 使用mysqldump命令导出文本文件

除了使用SELECT… INTO OUTFILE语句导出文本文件之外，还可以使用mysqldump命令。11.1节开始介绍了使用mysqldump备份数据库，该工具不仅可以将数据导出为包含CREATE、INSERT的sql文件，也可以导出为纯文本文件。

mysqldump创建一个包含创建表的CREATE TABLE语句的tablename.sql文件和一个包含其数据的tablename.txt文件。mysqldump导出文本文件的基本语法格式如下：

mysqldump -T path-u root -p dbname [tables] [OPTIONS]

只有指定了-T参数才可以导出纯文本文件；path表示导出数据的目录；tables为指定要导出的表名称，如果不指定，将导出数据库dbname中所有的表；[OPTIONS]为可选参数选项，这些选项需要结合-T选项使用。OPTIONS常见的取值有：

--fields-terminated-by=value：设置字段之间的分隔字符，可以为单个或多个字符，默认情况下为制表符（\t）。

--fields-enclosed-by=value：设置字段的包围字符。

--fields-optionally-enclosed-by=value：设置字段的包围字符，只能为单个字符，只能包括CHAR和VERCHAR等字符数据字段。

--fields-escaped-by=value：控制如何写入或读取特殊字符，只能为单个字符，即设置转义字符，默认值为\。

--lines-terminated-by=value：设置每行数据结尾的字符，可以为单个或多个字符，默认值为“\n”。

这里的OPTIONS的设置与SELECT…INTO OUTFILE语句中的OPTIONS不同，各个取值中等号后面的value值不要用引号括起来。

【例11.13】使用mysqldump将test_db数据库person表中的记录导出到文本文件，SQL语句如下：

mysqldump -T D:\ test_db person -u root -p

语句执行成功，系统D盘目录下面将会有两个文件，分别为person.sql和person.txt。person.sql包含创建person表的CREATE语句，其内容如下：

-- MySQL dump 10.13  Distrib 9.0.1, for Win64 (x86_64)

--

-- Host: localhost    Database: test_db

-- ------------------------------------------------------

-- Server version    9.0.1

 

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

 SET NAMES utf8mb4 ;

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

/*!40103 SET TIME_ZONE='+00:00' */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

 

--

-- Table structure for table `person`

--

 

DROP TABLE IF EXISTS `person`;

/*!40101 SET @saved_cs_client = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `person` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `name` char(40) NOT NULL DEFAULT '',

  `age` int(11) NOT NULL DEFAULT '0',

  `info` char(50) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

/*!40101 SET character_set_client = @saved_cs_client */;

 

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

 

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

 

-- Dump completed on 2024-07-25 16:40:55

备份文件中的信息已在11.1.1节中介绍了。

person.txt包含数据包中的数据，其内容如下：

1   Green       21      Lawyer

2   Suse         22      dancer

3   Mary         24      Musician

4   Willam      20      sports man

5   Laura       25      \N

6   Evans       27      secretary

7   Dale         22      cook

8   Edison      28      singer

9   Harry       21      magician

10  Harriet     19      pianist

【例11.14】使用mysqldump命令将test_db数据库中的person表中的记录导出到文本文件，使用FIELDS选项，要求字段之间使用逗号“,”间隔，所有字符类型字段值用双引号引起来，定义转义字符为“?”，每行记录以“\r\n”结尾，SQL语句如下：

mysqldump -T D:\ test_db person -u root -p --fields-terminated-by=, --fields-optionally-enclosed-by=\" --fields-escaped-by=? --lines-terminated-by=\r\n

Enter password: ******

上面语句要在一行中输入，语句执行成功后，系统D盘目录下面将会有两个文件，分别为person.sql和person.txt。person.sql包含创建person表的CREATE语句，其内容与【例11.13】中的相同；person.txt文件的内容与【例11.13】中的不同，显示如下：

1,"Green",21,"Lawyer"

2,"Suse",22,"dancer"

3,"Mary",24,"Musician"

4,"Willam",20,"sports man"

5,"Laura",25,?N

6,"Evans",27,"secretary"

7,"Dale",22,"cook"

8,"Edison",28,"singer"

9,"Harry",21,"magician"

10,"Harriet",19,"pianist"

可以看到，只有字符类型的值被双引号引起来了，而数值类型的值没有；第5行记录中的NULL表示为“?N”，使用“?”替代了系统默认的“\”。

11.4.3 使用mysql命令导出文本文件

mysql是一个功能丰富的工具命令，使用它们还可以在命令行模式下执行SQL指令，将查询结果导入文本文件中。相比mysqldump，mysql工具导出的结果具有更强的可读性。

如果MySQL服务器是单独的机器，用户是在一个客户端上进行操作，要把数据结果导入客户端上。

使用mysql导出数据文本文件的基本语法格式如下：

mysql -u root -p --execute= "SELECT语句" dbname > filename.txt

该命令使用--execute选项，表示执行该选项后面的语句并退出，后面的语句必须用双引号引起来；dbname为要导出的数据库名称；导出的文件中不同列之间使用制表符分隔，第1行包含了各个字段的名称。

【例11.15】使用mysql命令，将test_db数据库中的person表中的记录导出到文本文件，SQL语句如下：

mysql -u root -p --execute="SELECT * FROM person;" test_db > D:\person3.txt

语句执行完毕之后，系统D盘目录下面将会有名称为“person3.txt”的文本文件，其内容如下：

id  name         age     info

1   Green       21      Lawyer

2   Suse         22      dancer

3   Mary         24      Musician

4   Willam      20      sports man

5   Laura       25      NULL

6   Evans       27      secretary

7   Dale         22      cook

8   Edison      28      singer

9   Harry       21      magician

10  Harriet     19      pianist

可以看到，person3.txt文件中包含了每个字段的名称和各条记录，该显示格式与MySQL命令行下SELECT查询结果的显示格式相同。

使用mysql命令还可以指定查询结果的显示格式，如果某条记录的字段很多，可能一行不能完全显示，可以使用--vartical参数将每条记录分为多行显示。

【例11.16】使用mysql命令将test_db数据库中的person表中的记录导出到文本文件，使用--vertical参数显示结果，SQL语句如下：

mysql -u root -p --vertical --execute="SELECT * FROM person;" test_db > D:\person4.txt

语句执行之后，D:\person4.txt文件中的内容如下：

*** 1. row ***

  id: 1

name: Green

 age: 21

info: Lawyer

*** 2. row ***

  id: 2

name: Suse

 age: 22

info: dancer

*** 3. row ***

  id: 3

name: Mary

 age: 24

info: Musician

*** 4. row ***

  id: 4

name: Willam

 age: 20

info: sports man

*** 5. row ***

  id: 5

name: Laura

 age: 25

info: NULL

*** 6. row ***

  id: 6

name: Evans

 age: 27

info: secretary

*** 7. row ***

  id: 7

name: Dale

 age: 22

info: cook

*** 8. row ***

  id: 8

name: Edison

 age: 28

info: singer

*** 9. row ***

  id: 9

name: Harry

 age: 21

info: magician

*** 10. row ***

  id: 10

name: Harriet

 age: 19

info: pianist

可以看到，SELECT的查询结果导出到文本文件之后，显示格式发生了变化，如果person表中的记录内容很长，这样显示会让人更加容易阅读。

mysql还可以将查询结果导出到html文件中，使用--html选项即可。

【例11.17】使用MySQL命令将test_db数据库中的person表中的记录导出到html文件，SQL语句如下：

mysql -u root -p --html --execute="SELECT * FROM person;" test_db > D:\person5.html

语句执行成功，将在D盘创建文件person5.html，该文件在浏览器中的显示效果如图11.2所示。

如果要将表数据导出到xml文件中，可使用--xml选项。

【例11.18】使用mysql命令将test_db数据库中的person表中的记录导出到xml文件，SQL语句如下：

mysql -u root -p --xml --execute="SELECT * FROM person;" test_db >D:\person6.xml

语句执行成功，将在D盘创建文件person6.xml，该文件在浏览器中的显示效果如图11.3所示。

posted @ 2024-10-14 08:29 brucexia 阅读(851) 评论(0) 编辑收藏举报

刷新页面返回顶部

brucexia

清华大学出版社图格事业部编辑 IT图书出版 QQ49659450

MySQL数据的导出

11.4.1 使用SELECT…INTO OUTFILE导出文本文件

11.4.2 使用mysqldump命令导出文本文件

11.4.3 使用mysql命令导出文本文件

公告

brucexia

清华大学出版社图格事业部 编辑 IT图书出版 QQ49659450

MySQL数据的导出

11.4.1 使用SELECT…INTO OUTFILE导出文本文件

11.4.2 使用mysqldump命令导出文本文件

11.4.3 使用mysql命令导出文本文件

公告

清华大学出版社图格事业部编辑 IT图书出版 QQ49659450