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] .. ]3 của API được đặt
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] .. ]4 và không có nghĩa trong bất kỳ ngữ cảnh nào khác. Nó trả về các giá trị cột từ phần
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] .. ]5 của câu lệnh. Chức năng này đặc biệt hữu ích cho chèn nhiều hàng
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] .. ]4
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ế
________số 8_______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] .. ]0
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