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  student2   Suse    2   dancer3   Mary    24  Musician4   Willam  20  student5   Laura   0   6   Evans   27  secretary7   Dale    22  student8   Edison  28  cook9   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 affectedmysql> use test;Database changedmysql> DELETE FROM person;Query OK, 9 rows affectedmysql> LOAD DATA  INFILE 'C:/person0.txt' INTO TABLE test.person;Query OK, 9 rows affectedRecords: 9  Deleted: 0  Skipped: 0  Warnings: 0mysql> 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