[mysql] LOAD DATA

ubuntuer

在使用LOAD DATA到MySQL的時候，有2種情況：

（1）在遠(yuǎn)程客戶端（需要添加選項：–local-infile=1）導(dǎo)入遠(yuǎn)程客戶端文本到MySQL，需指定LOCAL（默認(rèn)就是ignore）,加ignore選項會放棄數(shù)據(jù)，加replace選項會更新數(shù)據(jù)，都不會出現(xiàn)唯一性約束問題。

（2）在本地服務(wù)器導(dǎo)入本地服務(wù)器文本到MySQL，不指定LOACL，出現(xiàn)唯一性約束沖突，會失敗回滾，數(shù)據(jù)導(dǎo)入不進(jìn)去，這個時候就需要加ignore或者replace來導(dǎo)入數(shù)據(jù)。

測試如下：

（1）本地服務(wù)器導(dǎo)入本地服務(wù)器文本

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’

# 出現(xiàn)唯一性約束沖突，會失敗回滾

mysql> select * from tmp_loaddata;

+—-+——+

| id | name |

+—-+——+

| 1 | test |

+—-+——+

1 row in set (0.00 sec)

mysql> LOAD DATA INFILE ‘/home/zhuxu/1.txt’ IGNORE INTO TABLE tmp_loaddata FIELDS TERMINATED BY ‘,’;

Query OK, 1 row affected (0.00 sec)

Records: 2 Deleted: 0 Skipped: 1 Warnings: 0

# 使用IGNORE對于沖突的數(shù)據(jù)丟棄掉。

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’ REPLACE INTO TABLE tmp_loaddata FIELDS TERMINATED BY ‘,’;

Query OK, 3 rows affected (0.00 sec)

Records: 2 Deleted: 1 Skipped: 0 Warnings: 0

# 使用REPLACE對于沖突的數(shù)據(jù)進(jìn)行更新。

mysql> select * from tmp_loaddata;

+—-+————+

| id | name |

+—-+————+

| 1 | new update |

| 2 | new update |

+—-+————+

2 rows in set (0.00 sec)

（2）遠(yuǎn)程客戶端導(dǎo)入遠(yuǎn)程客戶端文本

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

# 由于數(shù)據(jù)庫服務(wù)器沒有對應(yīng)的文本文件，所以報錯。

mysql>

mysql> LOAD DATA LOCAL INFILE ‘/tmp/2.txt’ INTO TABLE tmp_loaddata FIELDS TERMINATED BY ‘,’;

ERROR 1148 (42000): The used command is not allowed with this MySQL version

# 進(jìn)去mysql遠(yuǎn)程客戶端，還需要加–local-infile=1參數(shù)指定。

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: 3 Deleted: 0 Skipped: 1 Warnings: 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’ IGNORE INTO 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: 3 Deleted: 0 Skipped: 3 Warnings: 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’ REPLACE INTO 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: 3 Deleted: 1 Skipped: 0 Warnings: 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–