Trong MySQL, khi làm việc với các bảng, quan trọng để hiểu sự khác nhau giữa các câu lệnh UPSERT và INSERT. Câu lệnh INSERT đơn giản chỉ thêm các bản ghi hoàn toàn mới vào một bảng, trong khi UPSERT kết hợp các chức năng của cả INSERT và UPDATE. UPSERT được sử dụng khi bạn muốn chèn các bản ghi mới vào một bảng nhưng cũng cần xử lý những xung đột tiềm tàng, thực hiện cập nhật các bản ghi hiện có nếu xung đột xảy ra. Điều này đặc biệt hữu ích trong các tình huống nơi duy trì tính toàn vẹn dữ liệu là quan trọng.
Sự khác nhau giữa UPSERT và INSERT
| Tiêu chí | INSERT | UPSERT |
|---|---|---|
| Mục đích chính | Thêm các bản ghi hoàn toàn mới vào một bảng. | Chèn các bản ghi mới hoặc cập nhật các bản ghi hiện có để duy trì tính toàn vẹn dữ liệu. |
| Xử lý trùng lặp | Không xử lý trùng lặp ngầm định; dẫn đến lỗi nếu xung đột xảy ra. | Được thiết kế đặc biệt để xử lý các mục nhập trùng lặp bằng cách cập nhật các bản ghi hiện có. |
| Cú pháp | Sử dụng cú pháp đơn giản, chỉ định các giá trị cho mỗi cột trong bản ghi mới. | Sử dụng cú pháp INSERT ... ON DUPLICATE KEY UPDATE để cập nhật có điều kiện dựa trên xung đột khóa. |
UPSERT so với INSERT
Dưới đây là một sơ đồ đơn giản để cho thấy sự khác biệt giữa câu lệnh UPSERT và câu lệnh INSERT.
Tiếp theo, chúng tôi đã đặc biệt đưa ra một hình ảnh khác cho thấy cách thức hoạt động của câu lệnh UPSERT trong MySQL.
Minh họa sự khác nhau giữa UPSERT và INSERT với các ví dụ
Hãy xem hai ví dụ để minh họa cách hoạt động khác nhau của hai hoạt động cơ sở dữ liệu này.
Ví dụ UPSERT
#1 Giả sử bạn có một bảng có tên products với ràng buộc duy nhất trên cột product_code. Bạn muốn chèn một sản phẩm mới hoặc cập nhật giá nếu sản phẩm đã tồn tại. UPSERT là lựa chọn lý tưởng trong tình huống này:
Mã Mysql:
INSERT INTO products (product_code, product_name, price) VALUES ('P001', 'Widget A', 73.13) ON DUPLICATE KEY UPDATE price = 73.13; Câu lệnh UPSERT này đảm bảo nếu một sản phẩm với mã 'P001' đã tồn tại, giá của nó sẽ được cập nhật; nếu không, một bản ghi mới sẽ được chèn.
#2 Hãy tưởng tượng một bảng users với ràng buộc duy nhất trên cả username và email. Nếu bạn đang triển khai xác thực người dùng và muốn chèn một người dùng mới hoặc cập nhật thông tin của họ (ví dụ: thay đổi mật khẩu), UPSERT là lựa chọn tốt nhất:
Mã Mysql:
INSERT INTO users (username, email, password) VALUES ('hello_world', 'hello@world.com', 'new_secure_pass') ON DUPLICATE KEY UPDATE password = 'new_secure_pass'; Lệnh UPSERT này cho phép bạn xử lý các tình huống mà người dùng có thể cố gắng thay đổi mật khẩu, đảm bảo rằng thông tin sẽ được cập nhật nếu tên người dùng hoặc email đã tồn tại.
Ví dụ về INSERT
#1 Xem xét một bảng employees trong đó mỗi nhân viên có một ID duy nhất. Nếu bạn đang tuyển dụng một nhân viên mới và muốn thêm họ vào bảng, INSERT là lựa chọn thích hợp:
Mã MySQL:
INSERT INTO employees (employee_id, name, position) VALUES (1001, 'Hello World', 'Software Engineer'); Ở đây, bạn đang thêm một bản ghi mới cho một nhân viên mới mà không quan tâm đến các mục nhập đã tồn tại trước đó, làm cho INSERT trở thành hoạt động phù hợp.
#2 Xem xét một bảng transactions ghi lại các giao dịch tài chính với một ID giao dịch duy nhất. Khi ghi lại một giao dịch mới, bạn không cần phải lo lắng về xung đột, vì mỗi ID giao dịch là duy nhất. Do đó, một câu lệnh INSERT là đủ:
Mã MySQL:
INSERT INTO transactions (trans_id, amount, description) VALUES ('TXN123', 37.87, 'Purchase of goods'); Trong trường hợp này, bạn chỉ đơn giản là thêm một bản ghi mới vào bảng giao dịch mà không cần tính năng UPSERT.
Tóm lại, trong khi INSERT đơn giản để thêm bản ghi mới khi trùng lặp không phải là một vấn đề, UPSERT sẽ tỏa sáng trong các tình huống bạn muốn chèn dữ liệu mới trong khi xử lý tương tác xung đột và duy trì tính toàn vẹn dữ liệu. Lựa chọn hoạt động phù hợp phụ thuộc vào yêu cầu cụ thể của cơ sở dữ liệu và tính chất của dữ liệu bạn đang quản lý.
Khi nào sử dụng UPSERT
Bạn sẽ sử dụng lệnh upsert trong các tình huống sau đây.
- Nếu bạn cần đảm bảo rằng dữ liệu luôn được cập nhật. Ví dụ, bạn có thể sử dụng UPSERT để cập nhật địa chỉ email của người dùng trong cơ sở dữ liệu khi họ đăng ký một tài khoản mới, hoặc để cập nhật số lượng hàng tồn kho của một sản phẩm khi có một giao dịch mua hàng.
- Khi bạn cần ngăn chặn việc chèn dữ liệu trùng lặp vào cơ sở dữ liệu. Ví dụ, sử dụng UPSERT để chèn một sản phẩm mới vào bảng danh mục sản phẩm, nhưng chỉ khi nó chưa tồn tại.
- Sử dụng nó khi bạn phải chạy một hoạt động duy nhất trên một hàng, ngay cả khi hàng không tồn tại. Ví dụ, bạn có thể sử dụng UPSERT để tăng số lượt xem của một video, bất kể video đã được xem trước đó hay chưa.
Khi nào sử dụng INSERT
Dưới đây là một số ví dụ cơ bản khi bạn nên sử dụng hoạt động chèn.
- Trong trường hợp, bạn chắc chắn rằng hàng bạn đang chèn không tồn tại trong cơ sở dữ liệu. Ví dụ, bạn có thể sử dụng INSERT để thêm một người dùng mới vào cơ sở dữ liệu khi anh ta đăng ký một tài khoản mới.
- Khi bạn cần chèn nhiều hàng vào cơ sở dữ liệu cùng một lúc. Ví dụ, bạn có thể sử dụng INSERT để chèn một lô dữ liệu sản phẩm mới vào bảng danh mục sản phẩm.
- Sử dụng INSERT để thêm một hàng vào bảng mà không áp dụng bất kỳ cập nhật nào. Ví dụ, sử dụng INSERT để chèn một mục nhập nhật ký mới vào cơ sở dữ liệu.
Kết luận
UPSERT và INSERT đều là các hoạt động cơ sở dữ liệu hữu ích, nhưng chúng có mục đích khác nhau. UPSERT được sử dụng để chèn hoặc cập nhật hàng trong một bảng trong một câu lệnh duy nhất, trong khi INSERT được sử dụng để chèn hàng mới vào một bảng.
Lựa chọn hoạt động phụ thuộc vào yêu cầu cụ thể của ứng dụng của bạn.