Upsert là gì? Khi nào bạn nên sử dụng

Bizfly Cloud

1283

08-09-2024

Upsert là một thao tác cơ sở dữ liệu thông minh, cho phép cập nhật hàng tồn tại hoặc chèn hàng mới tùy thuộc vào dữ liệu đã có hay chưa. Bài viết này Bizfly Cloud sẽ giới thiệu chi tiết về Upsert và cách thực hiện nó trong các hệ quản trị cơ sở dữ liệu phổ biến.

Vậy upsert là gì?

Thuật ngữ upsert là một từ ghép — sự kết hợp của hai từ "update" (cập nhật) và "insert" (chèn). Trong bối cảnh của cơ sở dữ liệu quan hệ, upsert là một thao tác cơ sở dữ liệu sẽ cập nhật một hàng đang tồn tại nếu một giá trị được chỉ định đã có trong bảng, và chèn một hàng mới nếu giá trị đó 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à 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. Cụ thể, quy trình đó sẽ như sau:

• Nếu ID nhân viên tồn tại trong bảng, 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, thêm nó như một hàng mới.

Các RDBMS (hệ quản trị cơ sở dữ liệu quan hệ) khác nhau xử lý cú pháp cho upserts một cách khác nhau — chúng ta sẽ nói về điều này sau một chút — nhưng sử dụng cú pháp UPSERT của CockroachDB, dưới đây là một vài ví dụ về câu lệnh SQL, và bảng employees sẽ như thế nào nếu mỗi câu lệnh được thực hiện:

Ví dụ #1

Kết quả:

Trong ví dụ này, giá trị khóa chính là 3 chưa tồn tại trong bảng, nên thao tác UPSERT sẽ chèn một hàng mới vào bảng với các giá trị tương ứng.

Tuy nhiên, đây chỉ là một ví dụ đơn giản. Trên thực tế, trong nhiều RDBMS, lệnh UPSERT thậm chí không tồn tại! Đó là lý do tại sao nếu bạn tìm kiếm tài liệu cho cơ sở dữ liệu bạn chọn, bạn có thể không tìm thấy mục về "upsert."

Chúng ta vẫn 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 cùng xem cách thực hiện chúng trong MySQL và PostgreSQL trước khi quay trở lại CockroachDB để thảo luận một số chi tiết.

Chúng ta sẽ tiếp tục sử dụng bảng employees mẫu của chúng ta để cho thấy cách chúng hoạt động.

Upsert trong MySQL

Trong MySQL, lệnh UPSERT không tồn tại, nhưng upserts vẫn có thể được thực hiện. Phương pháp tốt nhất để thực hiện một upsert trong phiên bản hiện tại của MySQL là INSERT ... ON DUPLICATE KEY UPDATE. Hãy xem xét lệnh này một chút chi tiết hơn.

Như chính câu lệnh đề xuất, INSERT ... ON DUPLICATE KEY UPDATE 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 đang tồn tại với thông tin mới.

Vì vậy, nếu chúng ta thực hiện lệnh sau đây trên bảng employees mẫu...

...chúng ta sẽ nhận được kết quả tương tự như trong Ví dụ #1 ở trên. MySQL phát hiện ra rằng giá trị 2 đã 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 thực hiện cùng một lệnh với giá trị (4, 'Dallas', 'dallas@weyland.corp'), nó sẽ chèn một hàng mới vào employees với các giá trị đó, bởi vì giá trị 4 không tồn tại trong bảng mẫu.

Upsert trong PostgreSQL

PostgreSQL cũng không có lệnh UPSERT riêng biệt, nhưng upserts có thể được thực hiện bằng cách sử dụng INSERT ON CONFLICT. Lệnh này có thể phức tạp hơn một chút so với INSERT ... ON DUPLICATE KEY, 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 cấu trúc cơ bản của một câu lệnh INSERT ON CONFLICT trong Postgres:

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:

• conflict_target, tức là nơi nó sẽ tìm để phát hiện một xung đột.

• conflict_action, tức là cách lệnh sẽ được xử lý nếu phát hiện xung đột.

Điều này cho phép chúng ta áp dụng upserts một cách cụ thể hơn.

Trong phiên bản hiện tại của PostgreSQL, chúng ta có thể thực hiện một 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à những gì chúng ta muốn làm nếu phát hiện xung đột (trong trường hợp này, cập nhật hàng đang tồn tại):

Thực hiện lệnh này sẽ tạo ra kết quả giống như trong Ví dụ #1 ở đầu bài viết. PostgreSQL phát hiện ra xung đột - chúng ta đang cố gắng chèn một hàng với giá trị id là 2, nhưng một hàng với id đó đã tồn tại trong employees - vì vậy nó thực hiện UPDATE hàng đó với các giá trị mới.

Nếu chúng ta thực hiện lệnh này với các giá trị không tạo ra xung đột (ví dụ, (5, 'Kane', 'kane@weyland.corp'), 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, và giống PostgreSQL, upserts cũng có thể được thực hiện bằng cách sử dụng INSERT ON CONFLICT.

Mặc dù hai lệnh này có thể đạt được kết quả tương tự, chúng không hoàn toàn giống nhau. Hãy xem xét sự khác biệt giữa chúng và khi nào chúng ta có thể muốn sử dụng từng lệnh.

UPSERT so với INSERT ON CONFLICT

Lệnh UPSERT 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 UPDATE hoặc INSERT 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 đơn giản hơn INSERT ON CONFLICT, vì chúng ta không cần phải chỉ định mục tiêu xung đột hoặc hành động. Ví dụ, thực hiện lệnh sau đây đối với bảng employees mẫu của chúng ta...

...sẽ tạo ra bảng sau:

Bởi vì giá trị 6 không 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 thực hiện lệnh sau đây...

...chúng ta sẽ nhận được bảng sau:

Bởi vì giá trị 1 đã 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, chúng ta cũng có sự linh hoạt của việc sử dụng INSERT ON CONFLICT, có thể hữu ích trong một số trường hợp. Ví dụ, chúng ta có thể sử dụng INSERT ON CONFLICT để xử lý upserts trong các tình huống mà chúng ta muốn tránh xung đột không liên quan đến khóa chính. Chẳng hạn, chúng ta có thể chỉ định một cột khóa ngoại là mục tiêu xung đột.

Cũng có đôi khi có sự khác biệt về hiệu suất giữa UPSERT và INSERT ON CONFLICT, mặc dù những điều này sẽ phụ thuộc vào chi tiết cụ thể của tải công việc của bạn.