网站(建设)安全自查报告,手机wordpress的文章分类,网站建设售前说明书,世界十大建筑设计公司排名【图书推荐】《MySQL 9从入门到性能优化#xff08;视频教学版#xff09;》-CSDN博客
《MySQL 9从入门到性能优化#xff08;视频教学版#xff09;#xff08;数据库技术丛书#xff09;》(王英英)【摘要 书评 试读】- 京东图书 (jd.com)
MySQL9数据库技术_夏天又到了…【图书推荐】《MySQL 9从入门到性能优化视频教学版》-CSDN博客
《MySQL 9从入门到性能优化视频教学版数据库技术丛书》(王英英)【摘要 书评 试读】- 京东图书 (jd.com)
MySQL9数据库技术_夏天又到了的博客-CSDN博客
有时需要将MySQL数据库中的数据导出到外部存储文件中MySQL数据库中的数据可以导出成sql文本文件、xml文件或者html文件。本文介绍3中导出数据的方法。
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-privD:/ProgramData/MySQL/MySQL Server 9.0/Uploads
在上述代码前添加#注释掉然后添加以下内容
secure-file-privD:/
结果如图11.1所示。 图11.1 设置数据表的导出路径
重启MySQL服务器后再次使用SELECT...INTO OUTFILE将test_db数据库中的person表中的记录导出到文本文件SQL语句如下
mysqlSELECT * 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.txtLINESSTARTING BY TERMINATED BY end\r\n;
执行完语句之后换行显示每条记录结果如下 1 Green 21 Lawyer end2 Suse 22 dancer end3 Mary 24 Musician end4 Willam 20 sports man end5 Laura 25 \N end6 Evans 27 secretary end7 Dale 22 cook end8 Edison 28 singer end9 Harry 21 magician end10 Harriet 19 pianist end11.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-byvalue设置字段之间的分隔字符可以为单个或多个字符默认情况下为制表符\t。--fields-enclosed-byvalue设置字段的包围字符。--fields-optionally-enclosed-byvalue设置字段的包围字符只能为单个字符只能包括CHAR和VERCHAR等字符数据字段。--fields-escaped-byvalue控制如何写入或读取特殊字符只能为单个字符即设置转义字符默认值为\。--lines-terminated-byvalue设置每行数据结尾的字符可以为单个或多个字符默认值为“\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_CLIENTCHARACTER_SET_CLIENT */;
/*!40101 SET OLD_CHARACTER_SET_RESULTSCHARACTER_SET_RESULTS */;
/*!40101 SET OLD_COLLATION_CONNECTIONCOLLATION_CONNECTION */;SET NAMES utf8mb4 ;
/*!40103 SET OLD_TIME_ZONETIME_ZONE */;
/*!40103 SET TIME_ZONE00:00 */;
/*!40101 SET OLD_SQL_MODESQL_MODE, SQL_MODE */;
/*!40111 SET OLD_SQL_NOTESSQL_NOTES, SQL_NOTES0 */;--
-- 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)
) ENGINEInnoDB AUTO_INCREMENT11 DEFAULT CHARSETutf8;
/*!40101 SET character_set_client saved_cs_client */;/*!40103 SET TIME_ZONEOLD_TIME_ZONE */;/*!40101 SET SQL_MODEOLD_SQL_MODE */;
/*!40101 SET CHARACTER_SET_CLIENTOLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTSOLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTIONOLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTESOLD_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指令将查询结果导入文本文件中。相比mysqldumpmysql工具导出的结果具有更强的可读性。
如果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 --executeSELECT * 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 --executeSELECT * FROM person; test_db D:\person4.txt
语句执行之后D:\person4.txt文件中的内容如下
*** 1. row ***id: 1
name: Greenage: 21
info: Lawyer
*** 2. row ***id: 2
name: Suseage: 22
info: dancer
*** 3. row ***id: 3
name: Maryage: 24
info: Musician
*** 4. row ***id: 4
name: Willamage: 20
info: sports man
*** 5. row ***id: 5
name: Lauraage: 25
info: NULL
*** 6. row ***id: 6
name: Evansage: 27
info: secretary
*** 7. row ***id: 7
name: Daleage: 22
info: cook
*** 8. row ***id: 8
name: Edisonage: 28
info: singer
*** 9. row ***id: 9
name: Harryage: 21
info: magician
*** 10. row ***id: 10
name: Harrietage: 19
info: pianist可以看到SELECT的查询结果导出到文本文件之后显示格式发生了变化如果person表中的记录内容很长这样显示会让人更加容易阅读。
mysql还可以将查询结果导出到html文件中使用--html选项即可。
【例11.17】使用MySQL命令将test_db数据库中的person表中的记录导出到html文件SQL语句如下
mysql -u root -p --html --executeSELECT * 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 --executeSELECT * FROM person; test_db D:\person6.xml
语句执行成功将在D盘创建文件person6.xml该文件在浏览器中的显示效果如图11.3所示。
