Chèn mysql vào khóa trùng lặp bỏ qua

MySQL cung cấp một số câu lệnh hữu ích khi cần INSERT hàng sau khi xác định xem hàng đó thực tế là mới hay đã tồn tại

Dưới đây chúng ta sẽ xem xét ba phương pháp khác nhau và lần lượt giải thích ưu và nhược điểm của từng phương pháp để bạn nắm vững cách định cấu hình các câu lệnh của riêng mình khi cung cấp dữ liệu mới hoặc dữ liệu có khả năng tồn tại cho

mysql> INSERT INTO books
    (id, title, author, year_published)
VALUES
    (1, 'Green Eggs and Ham', 'Dr. Seuss', 1960);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
0

Sử dụng INSERT IGNORE

Việc sử dụng INSERT IGNORE khiến MySQL bỏ qua các lỗi thực thi một cách hiệu quả trong khi cố gắng thực hiện các câu lệnh INSERT. Điều này có nghĩa là một câu lệnh INSERT IGNORE chứa một giá trị trùng lặp trong chỉ mục

mysql> INSERT INTO books
    (id, title, author, year_published)
VALUES
    (1, 'Green Eggs and Ham', 'Dr. Seuss', 1960);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
5 hoặc trường
mysql> INSERT INTO books
    (id, title, author, year_published)
VALUES
    (1, 'Green Eggs and Ham', 'Dr. Seuss', 1960);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
6 không tạo ra lỗi, mà thay vào đó sẽ bỏ qua hoàn toàn lệnh INSERT cụ thể đó. Mục đích rõ ràng là để thực hiện một số lượng lớn các câu lệnh INSERT cho sự kết hợp của dữ liệu đã tồn tại trong cơ sở dữ liệu cũng như dữ liệu mới được đưa vào hệ thống

Ví dụ: bảng

mysql> INSERT INTO books
    (id, title, author, year_published)
VALUES
    (1, 'Green Eggs and Ham', 'Dr. Seuss', 1960);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
9 của chúng tôi có thể đã chứa một vài bản ghi

mysql> SELECT * FROM books LIMIT 3;
+----+-------------------------+---------------------+----------------+
| id | title                   | author              | year_published |
+----+-------------------------+---------------------+----------------+
|  1 | In Search of Lost Time  | Marcel Proust       |           1913 |
|  2 | Ulysses                 | James Joyce         |           1922 |
|  3 | Don Quixote             | Miguel de Cervantes |           1605 |
+----+-------------------------+---------------------+----------------+
3 rows in set (0.00 sec)

Nếu chúng tôi có một lô lớn dữ liệu mới và hiện có cho INSERT và một phần của dữ liệu đó chứa giá trị phù hợp cho trường

mysql> INSERT IGNORE INTO books
    (id, title, author, year_published)
VALUES
    (1, 'Green Eggs and Ham', 'Dr. Seuss', 1960);
Query OK, 0 rows affected (0.00 sec)
1 (là
mysql> INSERT INTO books
    (id, title, author, year_published)
VALUES
    (1, 'Green Eggs and Ham', 'Dr. Seuss', 1960);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
5
mysql> INSERT IGNORE INTO books
    (id, title, author, year_published)
VALUES
    (1, 'Green Eggs and Ham', 'Dr. Seuss', 1960);
Query OK, 0 rows affected (0.00 sec)
3 trong bảng), việc sử dụng một INSERT cơ bản sẽ tạo ra lỗi dự kiến

mysql> INSERT INTO books
    (id, title, author, year_published)
VALUES
    (1, 'Green Eggs and Ham', 'Dr. Seuss', 1960);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

Mặt khác, nếu chúng tôi sử dụng INSERT IGNORE, nỗ lực sao chép sẽ bị bỏ qua và không có lỗi nào xảy ra

mysql> INSERT IGNORE INTO books
    (id, title, author, year_published)
VALUES
    (1, 'Green Eggs and Ham', 'Dr. Seuss', 1960);
Query OK, 0 rows affected (0.00 sec)

Sử dụng REPLACE

Trong trường hợp bạn thực sự muốn thay thế các hàng trong đó các lệnh INSERT sẽ tạo ra lỗi do trùng lặp các giá trị

mysql> INSERT INTO books
    (id, title, author, year_published)
VALUES
    (1, 'Green Eggs and Ham', 'Dr. Seuss', 1960);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
5 hoặc
mysql> INSERT INTO books
    (id, title, author, year_published)
VALUES
    (1, 'Green Eggs and Ham', 'Dr. Seuss', 1960);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
6 như đã nêu ở trên, một tùy chọn là chọn câu lệnh REPLACE

Khi đưa ra một câu lệnh REPLACE, có hai kết quả có thể xảy ra cho mỗi lệnh được đưa ra

  • Không tìm thấy hàng dữ liệu hiện có nào có giá trị phù hợp và do đó, một câu lệnh INSERT tiêu chuẩn được thực hiện
  • Một hàng dữ liệu phù hợp được tìm thấy, làm cho hàng hiện có đó bị xóa bằng câu lệnh
    mysql> REPLACE INTO books
        (id, title, author, year_published)
    VALUES
        (1, 'Green Eggs and Ham', 'Dr. Seuss', 1960);
    Query OK, 2 rows affected (0.00 sec)
    
    3 tiêu chuẩn, sau đó một INSERT bình thường được thực hiện sau đó

Ví dụ: chúng ta có thể sử dụng REPLACE để hoán đổi bản ghi hiện có của chúng ta về

mysql> REPLACE INTO books
    (id, title, author, year_published)
VALUES
    (1, 'Green Eggs and Ham', 'Dr. Seuss', 1960);
Query OK, 2 rows affected (0.00 sec)
6 cuốn In Search of Lost Time của Marcel Proust với Green Eggs and Ham của Dr. Seuss

mysql> REPLACE INTO books
    (id, title, author, year_published)
VALUES
    (1, 'Green Eggs and Ham', 'Dr. Seuss', 1960);
Query OK, 2 rows affected (0.00 sec)

Lưu ý rằng mặc dù chúng tôi chỉ thay đổi một hàng, nhưng kết quả cho thấy có hai hàng bị ảnh hưởng bởi vì chúng tôi thực sự

mysql> REPLACE INTO books
    (id, title, author, year_published)
VALUES
    (1, 'Green Eggs and Ham', 'Dr. Seuss', 1960);
Query OK, 2 rows affected (0.00 sec)
7 hàng hiện tại sau đó
mysql> REPLACE INTO books
    (id, title, author, year_published)
VALUES
    (1, 'Green Eggs and Ham', 'Dr. Seuss', 1960);
Query OK, 2 rows affected (0.00 sec)
8 hàng mới để thay thế hàng đó

Thông tin thêm về cách sử dụng REPLACE có thể được tìm thấy trong tài liệu chính thức

Sử dụng INSERT .. ON DUPLICATE KEY UPDATE

Phương pháp thay thế (và thường được ưu tiên) cho các hàng

mysql> SET @id = 1,
    @title = 'In Search of Lost Time',
    @author = 'Marcel Proust',
    @year_published = 1913;
INSERT INTO books
    (id, title, author, year_published)
VALUES
    (@id, @title, @author, @year_published)
ON DUPLICATE KEY UPDATE
    title = @title,
    author = @author,
    year_published = @year_published;
1 có thể chứa các giá trị
mysql> INSERT INTO books
    (id, title, author, year_published)
VALUES
    (1, 'Green Eggs and Ham', 'Dr. Seuss', 1960);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
5 hoặc
mysql> INSERT INTO books
    (id, title, author, year_published)
VALUES
    (1, 'Green Eggs and Ham', 'Dr. Seuss', 1960);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
6 trùng lặp là sử dụng câu lệnh và mệnh đề INSERT .. ON DUPLICATE KEY UPDATE

Không giống như REPLACE – một lệnh phá hoại cố hữu do các lệnh

mysql> REPLACE INTO books
    (id, title, author, year_published)
VALUES
    (1, 'Green Eggs and Ham', 'Dr. Seuss', 1960);
Query OK, 2 rows affected (0.00 sec)
3 mà nó thực hiện khi cần thiết – sử dụng INSERT .. ON DUPLICATE KEY UPDATE không mang tính phá hủy, ở chỗ nó sẽ chỉ đưa ra các câu lệnh INSERT hoặc
mysql> SET @id = 1,
    @title = 'In Search of Lost Time',
    @author = 'Marcel Proust',
    @year_published = 1913;
INSERT INTO books
    (id, title, author, year_published)
VALUES
    (@id, @title, @author, @year_published)
ON DUPLICATE KEY UPDATE
    title = @title,
    author = @author,
    year_published = @year_published;
9, nhưng không bao giờ
mysql> REPLACE INTO books
    (id, title, author, year_published)
VALUES
    (1, 'Green Eggs and Ham', 'Dr. Seuss', 1960);
Query OK, 2 rows affected (0.00 sec)
3

Ví dụ: chúng tôi đã quyết định muốn thay thế bản ghi

mysql> REPLACE INTO books
    (id, title, author, year_published)
VALUES
    (1, 'Green Eggs and Ham', 'Dr. Seuss', 1960);
Query OK, 2 rows affected (0.00 sec)
6 về Green Eggs and Ham và hoàn nguyên nó về bản ghi gốc Tìm kiếm thời gian đã mất để thay thế. Do đó, chúng tôi có thể lấy câu lệnh INSERT ban đầu của mình và thêm mệnh đề
mysql> SELECT * FROM books LIMIT 1;
+----+------------------------+---------------+----------------+
| id | title                  | author        | year_published |
+----+------------------------+---------------+----------------+
|  1 | In Search of Lost Time | Marcel Proust |           1913 |
+----+------------------------+---------------+----------------+
1 row in set (0.00 sec)
3 mới

mysql> SET @id = 1,
    @title = 'In Search of Lost Time',
    @author = 'Marcel Proust',
    @year_published = 1913;
INSERT INTO books
    (id, title, author, year_published)
VALUES
    (@id, @title, @author, @year_published)
ON DUPLICATE KEY UPDATE
    title = @title,
    author = @author,
    year_published = @year_published;

Lưu ý rằng chúng tôi đang sử dụng cú pháp

mysql> SET @id = 1,
    @title = 'In Search of Lost Time',
    @author = 'Marcel Proust',
    @year_published = 1913;
INSERT INTO books
    (id, title, author, year_published)
VALUES
    (@id, @title, @author, @year_published)
ON DUPLICATE KEY UPDATE
    title = @title,
    author = @author,
    year_published = @year_published;
9 bình thường (nhưng loại trừ tên
mysql> SELECT * FROM books LIMIT 1;
+----+------------------------+---------------+----------------+
| id | title                  | author        | year_published |
+----+------------------------+---------------+----------------+
|  1 | In Search of Lost Time | Marcel Proust |           1913 |
+----+------------------------+---------------+----------------+
1 row in set (0.00 sec)
5 và từ khóa
mysql> SELECT * FROM books LIMIT 1;
+----+------------------------+---------------+----------------+
| id | title                  | author        | year_published |
+----+------------------------+---------------+----------------+
|  1 | In Search of Lost Time | Marcel Proust |           1913 |
+----+------------------------+---------------+----------------+
1 row in set (0.00 sec)
6 không cần thiết) và chỉ gán các giá trị
mysql> SELECT * FROM books LIMIT 1;
+----+------------------------+---------------+----------------+
| id | title                  | author        | year_published |
+----+------------------------+---------------+----------------+
|  1 | In Search of Lost Time | Marcel Proust |           1913 |
+----+------------------------+---------------+----------------+
1 row in set (0.00 sec)
7. Ngoài ra, mặc dù không cần thiết để phương thức
mysql> SELECT * FROM books LIMIT 1;
+----+------------------------+---------------+----------------+
| id | title                  | author        | year_published |
+----+------------------------+---------------+----------------+
|  1 | In Search of Lost Time | Marcel Proust |           1913 |
+----+------------------------+---------------+----------------+
1 row in set (0.00 sec)
3 hoạt động bình thường, chúng tôi cũng đã chọn sử dụng
mysql> SELECT * FROM books LIMIT 1;
+----+------------------------+---------------+----------------+
| id | title                  | author        | year_published |
+----+------------------------+---------------+----------------+
|  1 | In Search of Lost Time | Marcel Proust |           1913 |
+----+------------------------+---------------+----------------+
1 row in set (0.00 sec)
9 vì vậy chúng tôi không cần chỉ định các giá trị thực mà chúng tôi muốn INSERT hoặc
mysql> SET @id = 1,
    @title = 'In Search of Lost Time',
    @author = 'Marcel Proust',
    @year_published = 1913;
INSERT INTO books
    (id, title, author, year_published)
VALUES
    (@id, @title, @author, @year_published)
ON DUPLICATE KEY UPDATE
    title = @title,
    author = @author,
    year_published = @year_published;
9 nhiều lần