UPSERT MySQL

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’, ‘[email protected]’);

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’, ‘[email protected]’);

0 đã tồn tại trong bảng, vì vậy thao tác

UPSERT INTO employees (id, name, email) VALUES (3, ‘Ash’, ‘[email protected]’);

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’, ‘[email protected]’);

2 và

UPSERT INTO employees (id, name, email) VALUES (3, ‘Ash’, ‘[email protected]’);

3

Ví dụ #2

UPSERT INTO employees (id, name, email) VALUES (3, ‘Ash’, ‘[email protected]’);

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’, ‘[email protected]’);

4 không tồn tại trong bảng, do đó, thao tác

UPSERT INTO employees (id, name, email) VALUES (3, ‘Ash’, ‘[email protected]’);

1 sẽ chèn một hàng mới vào bảng với các giá trị liên quan

Tuy 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’, ‘[email protected]’);

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’, ‘[email protected]’);

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’, ‘[email protected]’);

9. Hãy xem xét lệnh đó chi tiết hơn một chút

Như chính lệnh gợi ý,

UPSERT INTO employees (id, name, email) VALUES (3, ‘Ash’, ‘[email protected]’);

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ới

Vì 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’, ‘[email protected]’) 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’, ‘[email protected]’);

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ới

Tươ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’, ‘[email protected]’) 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’, ‘[email protected]’) ON DUPLICATE KEY UPDATE;

6 không tồn tại trong

Upsert trong PostgreSQL

PostgreSQL cũng không có lệnh

UPSERT INTO employees (id, name, email) VALUES (3, ‘Ash’, ‘[email protected]’);

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’, ‘[email protected]’) 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’, ‘[email protected]’) ON DUPLICATE KEY UPDATE;

9, nhưng nó cũng cho phép chúng ta kiểm soát nhiều hơn

Hã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’, ‘[email protected]’) ON DUPLICATE KEY UPDATE;

8 trong Postgres

INSERT 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’, ‘[email protected]’)
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’, ‘[email protected]’);

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ới

Nế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’, ‘[email protected]’);

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’, ‘[email protected]’) ON DUPLICATE KEY UPDATE;

8

Mặ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’, ‘[email protected]’); 1 so với. INSERT INTO employees (id, name, email) VALUES (2, ‘Dennis’, ‘[email protected]’) ON DUPLICATE KEY UPDATE; 8

Lệnh

UPSERT INTO employees (id, name, email) VALUES (3, ‘Ash’, ‘[email protected]’);

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’, ‘[email protected]’);

1 đơn giản hơn một chút so với

INSERT INTO employees (id, name, email) VALUES (2, ‘Dennis’, ‘[email protected]’) 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’, ‘[email protected]`);

…sẽ cho ra bảng sau

Bởi vì giá trị của

UPSERT INTO employees (id, name, email) VALUES (6, ‘Lambert’, ‘[email protected]`);

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ới

Tương tự, nếu chúng ta chạy câu lệnh sau…

UPSERT INTO employees (id, name, email) VALUES (1, ‘Ripley’, ‘[email protected]`);

… ta sẽ được bảng sau

Bởi vì

UPSERT INTO employees (id, name, email) VALUES (6, ‘Lambert’, ‘[email protected]`);

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ới

Tuy 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’, ‘[email protected]’) 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’, ‘[email protected]’) 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’, ‘[email protected]’);

1 và

INSERT INTO employees (id, name, email) VALUES (2, ‘Dennis’, ‘[email protected]’) 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’, ‘[email protected]’);

1 để biết thêm thông tin

Sự khác biệt giữa UPSERT và cập nhật trong MySQL là gì?

UPSERT có giống như MERGE không?

Lệnh UPSERT là gì?

UPSERT có tốt hơn INSERT không?