1 导出
MySQL数据库中的数据可以导出成.sql文本文件、xml文件或html文件。
1.1 用SELECT…INTO OUTFILE导出
SELECT columnlist FROM table WHERE condition INTO OUTFILE 'filename' [OPTION]
--OPTIONS 选项
FIELDS TERMINATED BY 'value' /*设置字段之间分隔符,单个或多个字符,默认为'\t'*/
FIELDS [OPTIONALLY] ENCLOSEED BY 'value' /*设置字段包围分隔符,单个字符*/
FIELDS ESCAPED BY 'value' /*如何写入或读取特殊字符,单个字符*/
LINES STARTING BY 'value' /*每行数据开头的字符,单个或多个*/
LINES TERMINATED BY 'value' /*每行数据结尾的字符,单个或多个*/
(1)例子
SELECT * FROM test.person INTO OUTFILE "C:/person0.txt";
person0.txt
1 Green 21 student
2 Suse 2 dancer
3 Mary 24 Musician
4 Willam 20 student
5 Laura 0
6 Evans 27 secretary
7 Dale 22 student
8 Edison 28 cook
9 Harry 21 student
(2)例子
SELECT * FROM test.person INTO OUTFILE "C:/person1.txt"
FIELDS
TERMINATED BY ','
ENCLOSED BY '\"'
ESCAPED BY '\''
LINES
TERMINATED BY '\r\n';
person1.txt
"1","Green","21","student"
"2","Suse","2","dancer"
"3","Mary","24","Musician"
"4","Willam","20","student"
"5","Laura","0",""
"6","Evans","27","secretary"
"7","Dale","22","student"
"8","Edison","28","cook"
"9","Harry","21","student"
1.2 用mysqldump命令导出
mysqldump -T path -u root -p dbname [tables][OPTIONS]
--OPTION 选项
--fields-terminated-by=value
--fields-enclosed-by=value
--fields-optionally-enclosed-by=value
--fields-escaped-by=value
--lines-terminated-by=value
(1)例子
mysqldump -T C:/ test person -u root -p
语句执行后生成两个文件,person.sql(包含CREATE语句等)和person.txt(包含数据信息)。
1.3 用mysql命令导出
mysql -u root -p --execute="SELECT 语句" dbname>filename.txt
相比mysqldump,mysql工具导出的结果可读性更强。
(1)例子
mysql -u root -p --execute="SELECT * FROM person;" test > C:\person3.txt
(2)例子,使用- -vertical参数显示结果
mysql -u root -p --vertical --execute="SELECT * FROM person;" test > C:\person4.txt
(3)例子,导出为html文件
mysql -u root -p --html --execute="SELECT * FROM person;" test > C:\person5.html
(4)例子,导出为xml文件
mysql -u root -p --xml --execute="SELECT * FROM person;" test > C:\person6.xml
2 导入
1.1 用LOAD DATA INFILE导入
LOAD DATA INFILE 'filename.txt' INTO TABLE tablename [OPTION][IGNORE number LINES]
--OPTIONS 选项
FIELDS TERMINATED BY 'value' /*设置字段之间分隔符,单个或多个字符,默认为'\t'*/
FIELDS [OPTIONALLY] ENCLOSEED BY 'value' /*设置字段包围分隔符,单个字符*/
FIELDS ESCAPED BY 'value' /*如何写入或读取特殊字符,单个字符*/
LINES STARTING BY 'value' /*每行数据开头的字符,单个或多个*/
LINES TERMINATED BY 'value' /*每行数据结尾的字符,单个或多个*/
(1)例子
mysql> SELECT * FROM test.person INTO OUTFILE "C:/person0.txt";
Query OK, 9 rows affected
mysql> use test;
Database changed
mysql> DELETE FROM person;
Query OK, 9 rows affected
mysql> LOAD DATA INFILE 'C:/person0.txt' INTO TABLE test.person;
Query OK, 9 rows affected
Records: 9 Deleted: 0 Skipped: 0 Warnings: 0
mysql> SELECT * FROM person;
+----+--------+-----+-----------+
| id | name | age | info |
+----+--------+-----+-----------+
| 1 | Green | 21 | student |
| 2 | Suse | 2 | dancer |
| 3 | Mary | 24 | Musician |
| 4 | Willam | 20 | student |
| 5 | Laura | 0 | |
| 6 | Evans | 27 | secretary |
| 7 | Dale | 22 | student |
| 8 | Edison | 28 | cook |
| 9 | Harry | 21 | student |
+----+--------+-----+-----------+
9 rows in set
1.3 用mysqlimport命令导入
mysqlimport -u root -p dbname filename.txt [OPTIONS]
--OPTION 选项
--fields-terminated-by=value
--fields-enclosed-by=value
--fields-optionally-enclosed-by=value
--fields-escaped-by=value
--lines-terminated-by=value
--ignore-lines=n