MySQL知识（二十三）——表的导出和导入

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