首页 > 代码库 > 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导入导出