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--