最新消息:时间抓起来说是金子,抓不住就是流水。

MYSQL数据导入导出

MySql 老子黑牵翻 21314浏览 6评论

作为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’; 

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

导出整个数据库结构和数据
mysqldump -h localhost -uroot -p123456 database > dump.sql
     
导出单个数据表结构和数据
mysqldump -h localhost -uroot -p123456  database table > dump.sql
      
导出整个数据库结构(不包含数据)
mysqldump -h localhost -uroot -p123456  -d database > dump.sql
     
导出单个数据表结构(不包含数据)
mysqldump -h localhost -uroot -p123456  -d database table > dump.sql
     
只导出数据(不包含表结构)
mysqldump -t database -h localhost -uroot -p123456 > dump.sql

转载请注明:LINUX服务器运维架构技术分享 » MYSQL数据导入导出

发表我的评论
取消评论

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

网友最新评论 (6)

  1. Hey There. I found your blog using msn. This is a very well written article. I'll be sure to bookmark it and return to read more of 301 Moved Permanently . Thanks for the post. I'll certainly comeback.
    Inspirational Quotes10年前 (2012-03-26)回复