引言
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)集群适应小事务的情况,则导入数据时不宜导入过于大量的数据,应分多次小数据量导入。
网友评论