Chèn có giống như CẬP NHẬT trong MySQL không?

CHÈN. ON DUPLICATE KEY UPDATE 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ủa hàng/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ờ

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [PARTITION (partition_list)]
    SET col={expr | DEFAULT}, ...
    [ ON DUPLICATE KEY UPDATE
      col=expr
        [, col=expr] .. ]

Nếu có nhiều hơn một chỉ mục duy nhất được khớp, thì chỉ có chỉ mục đầu tiên được cập nhật. Không nên sử dụng câu lệnh này trên các bảng có nhiều hơn một chỉ mục duy nhất

Nếu bảng có khóa chính AUTO_INCREMENT và câu lệnh chèn hoặc cập nhật một hàng, thì hàm LAST_INSERT_ID() trả về giá trị AUTO_INCREMENT của nó

Hàm VALUES() chỉ có thể được sử dụng trong mệnh đề

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [PARTITION (partition_list)]
    SET col={expr | DEFAULT}, ...
    [ ON DUPLICATE KEY UPDATE
      col=expr
        [, col=expr] .. ]

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [PARTITION (partition_list)]
    SET col={expr | DEFAULT}, ...
    [ ON DUPLICATE KEY UPDATE
      col=expr
        [, col=expr] .. ]

Các tùy chọn BỎ QUA và TRÌ HOÃN bị bỏ qua khi bạn sử dụng

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [PARTITION (partition_list)]
    SET col={expr | DEFAULT}, ...
    [ ON DUPLICATE KEY UPDATE
      col=expr
        [, col=expr] .. ]

Xem Partition Pruning and Selection để biết chi tiết về mệnh đề PARTITION

Câu lệnh này kích hoạt kích hoạt INSERT và UPDATE. Xem Tổng quan về trình kích hoạt để biết chi tiết

Xem thêm một tuyên bố tương tự, REPLACE

ví dụ

CREATE TABLE ins_duplicate (id INT PRIMARY KEY, animal VARCHAR(30));
INSERT INTO ins_duplicate VALUES (1,'Aardvark'), (2,'Cheetah'), (3,'Zebra');

Nếu không có khóa hiện có, câu lệnh sẽ chạy như INSERT thông thường

INSERT INTO ins_duplicate VALUES (4,'Gorilla') 
  ON DUPLICATE KEY UPDATE animal='Gorilla';
Query OK, 1 row affected (0.07 sec)

SELECT * FROM ins_duplicate;
+----+----------+
| id | animal   |
+----+----------+
|  1 | Aardvark |
|  2 | Cheetah  |
|  3 | Zebra    |
|  4 | Gorilla  |
+----+----------+

INSERT thông thường có giá trị khóa chính là 1 sẽ không thành công do khóa hiện có

INSERT INTO ins_duplicate VALUES (1,'Antelope');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

Tuy nhiên, chúng ta có thể sử dụng INSERT ON DUPLICATE KEY UPDATE thay thế

Lưu ý rằng có hai hàng được báo cáo là bị ảnh hưởng, nhưng điều này chỉ đề cập đến CẬP NHẬT

SELECT * FROM ins_duplicate;
+----+----------+
| id | animal   |
+----+----------+
|  1 | Antelope |
|  2 | Cheetah  |
|  3 | Zebra    |
|  4 | Gorilla  |
+----+----------+

Thêm một cột duy nhất thứ hai

ALTER TABLE ins_duplicate ADD id2 INT;
UPDATE ins_duplicate SET id2=id+10;
ALTER TABLE ins_duplicate ADD UNIQUE KEY(id2);

Trong trường hợp hai hàng khớp với các khóa duy nhất khớp, chỉ hàng đầu tiên được cập nhật. Điều này có thể không an toàn và không được khuyến khích trừ khi bạn chắc chắn mình đang làm gì

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [PARTITION (partition_list)]
    SET col={expr | DEFAULT}, ...
    [ ON DUPLICATE KEY UPDATE
      col=expr
        [, col=expr] .. ]

Mặc dù hàng thứ ba có id là 3 có id2 là 13, cũng khớp nhưng nó không được cập nhật

Thay đổi id thành trường auto_increment. Nếu một hàng mới được thêm vào, auto_increment sẽ được chuyển tiếp. Nếu hàng được cập nhật, nó vẫn giữ nguyên

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, thì 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

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

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

Chèn có giống như cập nhật không?

Chèn có giống như cập nhật trong SQL không?

Chèn có nhanh hơn cập nhật trong MySQL không?

Sự khác biệt giữa câu lệnh chèn và câu lệnh cập nhật là gì?