MYSQL LOAD DATA INFILE 忽略重复行（自动增量为主键）

使用MYSQL的LOAD DATA INFILE命令可以方便地将数据从文件导入到数据库中。然而，有时候我们希望在导入过程中忽略重复行，特别是当我们的表中有自动增量的主键时。下面将介绍如何在使用LOAD DATA INFILE命令时忽略重复行，并提供一个案例代码。

LOAD DATA INFILE 'filename'
IGNORE
INTO TABLE tablename
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(column1, column2, column3, ...)

sql
CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
);

1,John
2,Alice
3,Bob
1,John
4,Mike

sql
LOAD DATA INFILE 'students.csv'
IGNORE
INTO TABLE students
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(id, name);

sql
SELECT * FROM students;

+----+-------+
| id | name  |
+----+-------+
| 1  | John  |
| 2  | Alice |
| 3  | Bob   |
| 4  | Mike  |
+----+-------+

sql
CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
);
LOAD DATA INFILE 'students.csv'
IGNORE
INTO TABLE students
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(id, name);
SELECT * FROM students;