mysql load data 主键_mysql load 主键验证

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)