Upserts rất hữu ích cho bất kỳ ai làm việc với cơ sở dữ liệu, nhưng thuật ngữ “upsert” thậm chí có thể không xuất hiện trong tài liệu DBMS của bạn
Vì vậy, một upsert là gì?
Upsert trong SQL là gì?
Thuật ngữ upsert là một portmanteau – sự kết hợp của các từ “update” và “insert”. ” Trong ngữ cảnh của cơ sở dữ liệu quan hệ, upert là một thao tác cơ sở dữ liệu sẽ cập nhật một hàng hiện có nếu một giá trị được chỉ định đã tồn tại trong bảng và chèn một hàng mới nếu giá trị được chỉ định chưa tồn tại
Ví dụ, hãy tưởng tượng chúng ta có một cơ sở dữ liệu với một bảng employees
và một cột id
làm khóa chính
Chúng ta có thể sử dụng upsert khi thay đổi thông tin nhân viên trong bảng này. Theo logic, nó sẽ trông như thế này
- Nếu ID nhân viên tồn tại trong bảng, hãy cập nhật hàng đó với thông tin mới
- Nếu ID nhân viên không tồn tại trong bảng, hãy thêm nó dưới dạng một hàng mới
Các RDBMS khác nhau xử lý cú pháp cho upserts theo cách khác nhau – chúng ta sẽ nói về điều đó sau một chút – nhưng sử dụng cú pháp CockroachDB UPSERT, đây là một số câu lệnh SQL mẫu và bảng employees
sẽ có kết quả nếu mỗi câu lệnh được chạy
Ví dụ 1
UPSERT INTO employees [id, name, email] VALUES [2, ‘Dennis’, ‘dennisp@weyland.corp’];
Kết quả
Trong ví dụ này, giá trị khóa chính của
UPSERT INTO employees [id, name, email] VALUES [3, ‘Ash’, ‘ash@hyperdyne.corp’];
0 đã tồn tại trong bảng, vì vậy thao tác UPSERT INTO employees [id, name, email] VALUES [3, ‘Ash’, ‘ash@hyperdyne.corp’];
1 đã cập nhật hàng đó với các giá trị mới cho UPSERT INTO employees [id, name, email] VALUES [3, ‘Ash’, ‘ash@hyperdyne.corp’];
2 và UPSERT INTO employees [id, name, email] VALUES [3, ‘Ash’, ‘ash@hyperdyne.corp’];
3Ví dụ #2
UPSERT INTO employees [id, name, email] VALUES [3, ‘Ash’, ‘ash@hyperdyne.corp’];
Kết quả
Trong ví dụ này, giá trị khóa chính của
UPSERT INTO employees [id, name, email] VALUES [3, ‘Ash’, ‘ash@hyperdyne.corp’];
4 không tồn tại trong bảng, do đó, thao tác UPSERT INTO employees [id, name, email] VALUES [3, ‘Ash’, ‘ash@hyperdyne.corp’];
1 sẽ chèn một hàng mới vào bảng với các giá trị liên quanTuy nhiên, đây chỉ là một ví dụ đơn giản. Trên thực tế, trong nhiều RDBMS,
UPSERT INTO employees [id, name, email] VALUES [3, ‘Ash’, ‘ash@hyperdyne.corp’];
1 thậm chí không tồn tại dưới dạng một lệnh. Đây là lý do tại sao nếu bạn tìm kiếm tài liệu về cơ sở dữ liệu bạn chọn, bạn có thể không tìm thấy mục nhập cho “upsert. ”Tuy nhiên, chúng ta có thể thực hiện upserts trong hầu hết các cơ sở dữ liệu phổ biến, vì vậy, hãy xem cách thực hiện chúng trong MySQL và PostgreSQL trước khi quay lại CockroachDB để thảo luận một số chi tiết
Chúng tôi sẽ tiếp tục sử dụng bảng employees
mẫu của chúng tôi để chứng minh cách chúng hoạt động
Nâng cấp trong MySQL
Lệnh
UPSERT INTO employees [id, name, email] VALUES [3, ‘Ash’, ‘ash@hyperdyne.corp’];
1 không tồn tại trong MySQL, nhưng vẫn có thể đạt được upserts. Phương pháp tốt nhất để triển khai upsert trong phiên bản hiện tại của MySQL là UPSERT INTO employees [id, name, email] VALUES [3, ‘Ash’, ‘ash@hyperdyne.corp’];
9. Hãy xem xét lệnh đó chi tiết hơn một chútNhư chính lệnh gợi ý,
UPSERT INTO employees [id, name, email] VALUES [3, ‘Ash’, ‘ash@hyperdyne.corp’];
9 sẽ chèn một hàng mới vào bảng trừ khi nó phát hiện một giá trị trùng lặp trong cột khóa chính, trong trường hợp đó, nó sẽ cập nhật hàng hiện có với thông tin mớiVì vậy, nếu chúng ta chạy lệnh sau trên bảng employees
ví dụ…
INSERT INTO employees [id, name, email] VALUES [2, ‘Dennis’, ‘dennisp@weyland.corp’] ON DUPLICATE KEY UPDATE;
…chúng tôi sẽ nhận được kết quả tương tự như chúng tôi đã thấy trong. MySQL phát hiện ra rằng giá trị
UPSERT INTO employees [id, name, email] VALUES [3, ‘Ash’, ‘ash@hyperdyne.corp’];
0 đã tồn tại trong cột khóa chính id
, vì vậy nó cập nhật hàng đó với thông tin mớiTương tự, nếu chúng ta chạy cùng lệnh đó với các giá trị
INSERT INTO employees [id, name, email] VALUES [2, ‘Dennis’, ‘dennisp@weyland.corp’] ON DUPLICATE KEY UPDATE;
4, nó sẽ chèn một hàng mới vào employees
với các giá trị đó, bởi vì giá trị INSERT INTO employees [id, name, email] VALUES [2, ‘Dennis’, ‘dennisp@weyland.corp’] ON DUPLICATE KEY UPDATE;
6 không tồn tại trongUpsert trong PostgreSQL
PostgreSQL cũng không có lệnh
UPSERT INTO employees [id, name, email] VALUES [3, ‘Ash’, ‘ash@hyperdyne.corp’];
1 chuyên dụng, nhưng có thể thực hiện upserts bằng cách sử dụng INSERT INTO employees [id, name, email] VALUES [2, ‘Dennis’, ‘dennisp@weyland.corp’] ON DUPLICATE KEY UPDATE;
8. Lệnh này có thể phức tạp hơn một chút so với INSERT INTO employees [id, name, email] VALUES [2, ‘Dennis’, ‘dennisp@weyland.corp’] ON DUPLICATE KEY UPDATE;
9, nhưng nó cũng cho phép chúng ta kiểm soát nhiều hơnHãy bắt đầu bằng cách xem xét cấu trúc cơ bản của một câu lệnh
INSERT INTO employees [id, name, email] VALUES [2, ‘Dennis’, ‘dennisp@weyland.corp’] ON DUPLICATE KEY UPDATE;
8 trong PostgresINSERT INTO table [col1, col2, col3]
VALUES [val1, val2, val3]
ON CONFLICT conflict_target conflict_action;
Như chúng ta có thể thấy trong lệnh trên, PostgreSQL cho phép chúng ta chỉ định hai điều
- xung đột_mục tiêu, tôi. e. nơi cần tìm để phát hiện xung đột
- xung đột_hành động, tôi. e. cách xử lý lệnh nếu phát hiện xung đột
Điều này cho phép chúng tôi nhắm mục tiêu nhiều hơn một chút về cách áp dụng uperts của chúng tôi
Trong phiên bản hiện tại của PostgreSQL
INSERT INTO table [col1, col2, col3]
VALUES [val1, val2, val3]
ON CONFLICT conflict_target conflict_action;
1, chúng tôi có thể đạt được upsert cơ bản bằng cách chỉ định mục tiêu xung đột [trong trường hợp này là id
, cột khóa chính] và điều chúng tôi muốn thực hiện nếu phát hiện xung đột [trong trường hợp này là cập nhật hàng hiện có INSERT INTO employees [id, name, email]
VALUES [2, ‘Dennis’, ‘dennisp@weyland.corp’]
ON CONFLICT [id] DO UPDATE;
Chạy lệnh này sẽ tạo ra kết quả giống như trong. PostgreSQL phát hiện xung đột – chúng tôi đang cố gắng chèn một hàng có giá trị id
là
UPSERT INTO employees [id, name, email] VALUES [3, ‘Ash’, ‘ash@hyperdyne.corp’];
0, nhưng hàng có giá trị id
đó đã tồn tại trong employees
– vì vậy nó chạy INSERT INTO table [col1, col2, col3]
VALUES [val1, val2, val3]
ON CONFLICT conflict_target conflict_action;
7 trên hàng đó bằng cách sử dụng các giá trị mớiNếu chúng ta chạy lệnh này với các giá trị không tạo ra xung đột [ví dụ:
INSERT INTO table [col1, col2, col3]
VALUES [val1, val2, val3]
ON CONFLICT conflict_target conflict_action;
8, nó sẽ chèn một hàng mới vào employees
với các giá trị đóUPSERT trong CockroachDB
CockroachDB có lệnh
UPSERT INTO employees [id, name, email] VALUES [3, ‘Ash’, ‘ash@hyperdyne.corp’];
1 và giống như PostgreSQL, bạn cũng có thể đạt được upserts bằng cách sử dụng INSERT INTO employees [id, name, email] VALUES [2, ‘Dennis’, ‘dennisp@weyland.corp’] ON DUPLICATE KEY UPDATE;
8Mặc dù hai lệnh này có thể đạt được kết quả tương tự nhưng chúng không hoàn toàn giống nhau. Hãy xem chúng khác nhau như thế nào và khi nào chúng ta có thể muốn sử dụng từng loại
UPSERT INTO employees [id, name, email] VALUES [3, ‘Ash’, ‘ash@hyperdyne.corp’];
1 so với. INSERT INTO employees [id, name, email] VALUES [2, ‘Dennis’, ‘dennisp@weyland.corp’] ON DUPLICATE KEY UPDATE;
8
UPSERT INTO employees [id, name, email] VALUES [3, ‘Ash’, ‘ash@hyperdyne.corp’];
INSERT INTO employees [id, name, email] VALUES [2, ‘Dennis’, ‘dennisp@weyland.corp’] ON DUPLICATE KEY UPDATE;
Lệnh
UPSERT INTO employees [id, name, email] VALUES [3, ‘Ash’, ‘ash@hyperdyne.corp’];
1 trong CockroachDB thực hiện upsert dựa trên tính duy nhất của cột hoặc các cột khóa chính và nó sẽ thực hiện INSERT INTO table [col1, col2, col3]
VALUES [val1, val2, val3]
ON CONFLICT conflict_target conflict_action;
7 hoặc INSERT INTO table [col1, col2, col3]
VALUES [val1, val2, val3]
ON CONFLICT conflict_target conflict_action;
1 tùy thuộc vào việc các giá trị được thêm vào có phải là duy nhất hay khôngĐiều này làm cho việc sử dụng
UPSERT INTO employees [id, name, email] VALUES [3, ‘Ash’, ‘ash@hyperdyne.corp’];
1 đơn giản hơn một chút so với INSERT INTO employees [id, name, email] VALUES [2, ‘Dennis’, ‘dennisp@weyland.corp’] ON DUPLICATE KEY UPDATE;
8, vì chúng tôi không cần chỉ định mục tiêu hoặc hành động xung đột. Ví dụ: chạy câu lệnh sau đối với bảng employees
ví dụ của chúng tôi…UPSERT INTO employees [id, name, email] VALUES [6, ‘Lambert’, ‘lambert@weyland.corp`];
…sẽ cho ra bảng sau
Bởi vì giá trị của
UPSERT INTO employees [id, name, email] VALUES [6, ‘Lambert’, ‘lambert@weyland.corp`];
0 chưa tồn tại trong employees
, CockroachDB chèn các giá trị vào bảng dưới dạng một hàng mớiTương tự, nếu chúng ta chạy câu lệnh sau…
UPSERT INTO employees [id, name, email] VALUES [1, ‘Ripley’, ‘ripley@weyland.corp`];
… ta sẽ được bảng sau
Bởi vì
UPSERT INTO employees [id, name, email] VALUES [6, ‘Lambert’, ‘lambert@weyland.corp`];
2 đã tồn tại trong id
, cột khóa chính, CockroachDB cập nhật hàng đó với thông tin mớiTuy nhiên, với CockroachDB, chúng tôi cũng có thể linh hoạt sử dụng
INSERT INTO employees [id, name, email] VALUES [2, ‘Dennis’, ‘dennisp@weyland.corp’] ON DUPLICATE KEY UPDATE;
8, điều này có thể hữu ích trong một số trường hợp. Ví dụ: chúng tôi có thể sử dụng INSERT INTO employees [id, name, email] VALUES [2, ‘Dennis’, ‘dennisp@weyland.corp’] ON DUPLICATE KEY UPDATE;
8 để xử lý upserts trong các tình huống mà chúng tôi muốn tránh xung đột không liên quan đến khóa chính. Ví dụ: chúng tôi có thể chỉ định cột khóa ngoại làm mục tiêu xung độtĐôi khi cũng có sự khác biệt về hiệu suất giữa
UPSERT INTO employees [id, name, email] VALUES [3, ‘Ash’, ‘ash@hyperdyne.corp’];
1 và INSERT INTO employees [id, name, email] VALUES [2, ‘Dennis’, ‘dennisp@weyland.corp’] ON DUPLICATE KEY UPDATE;
8, mặc dù những khác biệt này sẽ phụ thuộc vào chi tiết cụ thể về khối lượng công việc của bạn. Xem tài liệu CockroachDB UPSERT INTO employees [id, name, email] VALUES [3, ‘Ash’, ‘ash@hyperdyne.corp’];
1 để biết thêm thông tin