MySQL CHÈN BỎ QUA TRÊN CẬP NHẬT KHÓA DUPLICATE

Câu lệnh Insert on Duplicate Key Update là phần mở rộng của câu lệnh INSERT trong MySQL. Khi chúng ta chỉ định mệnh đề ON DUPLICATE KEY UPDATE trong câu lệnh SQL và một hàng sẽ gây ra giá trị lỗi trùng lặp trong cột chỉ mục UNIQUE hoặc PRIMARY KEY, thì việc cập nhật hàng hiện có sẽ xảy ra

Nói cách khác, khi chúng ta chèn các giá trị mới vào bảng và nó gây ra hàng trùng lặp trong cột UNIQUE OR PRIMARY KEY, chúng ta sẽ nhận được thông báo lỗi. Tuy nhiên, nếu chúng ta sử dụng mệnh đề ON DUPLICATE KEY UPDATE trong câu lệnh SQL, nó sẽ cập nhật hàng cũ bằng các giá trị hàng mới, cho dù nó có cột khóa chính hay cột duy nhất

Ví dụ: nếu cột col1 được định nghĩa là ĐỘC ĐÁO và chứa giá trị 10 trong bảng tab1, chúng ta sẽ nhận được hiệu ứng tương tự sau khi thực hiện hai câu lệnh bên dưới

Nó đảm bảo rằng nếu hàng được chèn khớp với nhiều hơn một chỉ mục duy nhất trong bảng, thì câu lệnh ON DUPLICATE KEY chỉ cập nhật chỉ mục duy nhất được khớp đầu tiên. Do đó, không nên sử dụng câu lệnh này trên các bảng chứa nhiều hơn một chỉ mục duy nhất

Nếu bảng chứa cột khóa chính AUTO_INCREMENT và câu lệnh ON DUPLICATE KEY cố gắng chèn hoặc cập nhật một hàng, thì hàm Last_Insert_ID() sẽ trả về giá trị AUTO_INCREMENT của nó

Sau đây là cú pháp của lệnh Insert on Duplicate Key Update trong MySQL

Trong cú pháp này, chúng ta có thể thấy rằng câu lệnh INSERT chỉ thêm mệnh đề ON DUPLICATE KEY UPDATE với phép gán cặp giá trị cột bất cứ khi nào nó tìm thấy các hàng trùng lặp. Hoạt động của mệnh đề ON DUPLICATE KEY UPDATE trước tiên sẽ cố gắng chèn các giá trị mới vào hàng và nếu xảy ra lỗi, nó sẽ cập nhật hàng hiện có với các giá trị hàng mới

Hàm VALUES() chỉ được sử dụng trong mệnh đề này và nó không có bất kỳ ý nghĩa nào trong bất kỳ ngữ cảnh nào khác. Nó trả về các giá trị cột từ phần INSERT và đặc biệt hữu ích cho các lần chèn nhiều hàng

MySQL đưa ra số lượng hàng bị ảnh hưởng với câu lệnh ON DUPLICATE KEY UPDATE dựa trên hành động đã cho

  • Nếu chúng ta chèn hàng mới vào một bảng, nó sẽ trả về một hàng bị ảnh hưởng
  • Nếu chúng tôi cập nhật hàng hiện có thành một bảng, nó sẽ trả về hai hàng bị ảnh hưởng
  • Nếu chúng tôi cập nhật hàng hiện có bằng cách sử dụng các giá trị hiện tại của nó vào bảng, nó sẽ trả về số lượng hàng bị ảnh hưởng 0

MySQL INSERT ON DUPLICATE KEY Ví dụ

Hãy để chúng tôi hiểu hoạt động của mệnh đề INSERT ON DUPLICATE KEY UPDATE trong MySQL với sự trợ giúp của một ví dụ

Đầu tiên, tạo một bảng có tên "Student" bằng cách sử dụng câu lệnh bên dưới

Tiếp theo, chèn dữ liệu vào bảng. Thực hiện câu lệnh sau

Thực thi câu lệnh SELECT để xác minh thao tác chèn

Chúng tôi sẽ nhận được đầu ra như bên dưới, nơi chúng tôi có ba hàng vào bảng

MySQL CHÈN BỎ QUA TRÊN CẬP NHẬT KHÓA DUPLICATE

Một lần nữa, thêm một hàng nữa vào bảng bằng truy vấn bên dưới

Câu lệnh trên sẽ thêm hàng thành công vì nó không có bất kỳ giá trị trùng lặp nào

MySQL CHÈN BỎ QUA TRÊN CẬP NHẬT KHÓA DUPLICATE

Cuối cùng, chúng tôi sẽ thêm một hàng có giá trị trùng lặp vào cột Stud_ID

MySQL đưa ra thông báo sau khi thực hiện thành công truy vấn trên

Ở bên dưới, chúng ta có thể thấy rằng hàng id=4 đã tồn tại. Vì vậy, truy vấn chỉ cập nhật Thành phố New York với California

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 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 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ế 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à hoàn nguyên hồ sơ đó 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

Sự khác biệt giữa bỏ qua INSERT và khi cập nhật khóa trùng lặp là gì?

BẬT CẬP NHẬT KHÓA TRỰC TIẾP có nghĩa là một bản cập nhật không cần thiết phải trả một số chi phí hoặc . CHÈN BỎ QUA ngụ ý lời mời cho các loại lỗi khác trượt vào không báo trước .

Làm cách nào để bỏ qua lỗi khóa trùng lặp trong MySQL INSERT?

Sử dụng lệnh INSERT IGNORE thay vì lệnh INSERT. Nếu một bản ghi không trùng lặp với một bản ghi hiện có, thì MySQL sẽ chèn nó như bình thường. Nếu bản ghi trùng lặp, thì từ khóa IGNORE sẽ yêu cầu MySQL loại bỏ nó một cách âm thầm mà không tạo ra lỗi .

Sự khác biệt giữa INSERT khi cập nhật và cập nhật khóa trùng lặp là gì?

Có sự khác biệt. Truy vấn INSERT phải kiểm tra các ràng buộc trên mỗi cột để xem chúng có bị vi phạm hay không khi thêm hàng đó . Nếu vậy, nó cần tìm hàng phù hợp để cập nhật và thực hiện cập nhật. Một truy vấn CẬP NHẬT chỉ phải tìm hàng để cập nhật và thực hiện cập nhật.

INSERT trên bản cập nhật khóa trùng lặp có phải là Atomic không?

Theo định nghĩa, tính nguyên tử yêu cầu mỗi giao dịch là tất cả hoặc không có gì. Vì vậy, vâng, đó là nguyên tử theo nghĩa là nếu dữ liệu mà bạn đang cố gắng chèn sẽ tạo ra một bản sao trong khóa chính hoặc trong chỉ mục duy nhất, .