Mysql数据导入导出

引言

Mysql数据的导入导出是我们十分常见的场景， 常常用于进行数据迁移以及数据备份，了解常见的导入导出方式以及注意事项是十分必要的，Mysql提供了多种方式支持数据的导入和导出。

一、使用SELECT ... INTO OUTFILE导出数据

select ...into outfile语法是mysql提供的一种数据导出的方案，支持自定义导出数据格式。
最简单的使用方法如下,将test表的所有数据全部导出到test.txt文件。

select * from test  into outfile '/tmp/test.txt';

更常用的方法是将表数据导出成csv格式，方便数据的查看以及重新导入

select a,b,c into outfile '/tmp/test.text' fields terminated by ',' optionally enclosed by '"' lines terminated by '\n' from test;

数据使用""括起来，使用逗号分隔，数据记录使用\n作为换行符。

二、使用mysqldump导出数据

mysqldump是mysql官方提供的用于数据导出的程序，其导出的被包含create、insert等语句，可以用于重新执行将数据导入新的库中。常见的用法有:

• 导出包含创建表信息的数据表
  以下语句导出了数据库condingway的test_table表格，--tab指定了导出的文件存放的目录

mysqldump -u root -p --no-create-info --tab=/tmp codingway test_table > codingway_test_dump.sql

• 导出不包含创建表信息的数据表

mysqldump -u root -p --tab=/tmp codingway test_table > codingway_test_dump.sql

• 导出单个数据库的全部数据

mysqldump -u root -p codingway > codingway_dump.sql

• 导出所有数据库的全部数据

mysqldump -u root -p --all-databases > all_databases_dump.sql

• 导出数据到另外一台服务器

mysqldump -u root -p codingway | mysql -h other-host.com codingway

三、原表数据导入备份表

• 同库导入全部数据

insert into target_table select * from origin_table;

• 同库导入部分数据

insert into target_table (column1,column2, ...) select column1,column2, ... from origin_table;

• 同库导入全部数据

create table target_table select * from origin_table;

• 同库导入部分数据

create table target_table select column1,column2,...from origin_table;

• 不同库导入全部数据

create table  target_table select * from orign_db.origin_table;

• 不同库导入部分数据

create table target_table select column1,column2,...from origin_db.origin_table;

四、使用mysqlimport导入数据

mysqlimport一般用于将select ...into file导出的数据文件导入到数据表中。

• 将数据导入到表中

mysqlimport -u root -p --local test test.txt

• 指定格式将数据导入到表中

mysqlimport -u root -p --local --fields-terminated-by=","  --lines-terminated-by="\n"  test test.txt

• 指定字段顺序将数据导入到表中

mysqlimport -u root -p --local --columns=b,c,a test test.txt

mysqlimport支持的参数选项如下:

选项	功能
-v	显示版本
-p	提示输入密码
-d or --delete	新数据导入数据表中之前删除数据表中的所有信息
-f or --force	不管是否遇到错误，mysqlimport将强制继续插入数据
-i or --ignore	导入数据时忽略有相同唯一键的行
-l or -lock-tables	数据被插入之前锁住表,防止插入数据时查询和更新受影响
-r or -replace	若表中存在有相同唯一键的行，则替换
--fields-enclosed-by=	使用什么符号将数据括起来，使用""表示使用双引号将数据括起来
--fields-terminated-by=	使用什么符号分割数据字段，默认分隔符是Tab，常用逗号
--lines-terminated-by=	使用什么符号进行行之间的分隔符，例如"\n"表示换行符

五、使用mysql命令导入数据

导入codingway整个数据库

mysql -uUser -pPassword < codingway.sql

六、使用source命令导入数据

导入codingway整个数据库

source /home/codingway.sql

七、使用load data导入数据

• 将数据导入到当前数据库的test表

load data local infile 'test.txt' into table test;

• 指定格式将数据导入到当前数据库的test表

load data local infile 'test.txt' into table(a,b,c) test fields terminated by ',' lines terminated by '\n';

八、数据导入的注意事项

• 注意导出和导入的数据格式必须保持一致，例如均使用逗号分隔字段，使用\n分隔行，使用双引号包含数据
• 需要注意数据库集群的事务特性，例如MGR(Mysql Group Replication)集群适应小事务的情况，则导入数据时不宜导入过于大量的数据，应分多次小数据量导入。