首页 > 代码库 > MySQL导入导出
MySQL导入导出
MySQL导出导入数据有以下几种方法：
1）select ... into outfile
2）load data
3）mysqldump
4）mysqlimport
5）mysql
一、表级别导入、导出
关于字符处理有以下相关子句：
1）fields terminated by ‘‘：各个字段之间使用tab分隔。
2）[optionally] enclosed by ‘‘：字段值使用什么符号引起来，如果指定了optionally选项，则enclosed by ‘‘指定字符串类型字段有效。
3）escaped by ‘‘：定义转义字符，默认为“\”。
4）lines terminated by ‘‘：定义换行符，linux下默认为\n。
文本文件格式：
数据：可以使用科学计数法
字符串：字符串里的特殊字符必须使用反斜线字符作为识别标识，以区别各种分隔符
日期：安照2017-05-30格式字符串表示
时间：按照15:12:00格式字符串表示
时间戳：20170520151200格式整数对待
null值：假设“\”作为转义字符，“‘”作为字符串前后缀标识，那么在导出操作中\N表示null值。
1、使用select ... into outfile导出表数据
使用select ... into outfile可以进行表级别的导出操作，并且输出文件不能先于输出存在。
例如：
mysql> select * into outfile ‘/datas/column_charset_00.sql‘ from column_charset;
mysql> select * into outfile ‘/datas/column_charset_01.sql‘ fields terminated by ‘,‘ from column_charset;
mysql> select * into outfile ‘/datas/column_charset_03.sql‘ fields terminated by ‘,‘ lines terminated by ‘\n‘ from column_charset;
mysql> select * into outfile ‘/datas/column_charset_04.sql‘ fields terminated by ‘,‘ optionally enclosed by ‘"‘ lines terminated by ‘\n‘ from column_charset;
2、使用load data导入数据到表中
select ... into outfile命令可以导出表数据到文件中，load data命令巧好于select ... into outfile命令相反，可以导入文件中数据到某张表中。
参数local作用：如果导出文件在服务器上，使用命令load data infile ... 导入数据；否则可以使用load data local infile ... 命令导入客户端本地文件数据到表中。
使用load data命令导入数据时需要注意mysql服务器字符集的设置。如果load data infile在某些mysql版本中不支持字符集，这时，mysql将假定导入字符集为character_set_database指定字符集，如果没有设置character_set_database参数，则默认采用character_set_server指定字符集，可以使用set character_set_database或set names命令更改服务器字符集设置，也可以在load data命令中指定字符集。
例如：
mysql> load data infile ‘/datas/column_charset_00.sql‘ into table column_charset;
--导入数据采用character_set_database默认指定字符集。
mysql> load data infile ‘/datas/column_charset_00.sql‘ into table column_charset character set utf8;
--命令中设置字符集导入tab分隔文件。
mysql> load data infile ‘/datas/column_charset_01.sql‘ into table column_charset character set utf8 fields terminated by ‘,‘;
--导入‘，’分隔文件到表中。
mysql> load data infile ‘/datas/column_charset_03.sql‘ into table column_charset character set utf8 fields terminated by ‘,‘ lines terminated by ‘\t‘;
--导入‘，’分隔列，tab分隔行的文件。
mysql> load data infile ‘/datas/column_charset_04.sql‘ into table column_charset character set utf8 fields terminated by ‘,‘ enclosed by ‘"‘ lines terminated by ‘\n‘;
或者
mysql> load data infile ‘/datas/column_charset_04.sql‘ into table column_charset character set utf8 fields terminated by ‘,‘ optionally enclosed by ‘"‘ lines terminated by ‘\n‘;
--导入‘，’分隔列，“\n”分隔行的文件，‘"‘号引用字段的文件。
mysql> load data infile ‘/datas/column_charset_04.sql‘ into table column_charset character set utf8 fields terminated by ‘,‘ optionally enclosed by ‘"‘ lines terminated by ‘\n‘ (c1,c2,c3,c4);
--指定导入表的字段顺序。
mysql> load data infile ‘/datas/column_charset_04.sql‘ into table column_charset character set utf8 fields terminated by ‘,‘ optionally enclosed by ‘"‘ lines terminated by ‘\n‘ (c1,c2,c3,c4) set c1=‘dbking‘;
--load data同时指定更新列。
mysql> load data local infile ‘/datas/column_charset_04.sql‘ into table column_charset character set utf8 fields terminated by ‘,‘ optionally enclosed by ‘"‘ lines terminated by ‘\n‘ (c1,c2,c3,c4) set c1=‘dbking‘;
mysql> load data local infile ‘/datas/column_charset_04.sql‘ replace into table column_charset character set utf8 fields terminated by ‘,‘ optionally enclosed by ‘"‘ lines terminated by ‘\n‘ (c1,c2,c3,c4) set c1=‘chavin‘;
--导入本地文件到表中。
local data优化：
相对于普通的mysql命令，load data命令导入速度要快得多，一般可以达到几万条记录每秒，如果想要拥有更高的速度，可以进行以下优化操作。这里针对于innodb和myisam存储引擎分别介绍：
针对于innodb模式，建议优化方式有：
1）将innodb_buffer_pool_size值设置的大一些。
2）将innodb_log_file_size设置的大一些。
3）设置忽略二级索引的唯一性约束，set unique_checks=0.
4）设置忽略外检约束，set foreign_key_checks=0.
5）设置不记录二进制文件，set sql_log_bin=0.
6）按主键顺序导入数据。
7）对于innodb引擎表，可以在导入前设置autocommit=0。
8）将大的文件切割成多个小的文件导入，例如split。
针对于myisam模式，建议优化方式有：
1）将bulk_insert_tree_size、myisam_sort_buffer_size、key_buffer_size设置的大一些。
2）先禁用key（alter table ... disable keys），然后再导入数据，然后再启用key（alter table ... enable keys）。重新启用key后，会重新批量创建索引，批量创建索引比一条一条创建索引效率高的多。alter table ... disable keys命令只禁用非唯一性索引，唯一索引和主键是不能禁用的，除非你手工移除它。
3）使用load data，tab分隔的文件更容易解析，比其他方式快。
二、使用mysqldump导出数据
mysqldump导出的一般是SQL文件，也成为转储文件或dump文件，我们可以使用mysql工具或mysqlimport工具导入mysqldump导出文件。
例如：
导出chavin数据库：
mysqldump -uroot -pmysql chavin > chavin00.sql
mysqldump -uroot -pmysql --complete-insert --force --insert-ignore --add-drop-database --hex-blob --database chavin > chavin02.sql
导出chavin库中的某些表：
mysqldump -uroot -pmysql chavin --tables column_charset column_collate > chavin01.sql
导出chavin库，采用sql与数据分离模式：
mysqldump -uroot -pmysql --tab=/datas/chavin00 chavin
导出chavin库，采用sql与数据分离，数据字段使用“，”分隔：
mysqldump -uroot -pmysql --tab=/datas/chavin01 --fields-terminated-by=‘,‘ --fields-enclosed-by=‘"‘ chavin
导出所有数据库：
mysqldump -uroot -pmysql --all-database --add-drop-database >db00.sql
导出xml格式数据：
mysqldump -uroot -pmysql --xml chavin >chavin.03.xml
导出数据库并增加压缩功能：
mysqldump -uroot -pmysql --hex-blob chavin|gzip >chavin04.sql.gz
导出全库：
mysqldump -uroot -pmysql --flush-logs --master-data=http://www.mamicode.com/2 --hex-blob -R -E -f --all-databases 2>> /datas/full-log |gzip > mysql-full.gz
仅导出数据结构：
mysqldump -uroot -pmysql -d --add-drop-table chavin > chavin11.sql
mysqldump -uroot -pmysql --no-data --add-drop-table chavin > chavin12.sql
三、导入由mysqldump导出的数据
1、使用mysql命令行工具可以导入由mysqldump导出的文件。
例如：
导入文件chavin.sql：
mysql -uroot -pmysql restore01 < chavin00.sql
导入压缩过的导出文件：
gzip -dc chavin04.sql.gz | mysql -uroot -pmysql chavin08
导入文件并且确保客户端、连接、文件字符集一致性：
mysql -uroot -pmysql --default-character-set=utf8 restore02 < chavin00.sql
2、mysqlimport工具可以用来导入数据。
3、使用source命令恢复数据
mysql> source /datas/chavin10.sql
四、使用mysql工具批处理功能导出数据
1、导出column_charset表：
mysql -uroot -poracle -h192.168.108.128 -P3306 --batch --default-character-set=utf8 -e "select * from chavin.column_charset;" > output.txt
mysql -uroot -poracle -h192.168.108.128 -P3306 --default-character-set=utf8 --batch "--execute=select * from column_charset;" chavin > output03.txt
mysql -uroot -poracle -h192.168.108.128 -P3306 --default-character-set=utf8 --batch -e "select * from column_charset;" chavin > output04.txt
2、查询结果纵向显示
mysql -uroot -poracle -h192.168.108.128 -P3306 --default-character-set=utf8 --vertical "--execute=select * from chavin.column_charset;" > output00.txt
3、生成html格式输出
mysql -uroot -poracle -h192.168.108.128 -P3306 --default-character-set=utf8 --html "--execute=select * from chavin.column_charset;" > output01.html
4、生成xml格式的输出
mysql -uroot -poracle -h192.168.108.128 -P3306 --default-character-set=utf8 --xml "--execute=select * from chavin.column_charset;" > output02.xml
五、操作系统split工具切割数据文件
split命令作用是切割文件。-l参数指定按多少行进行切割，不指定默认为每1000行切割一份。
# split -l 32 output03.txt split/output_split_sub_
# ll split/
total 20
-rw-r--r-- 1 root root 880 Jan 22 05:39 output_split_sub_aa
-rw-r--r-- 1 root root 896 Jan 22 05:39 output_split_sub_ab
-rw-r--r-- 1 root root 896 Jan 22 05:39 output_split_sub_ac
-rw-r--r-- 1 root root 896 Jan 22 05:39 output_split_sub_ad
-rw-r--r-- 1 root root 28 Jan 22 05:39 output_split_sub_ae
将大文件切割成小文件后，通过多个客户端并行导入，会提高效率。
MySQL导入导出