Làm cách nào để sử dụng khi cập nhật khóa trùng lặp trong MySQL?

MySQL INSERT ON DUPLICATE KEY UPDATE rất mạnh mẽ nhưng tính năng MySQL thường bị lãng quên. Nó được giới thiệu trong MySQL 4. 1 nhưng tôi vẫn liên tục thấy mọi người không biết về nó

Bản thân tôi rất thích tính năng này vì nó được thiết kế theo phong cách thực sự của MySQL – giải pháp rất hiệu quả cho các tác vụ thường xuyên trong khi vẫn giữ được vẻ đẹp và dễ sử dụng

Vậy MySQL INSERT … ON DUPLICATE KEY UPDATE tuyệt vời để làm gì? . Nếu bạn đang viết kế toán lưu lượng thì đó có thể là lưu lượng và số gói được truyền cho cổng hoặc địa chỉ IP nhất định. Đối với Ứng dụng web, có thể đếm số lượt truy cập trên mỗi trang hoặc địa chỉ IP, số lần từ khóa cụ thể được tìm kiếm, v.v.

Chức năng này cũng giúp dễ dàng thực hiện xử lý tệp nhật ký một lượt gia tăng và xây dựng các bảng tóm tắt

Đây là ví dụ

Vỏ bọc

1

2

3

4

5

6

tạo bảng ipstat(ip int unsigned not null primary key,

                          lần truy cập int unsigned not null,

                          last_hit dấu thời gian);

 

chèn vào ipstat giá trị(inet_aton('192.168.0.1'),1,now())

                       bật bản sao khóa cập nhật hits=hits+1;

Ví dụ này thực sự cho thấy một tính năng gọn gàng hơn của MySQL – các hàm inet_aton và inet_ntoa có thể chuyển đổi chuỗi địa chỉ IP thành số nguyên và ngược lại. Điều này cho phép tiết kiệm đáng kể độ dài trường bằng cách sử dụng 4 byte thay vì 15

Tính năng thứ ba mà ví dụ này tận dụng là trường DẤU THỜI GIAN. Theo mặc định, cột DẤU THỜI GIAN đầu tiên sẽ có giá trị tự động được cập nhật thành dấu thời gian hiện tại khi chèn và cập nhật. Chúng tôi thực sự có thể đã bỏ qua now() trong mệnh đề chèn nhưng điều này sẽ yêu cầu chỉ định danh sách các cột mà chúng tôi đã bỏ qua vì lợi ích của ví dụ

Vậy ví dụ này sẽ hoạt động như thế nào? . Nếu không có địa chỉ IP như vậy trong bảng, nó sẽ được thêm số lần truy cập = 1 nếu nó đã ở đó (lưu ý ip là KHÓA CHÍNH), nó sẽ chỉ được tăng lên và cập nhật dấu thời gian của lần truy cập cuối cùng

Lợi ích của việc sử dụng MySQL INSERT… ON DUPLICATE KEY UPDATE thay vì INSERT + UPDATE có thể khác nhau, tùy thuộc vào số lượng hàng mới và kích thước tập dữ liệu. Tăng tốc 30% nên là điển hình. Tăng hiệu suất không phải là lợi ích duy nhất – điều thậm chí còn quan trọng hơn là mã ứng dụng trở nên đơn giản hơn – ít bị lỗi hơn và dễ đọc

Trong bảng sinh viên của chúng tôi, chúng tôi có một trường tăng tự động duy nhất là ID. Ở đây chúng tôi không thể có hai bản ghi có cùng id. Vì vậy, nếu chúng tôi cố gắng sử dụng bất kỳ truy vấn chèn nào để thêm bản ghi (giả sử với id=2) với id đã tồn tại (Khóa trùng lặp) thì chúng tôi sẽ nhận được thông báo lỗi cho biết

Sao chép mục nhập '2' cho khóa 'id'

Bây giờ, chúng tôi có thể chỉ định trong truy vấn của mình rằng trong các trường hợp ngoại lệ như vậy (có id trùng lặp) thay vì chèn bản ghi mới, bản ghi hiện tại có thể được cập nhật với dữ liệu mới.
Bản ghi mới sẽ được chèn nếu không tìm thấy khóa trùng lặp hoặc nếu chúng tôi không vi phạm các ràng buộc duy nhất do thuộc tính bảng đặt.
Đây là một ví dụ sẽ tạo ra thông báo lỗi vì chúng tôi đang vi phạm các ràng buộc duy nhất.

INSERT INTO `student3` (`id`, `name`, `class`, `social`, `science`, `math`) VALUES (2, 'Max Ruin', 'Three', 86, 57, 86)

Sao chép mục nhập '2' cho khóa 'id'

Bây giờ, truy vấn truy vấn INSERT tương tự sẽ cập nhật bản ghi với dữ liệu mới thay vì thêm bất kỳ bản ghi mới nào.

INSERT INTO `student3` (`id`, `name`, `class`, `social`, `science`, `math`) VALUES (2, 'Max Ruin', 'Three', 86, 57, 86) on duplicate key update social=86,science=57,math=86

Chúng tôi sẽ nhận được thông báo cho biết đã chèn 2 hàng, nhưng thực tế chúng tôi chỉ cập nhật một bản ghi. Tại đây, MySQL sẽ trả về số hàng bị ảnh hưởng dựa trên hành động mà nó đã thực hiện

• Nếu một bản ghi mới được thêm vào ( đã chèn ) thì số hàng bị ảnh hưởng = 1
• Nếu một bản ghi được cập nhật với dữ liệu mới thì số hàng bị ảnh hưởng = 2
• Nếu một bản ghi mới được cập nhật với cùng một dữ liệu thì số hàng bị ảnh hưởng = 0

Cập nhật nhiều bản ghi

Bằng cách sử dụng cùng một truy vấn, chúng tôi có thể cập nhật nhiều bản ghi với dữ liệu mới.

INSERT INTO `student3` (`id`, `name`, `class`, `social`, `science`, `math`) VALUES
(2, 'Max Ruin', 'Three', 86, 57, 86),
(3, 'Arnold', 'Three', 56, 41, 76),
(4, 'Krish Star', 'Four', 62, 52, 72),
(5, 'John Mike', 'Four', 62, 82, 92),
(6, 'Alex John', 'Four', 58, 93, 83),
(7, 'My John Rob', 'Fifth', 79, 64, 74),
(8, 'Asruid', 'Five', 89, 84, 94),
(9, 'Tes Qry', 'Six', 77, 61, 71),
(10, 'Big John', 'Four', 56, 44, 56) 

ON DUPLICATE KEY UPDATE social=values(social),science=values(science),math=values(math);

Truy vấn trên sẽ cập nhật dữ liệu mới cho 9 bản ghi. Thông báo chúng tôi sẽ nhận được là 18 hàng được chèn

CẬP NHẬT với CHÈN

Bây giờ, chúng ta hãy thử cập nhật 9 bản ghi hiện có và thêm một bản ghi mới với id mới ( Đừng quên đặt lại dữ liệu bảng về giá trị cũ )

INSERT INTO `student3` (`id`, `name`, `class`, `social`, `science`, `math`) VALUES
(2, 'Max Ruin', 'Three', 86, 57, 86),
(3, 'Arnold', 'Three', 56, 41, 76),
(4, 'Krish Star', 'Four', 62, 52, 72),
(5, 'John Mike', 'Four', 62, 82, 92),
(6, 'Alex John', 'Four', 58, 93, 83),
(7, 'My John Rob', 'Fifth', 79, 64, 74),
(8, 'Asruid', 'Five', 89, 84, 94),
(9, 'Tes Qry', 'Six', 77, 61, 71),
(10, 'Big John', 'Four', 56, 44, 56),
(11,'New Name','Five',75,78,52) 

ON DUPLICATE KEY UPDATE social=values(social),science=values(science),math=values(math);

Bây giờ 9 bản ghi sẽ được cập nhật với dữ liệu mới và một bản ghi mới ( với id =11 ) sẽ . Thông báo chúng tôi sẽ nhận được là 19 hàng được chèn

Tải xuống kết xuất sql của bảng student3
← CẬP NHẬT Truy vấn Xuất dữ liệu sang bảng hiện có→
← Tham khảo SQL Cách xóa Bản ghi trong các bảng khác nhau →<

plus2net. com

13-10-2021

lời giải thích rất hay.
Cảm ơn.

13-10-2021

Bài viết thực sự tốt cho người mới bắt đầu

Làm cách nào để sử dụng khóa trùng lặp trong MySQL?

Làm cách nào để cập nhật các giá trị trùng lặp trong MySQL?

Cập nhật khóa trùng lặp có chậm không?

Làm cách nào để thêm mục nhập trùng lặp trong MySQL?