mysql load data infile 的用法(40w數據 用了3-5秒導進mysql)。本站提示廣大學習愛好者：（mysql load data infile 的用法(40w數據 用了3-5秒導進mysql)）文章只能為提供參考，不一定能成為您想要的結果。以下是mysql load data infile 的用法(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 '"';
不曉得用replace 這個症結字的話，照樣會亂碼。。分歧、等高手答復。
在具體的引見，推舉年夜家去看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--
