Practical MySQL & MariaDB

Giải thích MySQL UPSERT: Hướng dẫn INSERT … ON DUPLICATE KEY UPDATE kèm ví dụ

Cơ bản về SQL
目次

1. UPSERT là gì?

Tổng quan

“UPSERT” đề cập đến một tính năng của cơ sở dữ liệu kết hợp các thao tác “INSERT” và “UPDATE”. Nói cách khác, nếu dữ liệu chưa tồn tại, nó sẽ được chèn vào; nếu dữ liệu đã tồn tại, nó sẽ được cập nhật. Bằng cách sử dụng tính năng này, bạn có thể thực hiện các thao tác hiệu quả đồng thời duy trì tính nhất quán của dữ liệu.

Trong MySQL, chức năng này được triển khai bằng cú pháp INSERT ... ON DUPLICATE KEY UPDATE. Tính năng này cho phép bạn tránh lỗi khóa trùng lặp và cập nhật các bản ghi hiện có ngay cả khi có khóa trùng lặp.

Trường hợp sử dụng

  • Hệ thống quản lý khách hàng: Thêm dữ liệu khách hàng mới nếu chưa tồn tại, và cập nhật thông tin khách hàng hiện có khi có thay đổi.
  • Quản lý tồn kho sản phẩm: Thêm sản phẩm mới đồng thời cập nhật số lượng tồn kho của các 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 hoá các truy vấn SQL
  • Duy trì tính toàn vẹn dữ liệu

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

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

Cú pháp cơ bản

INSERT INTO table_name (column1, column2)
VALUES (value1, value2)
ON DUPLICATE KEY UPDATE
column1 = value1, column2 = value2;

Giải thích:

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

Ví dụ:

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

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

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

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

Khi sử dụng UPSERT, có những trường hợp bạn chỉ muốn cập nhật các cột cụ thể. Trong những tình huống này, bạn có thể chỉ định những cột cần thiết trong mệnh đề ON DUPLICATE KEY UPDATE.

INSERT INTO products (product_id, name, price)
VALUES (100, 'Laptop', 50000)
ON DUPLICATE KEY UPDATE
price = VALUES(price);

Trong ví dụ này, nếu một sản phẩm có product_id = 100 đã tồn tại, chỉ cột price sẽ được cập nhật, trong khi các cột khác (như name) vẫn giữ nguyên.

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

Các cơ sở dữ liệu không phải MySQL cũng cung cấp chức năng tương tự. Ví dụ, PostgreSQL và SQLite sử dụng câu lệnh INSERT ... ON CONFLICT hoặc MERGE để đạt được hành vi giống UPSERT.

Ví dụ PostgreSQL

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

Trong PostgreSQL và SQLite, mệnh đề ON CONFLICT được dùng để điều khiển hành vi khi xảy ra lỗi khóa trùng lặp. Ngược lại, MySQL sử dụng mệnh đề ON DUPLICATE KEY UPDATE.

Đặc điểm riêng của MySQL

  • MySQL sử dụng INSERT ... ON DUPLICATE KEY UPDATE, và vì cú pháp này khác với các hệ quản trị khác, cần chú ý đặc biệt khi di chuyển giữa các hệ thống.

5. Kỹ thuật UPSERT nâng cao

UPSERT hàng loạt (Xử lý nhiều bản ghi cùng lúc)

UPSERT không chỉ có thể thực hiện cho một bản ghi duy nhất mà còn có thể áp dụng cho 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 của các thao tác cơ sở dữ liệu.

INSERT INTO products (product_id, name, price)
VALUES
(100, 'Laptop', 50000),
(101, 'Smartphone', 30000)
ON DUPLICATE KEY UPDATE
price = VALUES(price);

Trong ví dụ này, nhiều bản ghi sản phẩm được chèn vào một lần. Nếu có khóa trùng lặp, chỉ trường price của các bản ghi tương ứng sẽ được cập nhật.

Sử dụng Stored Procedure cho UPSERT

Để tối ưu hoá quá trình UPSERT, bạn cũng có thể sử dụng các stored procedure. Điều này cho phép bạn tạo ra logic có thể tái sử dụng ngay trong cơ sở dữ liệu, nâng cao cả độ dễ đọc và khả năng bảo trì của mã nguồn.

6. Những Sai Lầm Thường Gặp và Các Lưu Ý Quan Trọng

Giao Dịch và Deadlock

Khi sử dụng UPSERT—đặc biệt là với khối lượng dữ liệu lớn—có thể xảy ra deadlock. Nếu mức cách ly giao dịch của MySQL được đặt thành REPEATABLE READ, gap lock sẽ có khả năng xuất hiện cao hơn.

Tránh Gap Lock

  • Bạn có thể giảm khả năng xảy ra deadlock bằng cách thay đổi mức cách ly giao dịch thành READ COMMITTED.
  • Nếu cần, hãy cân nhắc chia một thao tác UPSERT lớn thành các batch nhỏ hơn và thực thi nhiều truy vấn thay vì một câu lệnh duy nhất.

7. Kết Luận

Tính năng UPSERT của MySQL rất hữu ích cho việc đơn giản hoá việc chèn và cập nhật dữ liệu đồng thời tránh lỗi trùng khóa. Tuy nhiên, việc triển khai UPSERT đòi hỏi phải cân nhắc kỹ lưỡng về các deadlock tiềm ẩn và cài đặt giao dịch. Khi được sử dụng đúng cách, nó giúp các thao tác trên cơ sở dữ liệu trở nên đơn giản và hiệu quả hơn.