MySQL CHÈN BỎ QUA TRÊN KHÓA DUPLICATE

Question

Cơ sở dữ liệu MySQL hỗ trợ một cách rất thuận tiện để CHÈN hoặc CẬP NHẬT một bản ghi. Đây là phần mở rộng không chuẩn cho cú pháp SQL, được hỗ trợ bởi jOOQ và được mô phỏng trong RDBMS khác, nếu điều này có thể thực hiện được (e. g. nếu chúng hỗ trợ câu lệnh MERGE tiêu chuẩn SQL). Đây là một ví dụ về cách sử dụng mệnh đề ON DUPLICATE KEY UPDATE

Nội dung chính Show

Sử dụng INSERT IGNORE
Sử dụng REPLACE
Sử dụng INSERT .. ON DUPLICATE KEY UPDATE
Làm cách nào để tránh các bản ghi trùng lặp khi chèn vào SQL Server?
Làm cách nào để kiểm tra các bản ghi trùng lặp trong SQL trước khi chèn?
Chèn vào bản cập nhật khóa trùng lặp là gì?
Bỏ qua chèn có nhanh hơn chèn không?

// Add a new author called "Koontz" with ID 3.
// If that ID is already present, update the author's name
create.insertInto(AUTHOR, AUTHOR.ID, AUTHOR.LAST_NAME)
      .values(3, "Koontz")
      .onDuplicateKeyUpdate()
      .set(AUTHOR.LAST_NAME, "Koontz")
      .execute();

Cơ sở dữ liệu MySQL cũng hỗ trợ mệnh đề INSERT IGNORE INTO. Điều này được jOOQ hỗ trợ bằng cách sử dụng biến thể cú pháp SQL thuận tiện hơn của ON DUPLICATE KEY IGNORE

// Add a new author called "Koontz" with ID 3.
// If that ID is already present, ignore the INSERT statement
create.insertInto(AUTHOR, AUTHOR.ID, AUTHOR.LAST_NAME)
      .values(3, "Koontz")
      .onDuplicateKeyIgnore()
      .execute();

Nếu cơ sở dữ liệu cơ bản không có bất kỳ cách nào để "bỏ qua" các câu lệnh INSERT không thành công, (e. g. MySQL qua INSERT IGNORE), jOOQ có thể mô phỏng câu lệnh bằng cách sử dụng câu lệnh MERGE hoặc sử dụng INSERT . SELECT WHERE NOT EXISTS:

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, nhưng thay vào đó sẽ hoàn toàn bỏ qua 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 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 vốn có 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 là không 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ờ là

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 rằng chúng tôi muốn thay thế hồ 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)

6 về Green Eggs and Ham và chuyển nó về hồ sơ 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

Làm cách nào để tránh các bản ghi trùng lặp khi chèn vào SQL Server?

5 cách dễ dàng để xử lý các bản sao bằng SQL INSERT INTO SELECT .

Sử dụng INSERT INTO SELECT DISTINCT. Tùy chọn đầu tiên về cách xác định bản ghi SQL trong SQL là sử dụng DISTINCT trong CHỌN của bạn. .

Sử dụng WHERE NOT IN. Tiếp theo, chúng tôi điền vào bảng PastaDishes. .

Sử dụng WHERE NOT EXISTS. .

Sử dụng NẾU KHÔNG TỒN TẠI. .

Sử dụng COUNT(*) = 0

Làm cách nào để kiểm tra các bản ghi trùng lặp trong SQL trước khi chèn?

Tạo truy vấn chèn ban đầu của bạn, chẳng hạn như "INSERT INTO your_table ( column1 , column2 ) VALUES ('1','a');"

Thay đổi truy vấn bằng cách thêm quy tắc cho những việc cần làm nếu tìm thấy khóa trùng lặp, chẳng hạn như "CHÈN VÀO_bảng_của_bạn ( cột1 , cột2 ) GIÁ TRỊ ('1','a') TRÊN CẬP NHẬT KHÓA DUPLICATE cột1 ='1', cột2

Chèn vào bản cập nhật khóa trùng lặp là gì?

CHÈN. TRÊN CẬP NHẬT KHÓA DUPLICATE là một phần mở rộng MariaDB/MySQL cho câu lệnh INSERT, nếu nó tìm thấy một khóa chính hoặc khóa duy nhất trùng lặp, thay vào đó, nó sẽ thực hiện CẬP NHẬT . Giá trị/các hàng bị ảnh hưởng được báo cáo là 1 nếu một hàng được chèn vào và 2 nếu một hàng được cập nhật, trừ khi cờ CLIENT_FOUND_ROWS của API được đặt.

Bỏ qua chèn có nhanh hơn chèn không?

TL;DR. Chèn một số lượng lớn hàng mà không có xung đột nhanh hơn RẤT NHIỀU so với việc (không) chèn lại cùng một hàng , CẢ HAI bằng cách sử dụng cú pháp INSERT IGNORE.

programming mysql