作为DBA，经常会碰到导入导出数据的需求，本篇就介绍了mysqldump和load data这两种方法。使用mysqldump时可以加上一些参数，dump需要的数据。重点介绍了使用load data导入时遇到的2个问题及解决方法。
1、 整个库的导出导入
用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’;
改变终端字符集，查看中文显示正常。
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2011-8-2 9:11:08 |
赞(
操作
