注册 登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

曾国藩的博客

 
 
 

日志

 
 

mysql load data infile (40w数据 3-5秒导入mysql)  

2013-03-05 10:47:15|  分类: SQL/Oracle/Mysql |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
http://www.jbxue.com/db/5875.html

测试数据时,生成txt文件应该快点,再用这种方式导入到mysql 速度上快点。40w数据 用了3-5秒导入mysql,超级赞。

如果是导入有中文的数据,我的mysql 设置的utf8 字符集,所以你要导入的xxx.txt 文件也要保存utf-8的字符集,命令:
 

复制代码 代码如下:
load data infile "d:/Websites/Sxxxx/test1.txt" ignore into table `names` fields terminated by ',' enclosed by '"';

在使用LOAD DATA到MySQL的时候,有2种情况:

(1)在远程客户端(需要添加选项:--local-infile=1)导入远程客户端文本到MySQL,需指定LOCAL(默认就是ignore),加ignore选项会放弃数据,加replace选项会更新数据,都不会出现唯一性约束问题。

(2)在本地服务器导入本地服务器文本到MySQL,不指定LOACL,出现唯一性约束冲突,会失败回滚,数据导入不进去,这个时候就需要加ignore或者replace来导入数据。

测试如下:
(1)本地服务器导入本地服务器文本
 

复制代码 代码如下:

mysql> show create table tmp_loaddata\G;
*************************** 1. row ***************************
Table: tmp_loaddata
Create Table:CREATE TABLE `tmp_loaddata` (
`id` int(11) NOT NULL,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
ERROR:
No query specified

mysql>select * from tmp_loaddata;
+----+------+
| id | name |
+----+------+
|1 | test |
+----+------+
1 row in set (0.00 sec)

mysql>
mysql>system cat /home/zhuxu/1.txt
1,new update
2,new update

mysql>
mysql>LOAD DATA INFILE '/home/zhuxu/1.txt' INTO TABLE tmp_loaddata FIELDS TERMINATED BY ',';
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
#出现唯一性约束冲突,会失败回滚

mysql>select * from tmp_loaddata;
+----+------+
| id | name |
+----+------+
|1 | test |
+----+------+
1 row in set (0.00 sec)

mysql>LOAD DATA INFILE '/home/zhuxu/1.txt' IGNOREINTO TABLE tmp_loaddata FIELDS TERMINATED BY ',';
Query OK,1 row affected(0.00 sec)
Records: 2Deleted: 0Skipped: 1Warnings: 0
#使用IGNORE对于冲突的数据丢弃掉。

mysql>select * from tmp_loaddata;
+----+------------+
| id | name|
+----+------------+
|1 | test|
|2 | new update |
+----+------------+

2 rows in set (0.00 sec)
mysql>LOAD DATA INFILE '/home/zhuxu/1.txt' REPLACEINTO TABLE tmp_loaddata FIELDS TERMINATED BY ',';
Query OK,3 rows affected(0.00 sec)
Records: 2Deleted: 1Skipped: 0Warnings: 0
#使用REPLACE对于冲突的数据进行更新。

mysql>select * from tmp_loaddata;
+----+------------+
| id | name|
+----+------------+
|1 | new update |
|2 | new update |
+----+------------+

2 rows in set (0.00 sec)
 

(2)远程客户端导入远程客户端文本
 

复制代码 代码如下:

[zhuxu@xentest9-vm1 tmp]$mysql -uzhuxu -pzhuxu test -h10.254.5.151
Welcome to the MySQL monitor.Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.1.47-log Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>select * from tmp_loaddata;
+----+------+
| id | name |
+----+------+
|1 | test |
+----+------+
1 row in set (0.00 sec)

mysql>system cat /tmp/2.txt
1,new update
2,new update
3,new update

mysql>
mysql>LOAD DATA INFILE '/tmp/2.txt' INTO TABLE tmp_loaddata FIELDS TERMINATED BY ',';
ERROR 13 (HY000): Can't get stat of '/tmp/2.txt' (Errcode: 2)
#由于数据库服务器没有对应的文本文件,所以报错。

mysql>
mysql>LOAD DATA LOCALINFILE '/tmp/2.txt' INTO TABLE tmp_loaddata FIELDS TERMINATED BY ',';
ERROR 1148 (42000): The used command is not allowed with this MySQL version
#进去mysql远程客户端,还需要加--local-infile=1参数指定。
mysql> exit
Bye
[zhuxu@xentest9-vm1 tmp]$mysql -uzhuxu -pzhuxu test -h10.254.5.151 --local-infile=1--show-warnings -v -v -v \
> -e "LOAD DATA LOCAL INFILE '/tmp/2.txt' INTO TABLE tmp_loaddata FIELDS TERMINATED BY ','";
--------------
LOAD DATA LOCAL INFILE '/tmp/2.txt' INTO TABLE tmp_loaddata FIELDS TERMINATED BY ','
--------------
Query OK,2 rows affected(0.00 sec)
Records: 3Deleted: 0Skipped: 1Warnings: 0
Bye

mysql>select * from tmp_loaddata;
+----+------------+
| id | name|
+----+------------+
|1 | test|
|2 | new update |
|3 | new update |
+----+------------+
3 rows in set (0.00 sec)
#

[zhuxu@xentest9-vm1 tmp]$mysql -uzhuxu -pzhuxu test -h10.254.5.151 --local-infile=1--show-warnings -v -v -v \

> -e "LOAD DATA LOCAL INFILE '/tmp/2.txt' IGNOREINTO TABLE tmp_loaddata FIELDS TERMINATED BY ','";

--------------

LOAD DATA LOCAL INFILE '/tmp/2.txt' IGNORE INTO TABLE tmp_loaddata FIELDS TERMINATED BY ','

--------------

Query OK,0 rows affected(0.00 sec)

Records: 3Deleted: 0Skipped: 3Warnings: 0

Bye

mysql>select * from tmp_loaddata;

+----+------------+

| id | name|

+----+------------+

|1 | test|

|2 | new update |

|3 | new update |

+----+------------+

3 rows in set (0.00 sec)

#

[zhuxu@xentest9-vm1 tmp]$mysql -uzhuxu -pzhuxu test -h10.254.5.151 --local-infile=1--show-warnings -v -v -v \

> -e "LOAD DATA LOCAL INFILE '/tmp/2.txt' REPLACEINTO TABLE tmp_loaddata FIELDS TERMINATED BY ','";

--------------

LOAD DATA LOCAL INFILE '/tmp/2.txt' REPLACE INTO TABLE tmp_loaddata FIELDS TERMINATED BY ','

--------------

Query OK,4 rows affected(0.00 sec)

Records: 3Deleted: 1Skipped: 0Warnings: 0

Bye

mysql> select * from tmp_loaddata;

+----+------------+

| id | name|

+----+------------+

|1 | new update |

|2 | new update |

|3 | new update |

+----+------------+

3 rows in set (0.00 sec)


  评论这张
 
阅读(470)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018