mysql load 主键验证_MySQL

load data LOCAL infile '~/Documents/load_test.txt' INTO TABLE test FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '/n';

1.如果id不是主键，会不停增加。
mysql> load data LOCAL infile '~/Documents/load_test.txt' INTO TABLE test FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '/n';
Query OK, 2 rows affected (0.02 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from test;
+------+-----------+
| id | name |
+------+-----------+
| 1 | wang |
| 2 | xia |
| 3 | wang-load |
| 4 | xia-load |
+------+-----------+
4 rows in set (0.00 sec)

mysql> load data LOCAL infile '~/Documents/load_test.txt' INTO TABLE test FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '/n';
Query OK, 2 rows affected (0.04 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from test;+------+-----------+
| id | name |
+------+-----------+
| 1 | wang |
| 2 | xia |
| 3 | wang-load |
| 4 | xia-load |
| 3 | wang-load |
| 4 | xia-load |
+------+-----------+
6 rows in set (0.00 sec)


2.如果id是主键，会skip
mysql> alter table test add PRIMARY KEY (id);
Query OK, 2 rows affected (0.38 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from test;+----+------+
| id | name |
+----+------+
| 1 | wang |
| 2 | xia |
+----+------+
2 rows in set (0.00 sec)

mysql> load data LOCAL infile '~/Documents/load_test.txt' INTO TABLE test FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '/n';
Query OK, 2 rows affected (0.05 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from test;+----+-----------+
| id | name |
+----+-----------+
| 1 | wang |
| 2 | xia |
| 3 | wang-load |
| 4 | xia-load |
+----+-----------+
4 rows in set (0.00 sec)



修改TXT文件
mysql> load data LOCAL infile '~/Documents/load_test.txt' INTO TABLE test FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '/n';Query OK, 0 rows affected (0.00 sec)
Records: 2 Deleted: 0 Skipped: 2 Warnings: 0

mysql> select * from test;+----+-----------+
| id | name |
+----+-----------+
| 1 | wang |
| 2 | xia |
| 3 | wang-load |
| 4 | xia-load |
+----+-----------+
4 rows in set (0.00 sec)