霜天部落 | 专注PHP研发,研究LAMP高性能架构部署与优化

MySQL数据导入导出

作为DBA,经常会碰到MySQL导入导出数据的需求,本篇就介绍了mysqldump和load data这两种方法。

使用mysqldump时可以加上一些参数,dump需要的数据。另外重点介绍了使用load data导入时遇到的2个问题及解决方法。

1、 整个MYSQL库的导出导入

用mysqldump命令,可以只导出表结构,也可以连数据一起导出,看需求。可以用mysqldump 导出整个库,也可以导出单个表。

例如:将整个test库导出,包括表结构、数据:

[mysql@my101 data]$ mysqldump -uroot test > test.txt

将test库中导出的所有表、数据,导入到pyt库:

[mysql@my101 data]$ mysql -uroot pyt < test.txt 只导出表结构: [mysql@my101 data]$ mysqldump -uroot –no-data test > test.txt

只导出a表的数据:

[mysql@my101 data]$ mysqldump -uroot –no-create-info test a> test.txt

【注意】:用mysqldump,如果不加任何参数,dump出来的文件,如果存在表则会先drop table,然后再create table ,最后insert数据。所以要特别注意。可以通过添加参数去掉drop或者直接去掉create,如mysqldump –no-create-info 、mysqldump –add-drop-table=’false’ ,当然最安全最正确的做法是导入之前先检查文件,是否存在drop等命令会破坏原有表。

2、 单表的导出导入

Mysqldump就不说了;说说load data。

一般数据导出txt格式:

Select * from … into outfile ‘a.txt’ fields terminated by ‘,’ optionally enclosed by ‘”‘;

也可以导出csv格式,方便用户以excel表格形式查看数据(这里字段间以逗号分隔):

select * from xy2 into outfile ‘/tmp/xy2.csv’ fields terminated by ‘,’;

数据导入:

1)load data infile ‘/tmp/tab_a.txt’ into table tab_a fields terminated by ‘,’ optionally enclosed by ‘”‘ lines terminated by ‘\n’ (id,name,gmt_create) set gmt_modified=gmt_create;

其中:fields terminated by ‘,’是指字段间以逗号分隔;

optionally enclosed by ‘”‘表示:字符型日期型的字段会加””。如果是ENCLOSED BY ‘”‘的话,所有的字段都会加”, ENCLOSED BY描述的是字段的括起字符。比方以引号括起每一个字段。

Load时还可以加上括号指定字段,比如文件里没有gmt_modified值,还可以使用set来解决。

2)当要忽视原来表中的数据时,可以用replace 或者ignore:

load data infile ‘/tmp/w6.txt’ replace into table tab_b fields terminated by ‘\t’;

3)将excel文件导入:

先将excle文件保存为.csv文件;然后上传到目录下;通过load data infile ‘…’ into table … fields terminated by ‘,’;导入数据。这里数据库的表可以不是CSV引擎的。

3、 使用load data时,遇到的2个问题

1)在windows下导出文件,要导入linux表出现警告。

在windows下导出的数据文件mod0.txt,上传到Linux,查看文件:

“10371313″,”t_maybe”,”07/17/2009 07:28:50″,”12/07/2009 20:20:25″,”abc”

“10812978″,”t_read”,”07/06/2009 21:32:16″,”12/03/2009 21:22:55″,”def”

………………………………………………………………

在Linux下load data 一直有警告:

mysql> load data infile ‘/tmp/mod0.txt’ into table a0 FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\n’;

Query OK, 836644 rows affected, 65535 warnings (22.42 sec)

Records: 836644 Deleted: 0 Skipped: 0 Warnings: 2509933

mysql> select * from a0 limit 2;

+———–+——————+————+—————-+—————-+

| user_id | real_name | gmt_create | gmt_modified | nick |

+———–+—————-+—————-+—————-+————-+

| 10371313 | t_maybe | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | abc”

“10812978 |

| 0 | t_read | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |def”

“238102432 |

+———–+————-+—————-+—————–+————–+

2 rows in set (0.00 sec)

解决:windows下导出的数据行的结束多了符号,要用【dos2unix】命令转换成Linux格式

mysql@my101 tmp]$ dos2unix -o mod1.txt

dos2unix: converting file mod1.txt to UNIX format …

再导入就正确了:

mysql> load data infile ‘/tmp/mod0.txt’ into table a0 FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\n’;

Query OK, 1673286 rows affected, 65535 warnings (33.74 sec)

Records: 1673286 Deleted: 0 Skipped: 0 Warnings: 3346572

(这里是时间格式不对,所以导致警告,没关系的。)

mysql> select * from a0 limit 2;

+———–+——————+—————-+—————+——–+

| user_id | real_name | gmt_create | gmt_modified | nick |

+———–+——————+————-+——————-+——–+

| 10371313 | t_maybe | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | abc |

| 10812978 | t_read | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | def |

+———–+——————+—————–+—————-+——–+

2)导出的文件和要导入的表字符集不一致时出现警告

一开始导入文件一直有警告:

root@pyt 04:36:49>load data infile ’/tmp/xy.txt’ into table xy fields terminated by ’,’ optionally enclosed by ’\” lines terminated by ’\n’;
Query OK, 188 rows affected, 906 warnings (0.00 sec)
Records: 188 Deleted: 0 Skipped: 0 Warnings: 607

root@pyt 04:37:08>show warnings;
+———+——+—————————————————————————————+
| Level | Code | Message |
+———+——+—————————————————————————————+
| Warning | 1366 | Incorrect string value: ’\xBC\xD3\xD6\xAE’ for column ’create_user’ at row 1 |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column ’update_time’ at row 1 |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column ’update_user’ at row 1 |
| Warning | 1366 | Incorrect string value: ’\xC0\xED\xB9\xA4\xBF\xC6…’ for column ’obj_title’ at row 1 |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column ’memo’ at row 1

主要就2个警告,一个是因为将空的字段导入到not null约束的列中,无大碍。另一个警告是字符串不正确,想到可能是因为字符集不一致造成的。

在linux直接less打开文件,发现是乱码,SecureCRT终端字符集是gbk的。查看linux环境字符集,

$locale

LANG=en_US.UTF-8

发现是utf8,通过export LANG=zh_CN.GBK 改成gbk后再看该文件,中文显示正常,说明该文件确实是gbk字符集的。

现在要将gbk文件导入utf8表怎么办呢?

有2个办法:一是将gbk文件转换成utf8文件再导入

用【iconv】命令改变文件的字符类型:

$iconv -f=gbk -t=utf-8 xy.txt

然后将终端外观字符集编码改成utf8就能看到中文显示正常,再执行load data就可以了。

另外一个方法是:直接将文件导入utf8表,加上【CHARACTER SET gbk】:

load data infile ‘/tmp/xy.txt’ into table xy CHARACTER SET gbk fields terminated by ‘,’ optionally enclosed by ‘\” lines terminated by ‘\n’;

改变终端字符集,查看中文显示正常。

原文链接:http://www.taobaodba.com/html/558_loaddata.html