MySQL cung cấp một số câu lệnh hữu ích khi cần INSERT
hàng sau khi xác định xem hàng đó thực tế là mới hay đã tồn tại
Dưới đây chúng ta sẽ xem xét ba phương pháp khác nhau và lần lượt giải thích ưu và nhược điểm của từng phương pháp để bạn nắm vững cách định cấu hình các câu lệnh của riêng mình khi cung cấp dữ liệu mới hoặc dữ liệu có khả năng tồn tại cho
mysql> INSERT INTO books
[id, title, author, year_published]
VALUES
[1, 'Green Eggs and Ham', 'Dr. Seuss', 1960];
ERROR 1062 [23000]: Duplicate entry '1' for key 'PRIMARY'
0Sử dụng INSERT IGNORE
Việc sử dụng INSERT IGNORE
khiến MySQL bỏ qua các lỗi thực thi một cách hiệu quả trong khi cố gắng thực hiện các câu lệnh INSERT
. Điều này có nghĩa là một câu lệnh INSERT IGNORE
chứa một giá trị trùng lặp trong chỉ mục
mysql> INSERT INTO books
[id, title, author, year_published]
VALUES
[1, 'Green Eggs and Ham', 'Dr. Seuss', 1960];
ERROR 1062 [23000]: Duplicate entry '1' for key 'PRIMARY'
5 hoặc trường mysql> INSERT INTO books
[id, title, author, year_published]
VALUES
[1, 'Green Eggs and Ham', 'Dr. Seuss', 1960];
ERROR 1062 [23000]: Duplicate entry '1' for key 'PRIMARY'
6 không tạo ra lỗi, mà thay vào đó sẽ bỏ qua hoàn toàn lệnh INSERT
cụ thể đó. Mục đích rõ ràng là để thực hiện một số lượng lớn các câu lệnh INSERT
cho sự kết hợp của dữ liệu đã tồn tại trong cơ sở dữ liệu cũng như dữ liệu mới được đưa vào hệ thốngVí dụ: bảng
mysql> INSERT INTO books
[id, title, author, year_published]
VALUES
[1, 'Green Eggs and Ham', 'Dr. Seuss', 1960];
ERROR 1062 [23000]: Duplicate entry '1' for key 'PRIMARY'
9 của chúng tôi có thể đã chứa một vài bản ghimysql> SELECT * FROM books LIMIT 3;
+----+-------------------------+---------------------+----------------+
| id | title | author | year_published |
+----+-------------------------+---------------------+----------------+
| 1 | In Search of Lost Time | Marcel Proust | 1913 |
| 2 | Ulysses | James Joyce | 1922 |
| 3 | Don Quixote | Miguel de Cervantes | 1605 |
+----+-------------------------+---------------------+----------------+
3 rows in set [0.00 sec]
Nếu chúng tôi có một lô lớn dữ liệu mới và hiện có cho INSERT
và một phần của dữ liệu đó chứa giá trị phù hợp cho trường
mysql> INSERT IGNORE INTO books
[id, title, author, year_published]
VALUES
[1, 'Green Eggs and Ham', 'Dr. Seuss', 1960];
Query OK, 0 rows affected [0.00 sec]
1 [là mysql> INSERT INTO books
[id, title, author, year_published]
VALUES
[1, 'Green Eggs and Ham', 'Dr. Seuss', 1960];
ERROR 1062 [23000]: Duplicate entry '1' for key 'PRIMARY'
5 mysql> INSERT IGNORE INTO books
[id, title, author, year_published]
VALUES
[1, 'Green Eggs and Ham', 'Dr. Seuss', 1960];
Query OK, 0 rows affected [0.00 sec]
3 trong bảng], việc sử dụng một INSERT
cơ bản sẽ tạo ra lỗi dự kiếnmysql> INSERT INTO books
[id, title, author, year_published]
VALUES
[1, 'Green Eggs and Ham', 'Dr. Seuss', 1960];
ERROR 1062 [23000]: Duplicate entry '1' for key 'PRIMARY'
Mặt khác, nếu chúng tôi sử dụng INSERT IGNORE
, nỗ lực sao chép sẽ bị bỏ qua và không có lỗi nào xảy ra
mysql> INSERT IGNORE INTO books
[id, title, author, year_published]
VALUES
[1, 'Green Eggs and Ham', 'Dr. Seuss', 1960];
Query OK, 0 rows affected [0.00 sec]
Sử dụng REPLACE
Trong trường hợp bạn thực sự muốn thay thế các hàng trong đó các lệnh INSERT
sẽ tạo ra lỗi do trùng lặp các giá trị
mysql> INSERT INTO books
[id, title, author, year_published]
VALUES
[1, 'Green Eggs and Ham', 'Dr. Seuss', 1960];
ERROR 1062 [23000]: Duplicate entry '1' for key 'PRIMARY'
5 hoặc mysql> INSERT INTO books
[id, title, author, year_published]
VALUES
[1, 'Green Eggs and Ham', 'Dr. Seuss', 1960];
ERROR 1062 [23000]: Duplicate entry '1' for key 'PRIMARY'
6 như đã nêu ở trên, một tùy chọn là chọn câu lệnh REPLACE
Khi đưa ra một câu lệnh REPLACE
, có hai kết quả có thể xảy ra cho mỗi lệnh được đưa ra
- Không tìm thấy hàng dữ liệu hiện có nào có giá trị phù hợp và do đó, một câu lệnh
INSERT
tiêu chuẩn được thực hiện - Một hàng dữ liệu phù hợp được tìm thấy, làm cho hàng hiện có đó bị xóa bằng câu lệnh
3 tiêu chuẩn, sau đó mộtmysql> REPLACE INTO books [id, title, author, year_published] VALUES [1, 'Green Eggs and Ham', 'Dr. Seuss', 1960]; Query OK, 2 rows affected [0.00 sec]
INSERT
bình thường được thực hiện sau đó
Ví dụ: chúng ta có thể sử dụng REPLACE
để hoán đổi bản ghi hiện có của chúng ta về
mysql> REPLACE INTO books
[id, title, author, year_published]
VALUES
[1, 'Green Eggs and Ham', 'Dr. Seuss', 1960];
Query OK, 2 rows affected [0.00 sec]
6 cuốn In Search of Lost Time của Marcel Proust với Green Eggs and Ham của Dr. Seussmysql> REPLACE INTO books
[id, title, author, year_published]
VALUES
[1, 'Green Eggs and Ham', 'Dr. Seuss', 1960];
Query OK, 2 rows affected [0.00 sec]
Lưu ý rằng mặc dù chúng tôi chỉ thay đổi một hàng, nhưng kết quả cho thấy có hai hàng bị ảnh hưởng bởi vì chúng tôi thực sự
mysql> REPLACE INTO books
[id, title, author, year_published]
VALUES
[1, 'Green Eggs and Ham', 'Dr. Seuss', 1960];
Query OK, 2 rows affected [0.00 sec]
7 hàng hiện tại sau đó mysql> REPLACE INTO books
[id, title, author, year_published]
VALUES
[1, 'Green Eggs and Ham', 'Dr. Seuss', 1960];
Query OK, 2 rows affected [0.00 sec]
8 hàng mới để thay thế hàng đóThông tin thêm về cách sử dụng REPLACE
có thể được tìm thấy trong tài liệu chính thức
Sử dụng INSERT .. ON DUPLICATE KEY UPDATE
Phương pháp thay thế [và thường được ưu tiên] cho các hàng
mysql> SET @id = 1,
@title = 'In Search of Lost Time',
@author = 'Marcel Proust',
@year_published = 1913;
INSERT INTO books
[id, title, author, year_published]
VALUES
[@id, @title, @author, @year_published]
ON DUPLICATE KEY UPDATE
title = @title,
author = @author,
year_published = @year_published;
1 có thể chứa các giá trị mysql> INSERT INTO books
[id, title, author, year_published]
VALUES
[1, 'Green Eggs and Ham', 'Dr. Seuss', 1960];
ERROR 1062 [23000]: Duplicate entry '1' for key 'PRIMARY'
5 hoặc mysql> INSERT INTO books
[id, title, author, year_published]
VALUES
[1, 'Green Eggs and Ham', 'Dr. Seuss', 1960];
ERROR 1062 [23000]: Duplicate entry '1' for key 'PRIMARY'
6 trùng lặp là sử dụng câu lệnh và mệnh đề INSERT .. ON DUPLICATE KEY UPDATE
Không giống như REPLACE
– một lệnh phá hoại cố hữu do các lệnh
mysql> REPLACE INTO books
[id, title, author, year_published]
VALUES
[1, 'Green Eggs and Ham', 'Dr. Seuss', 1960];
Query OK, 2 rows affected [0.00 sec]
3 mà nó thực hiện khi cần thiết – sử dụng INSERT .. ON DUPLICATE KEY UPDATE
không mang tính phá hủy, ở chỗ nó sẽ chỉ đưa ra các câu lệnh INSERT
hoặc mysql> SET @id = 1,
@title = 'In Search of Lost Time',
@author = 'Marcel Proust',
@year_published = 1913;
INSERT INTO books
[id, title, author, year_published]
VALUES
[@id, @title, @author, @year_published]
ON DUPLICATE KEY UPDATE
title = @title,
author = @author,
year_published = @year_published;
9, nhưng không bao giờ mysql> REPLACE INTO books
[id, title, author, year_published]
VALUES
[1, 'Green Eggs and Ham', 'Dr. Seuss', 1960];
Query OK, 2 rows affected [0.00 sec]
3Ví dụ: chúng tôi đã quyết định muốn thay thế bản ghi
mysql> REPLACE INTO books
[id, title, author, year_published]
VALUES
[1, 'Green Eggs and Ham', 'Dr. Seuss', 1960];
Query OK, 2 rows affected [0.00 sec]
6 về Green Eggs and Ham và hoàn nguyên nó về bản ghi gốc Tìm kiếm thời gian đã mất để thay thế. Do đó, chúng tôi có thể lấy câu lệnh INSERT
ban đầu của mình và thêm mệnh đề mysql> SELECT * FROM books LIMIT 1;
+----+------------------------+---------------+----------------+
| id | title | author | year_published |
+----+------------------------+---------------+----------------+
| 1 | In Search of Lost Time | Marcel Proust | 1913 |
+----+------------------------+---------------+----------------+
1 row in set [0.00 sec]
3 mớimysql> SET @id = 1,
@title = 'In Search of Lost Time',
@author = 'Marcel Proust',
@year_published = 1913;
INSERT INTO books
[id, title, author, year_published]
VALUES
[@id, @title, @author, @year_published]
ON DUPLICATE KEY UPDATE
title = @title,
author = @author,
year_published = @year_published;
Lưu ý rằng chúng tôi đang sử dụng cú pháp
mysql> SET @id = 1,
@title = 'In Search of Lost Time',
@author = 'Marcel Proust',
@year_published = 1913;
INSERT INTO books
[id, title, author, year_published]
VALUES
[@id, @title, @author, @year_published]
ON DUPLICATE KEY UPDATE
title = @title,
author = @author,
year_published = @year_published;
9 bình thường [nhưng loại trừ tên mysql> SELECT * FROM books LIMIT 1;
+----+------------------------+---------------+----------------+
| id | title | author | year_published |
+----+------------------------+---------------+----------------+
| 1 | In Search of Lost Time | Marcel Proust | 1913 |
+----+------------------------+---------------+----------------+
1 row in set [0.00 sec]
5 và từ khóa mysql> SELECT * FROM books LIMIT 1;
+----+------------------------+---------------+----------------+
| id | title | author | year_published |
+----+------------------------+---------------+----------------+
| 1 | In Search of Lost Time | Marcel Proust | 1913 |
+----+------------------------+---------------+----------------+
1 row in set [0.00 sec]
6 không cần thiết] và chỉ gán các giá trị mysql> SELECT * FROM books LIMIT 1;
+----+------------------------+---------------+----------------+
| id | title | author | year_published |
+----+------------------------+---------------+----------------+
| 1 | In Search of Lost Time | Marcel Proust | 1913 |
+----+------------------------+---------------+----------------+
1 row in set [0.00 sec]
7. Ngoài ra, mặc dù không cần thiết để phương thức mysql> SELECT * FROM books LIMIT 1;
+----+------------------------+---------------+----------------+
| id | title | author | year_published |
+----+------------------------+---------------+----------------+
| 1 | In Search of Lost Time | Marcel Proust | 1913 |
+----+------------------------+---------------+----------------+
1 row in set [0.00 sec]
3 hoạt động bình thường, chúng tôi cũng đã chọn sử dụng mysql> SELECT * FROM books LIMIT 1;
+----+------------------------+---------------+----------------+
| id | title | author | year_published |
+----+------------------------+---------------+----------------+
| 1 | In Search of Lost Time | Marcel Proust | 1913 |
+----+------------------------+---------------+----------------+
1 row in set [0.00 sec]
9 vì vậy chúng tôi không cần chỉ định các giá trị thực mà chúng tôi muốn INSERT
hoặc mysql> SET @id = 1,
@title = 'In Search of Lost Time',
@author = 'Marcel Proust',
@year_published = 1913;
INSERT INTO books
[id, title, author, year_published]
VALUES
[@id, @title, @author, @year_published]
ON DUPLICATE KEY UPDATE
title = @title,
author = @author,
year_published = @year_published;
9 nhiều lần