mysqlloaddatainfile的用法(40w数据用了3-5秒导进mysql)
在使用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)
--EOF--
相关文章
- MYSQL 如何删除表中重复数据
- MySQL 中查询字符长度的方法(mysql查询字符长度)
- 浅析MySQL中删除某条数据的方法(mysql删除某条数据)
- MySQL查询:掌握事务处理技巧(mysql查询事务)
- 落MySQL数据库:让数据落地生根(mysql数据集)
- 快速学习:使用C与MySQL查询数据(cmysql查询数据)
- MySQL时间函数:提高数据库效率(mysql数据库时间函数)
- MySQL数据库分片技术实战(mysql数据分片)
- 如何快速从MySQL导出数据(从mysql导出数据)
- 快速导出MySQL表中指定数据(mysql导出指定表数据)
- MySQL导出文本数据的步骤指南(mysql导出文本数据)
- MySQL下标从0开始(mysql下标从几开始)
- 类型MySQL中的二进制数据类型及其应用(mysql二进制数据)
- 远程访问MySQL数据库:实现简单而快速(访问远程mysql数据库)
- MySQL数据迁移——高效方案探究(mysql数据迁移方案)
- 使用PHP连接MySQL数据库实现数据操作(php连接mysql类)
- MySQL查询字符编码,了解数据存储原理(mysql查询字符编码)
- MySQL实现PHP数组存储的效果(mysql存储数组php)
- MySQL如何插入中文数据(mysql插入中文数据)
- MySQL与游戏——数据存储的重要性(mysql游戏)
- MySQL双表联查了解基本语法和应用方法(mysql中两表联查)
- MySQL中如何避免重复数据输入(mysql中不允许重复)
- MySQL中使用LIKE搜索数据(mysql中like查找)
- MySQL中出现错误怎么办错误解决方法(mysql中errors)
- ASP编程查询MySQL之道(asp查询mysql)
- 快速学习MySQL语法以ADO为桥梁(ado写mysql语法)
- 使用MySQL中的一张表作为条件,进一步查询和筛选数据(mysql一张表作为条件)
- MySQL一主多从多点备份与负载均衡实现(mysql一主多从介绍)
- 如何实现Mysql一主二从配置(mysql一主二从配置)
- MySQL双数据查询实现数据联合检索(mysql 两数据库查询)
- MySQL存储数据的方法简介(mysql上传保存数据)