UPSERT trong MySQL là gì? Cách sử dụng INSERT ON DUPLICATE KEY UPDATE│MySQL & MariaDBの世界

1. UPSERT là gì?

Tổng quan

“UPSERT” trong cơ sở dữ liệu là chức năng kết hợp giữa “INSERT” và “UPDATE”. Nghĩa là, nếu dữ liệu mới chưa tồn tại thì sẽ được chèn vào, còn nếu dữ liệu đã tồn tại thì sẽ được cập nhật. Chức năng này giúp duy trì tính nhất quán của dữ liệu và tối ưu hóa thao tác.

Trong MySQL, cú pháp INSERT ... ON DUPLICATE KEY UPDATE được sử dụng cho mục đích này. Nhờ đó, ngay cả khi xảy ra tình huống trùng lặp dữ liệu, bạn có thể tránh lỗi khóa trùng và cập nhật dữ liệu đã tồn tại.

Tình huống sử dụng

Hệ thống quản lý khách hàng: Thêm khách hàng mới nếu chưa có, và cập nhật thông tin nếu khách hàng đã tồn tại.
Quản lý hàng tồn kho: Thêm sản phẩm mới và cập nhật số lượng tồn kho của sản phẩm đã có.

Ưu điểm của UPSERT trong MySQL

Tránh lỗi khóa trùng lặp
Đơn giản hóa câu lệnh SQL
Giữ tính toàn vẹn của dữ liệu

2. Cách sử dụng cơ bản của UPSERT trong MySQL

Trong MySQL, thao tác UPSERT được thực hiện bằng cú pháp INSERT ... ON DUPLICATE KEY UPDATE. Khi sử dụng cú pháp này, nếu phát sinh khóa trùng, thay vì chèn dữ liệu mới, một phần hoặc toàn bộ dữ liệu hiện có sẽ được cập nhật.

Cú pháp cơ bản

INSERT INTO tên_bảng (cột1, cột2)
VALUES (giá_trị1, giá_trị2)
ON DUPLICATE KEY UPDATE
cột1 = giá_trị1, cột2 = giá_trị2;

Giải thích:

INSERT INTO để chèn dữ liệu.
Nếu dữ liệu đã tồn tại trong bảng, phần ON DUPLICATE KEY UPDATE sẽ được thực thi và cập nhật dữ liệu hiện có.

Ví dụ:

INSERT INTO users (user_id, name)
VALUES (1, 'Tanaka Taro')
ON DUPLICATE KEY UPDATE
name = 'Tanaka Taro';

Trong ví dụ trên, nếu người dùng có user_id bằng 1 đã tồn tại thì trường name sẽ được cập nhật thành ‘Tanaka Taro’. Nếu chưa tồn tại thì dữ liệu mới sẽ được thêm vào.

3. Cú pháp chi tiết và ví dụ về UPSERT

Cập nhật nhiều cột

Khi sử dụng UPSERT, bạn có thể chỉ định cập nhật một số cột cụ thể. Trong trường hợp đó, chỉ định cột cần cập nhật trong phần ON DUPLICATE KEY UPDATE.

INSERT INTO products (product_id, name, price)
VALUES (100, 'Máy tính xách tay', 50000)
ON DUPLICATE KEY UPDATE
price = VALUES(price);

Trong ví dụ này, nếu product_id = 100 đã tồn tại, chỉ có price được cập nhật, các cột khác (như name) không thay đổi.

4. Khác biệt với các cơ sở dữ liệu khác

Ngoài MySQL, các cơ sở dữ liệu khác cũng có phương pháp thực hiện tương tự. Ví dụ, PostgreSQL và SQLite sử dụng cú pháp INSERT ... ON CONFLICT hoặc MERGE cho UPSERT.

Ví dụ trong PostgreSQL

INSERT INTO users (user_id, name)
VALUES (1, 'Tanaka Taro')
ON CONFLICT (user_id) DO UPDATE SET
name = 'Tanaka Taro';

Trong PostgreSQL hoặc SQLite, cú pháp ON CONFLICT được dùng để xử lý khi xảy ra lỗi trùng khóa. Trong khi đó, MySQL sử dụng ON DUPLICATE KEY UPDATE.

Đặc thù của MySQL

MySQL sử dụng INSERT ... ON DUPLICATE KEY UPDATE, khác với cú pháp của các cơ sở dữ liệu khác, do đó cần lưu ý khi di chuyển hệ thống.

5. Cách sử dụng nâng cao của UPSERT

Bulk UPSERT (xử lý nhiều bản ghi cùng lúc)

UPSERT không chỉ áp dụng cho một bản ghi mà còn có thể xử lý nhiều bản ghi cùng lúc. Điều này giúp cải thiện đáng kể hiệu suất thao tác cơ sở dữ liệu.

INSERT INTO products (product_id, name, price)
VALUES
(100, 'Máy tính xách tay', 50000),
(101, 'Điện thoại thông minh', 30000)
ON DUPLICATE KEY UPDATE
price = VALUES(price);

Trong ví dụ này, nhiều sản phẩm được chèn một lần và nếu có khóa trùng, chỉ giá trị price sẽ được cập nhật.

UPSERT với Stored Procedure

Để tối ưu hóa xử lý UPSERT, có thể sử dụng Stored Procedure. Cách này giúp tạo mã tái sử dụng trong cơ sở dữ liệu, tăng khả năng đọc và dễ bảo trì.

6. Cạm bẫy và lưu ý khi dùng UPSERT

Giao dịch và Deadlock

Khi sử dụng UPSERT, đặc biệt với lượng dữ liệu lớn, có thể xảy ra deadlock. Nếu mức cô lập giao dịch của MySQL được đặt ở REPEATABLE READ, dễ phát sinh Gap Lock.

Cách tránh Gap Lock

Chuyển mức cô lập giao dịch sang READ COMMITTED để giảm nguy cơ deadlock.
Nếu cần, hãy chia nhỏ thao tác UPSERT thành nhiều truy vấn để thực thi từng bước.

7. Kết luận

Tính năng UPSERT trong MySQL rất hữu ích để chèn và cập nhật dữ liệu một cách hiệu quả, đồng thời tránh lỗi khóa trùng. Tuy nhiên, khi triển khai cần lưu ý nguy cơ deadlock và cấu hình giao dịch. Nếu sử dụng đúng cách, UPSERT giúp đơn giản hóa và tăng hiệu quả thao tác với cơ sở dữ liệu.

UPSERT trong MySQL là gì? Cách sử dụng INSERT ON DUPLICATE KEY UPDATE