mysql load 主键验证

2019独角兽企业重金招聘Python工程师标准>>>

 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)

转载于:https://my.oschina.net/wangjiankui/blog/264468