在 MySQL 表中使用重复键验证插入值
-
使用
ON DUPLICATE KEY更新方法在具有重复键验证的 MySQL 表中插入值 -
使用
REPLACE方法在具有重复键验证的 MySQL 表中插入值 -
使用
IGNORE方法在具有重复键验证的 MySQL 表中插入值
传统 SQL 的 INSERT 语句不会针对现有数据库表执行其参数/值的输入验证。在插入过程中发现重复键时，有时会导致错误。
这在 MySQL 中通过 INSERT 的扩展与 ON DUPLICATE KEY UPDATE、REPLACE 和 IGNORE 等语句进行处理。
为了说明这些方法，让我们创建一个名为 programming_languages 的示例数据库。
-- Here goes the definition of the database
CREATE DATABASE programming_languages;
USE programming_languages;
-- Creating a table
CREATE TABLE Details(
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(25) UNIQUE, -- Making name column unique
year_released VARCHAR (5),
PRIMARY KEY(id)
);
输出：
20:22:03 CREATE DATABASE programming_languages 1 row(s) affected 0.219 sec
20:22:03 USE programming_languages 0 row(s) affected 0.000 sec
20:22:03 CREATE TABLE Details(id INT NOT NULL AUTO_INCREMENT, name VARCHAR(25) UNIQUE, year_released VARCHAR (5),PRIMARY KEY(id)) 0 row(s) affected 0.625 sec
现在，详细信息表将填充如下值。
-- Names of popular programming languages and their release year
INSERT INTO Details (name, year_released) VALUES ('python', 1991),('c++', 1985),('Java', 1995);
SELECT * FROM Details ORDER BY id; -- Previewing table
输出：
id name year_released
1 python 1991
2 c++ 1985
3 Java 1995
-----------------------------------------------------------------------------------------
20:23:24 INSERT INTO Details (name, year_released) VALUES ('python', 1991),('c++', 1985),('Java',1995) 3 row(s) affected Records: 3 Duplicates: 0 Warnings: 0 0.109 sec
20:23:24 SELECT * FROM Details ORDER BY id LIMIT 0, 1000 3 row(s) returned 0.000 sec / 0.000 sec
我们尝试在表中插入一个名为 python 的行。正如预期的那样，此操作会导致错误。
-- Trying to insert a new value
INSERT INTO Details (name, year_released) VALUES ('python', 1992)
输出：
20:27:31 INSERT INTO Details (name, year_released) VALUES ('python',1992) Error Code: 1062. Duplicate entry 'python' for key 'details.name' 0.046 sec
使用 ON DUPLICATE KEY 更新方法在具有重复键验证的 MySQL 表中插入值
此方法要么插入新值（如果表中不存在），要么更新现有行。因此，如果一行包含与新插入相同的数据，MySQL 不会进行任何更改。但是，如果其数据与插入查询不同，它会更新该行。如果该行不存在，则插入该行。
INSERT INTO Details (name, year_released) VALUES ('python', 1992) as V ON DUPLICATE KEY UPDATE name = V.name, year_released = V.year_released;
SELECT * FROM Details ORDER BY id; -- Checking the output
输出：
id name year_released
1 python 1992
2 c++ 1985
3 Java 1995
-----------------------------------------------------------------------------------------
20:47:35 INSERT INTO Details (name, year_released) VALUES ('python', 1992) as V ON DUPLICATE KEY UPDATE name = V.name, year_released = V.year_released 2 row(s) affected 0.172 sec
20:49:23 SELECT * FROM Details ORDER BY id LIMIT 0, 1000 3 row(s) returned 0.000 sec / 0.000 sec
从输出中观察到，year_released 列已针对 python 进行了更新，并且日志表明两 (2) 行受到影响（典型的行更新操作）。可以参考此扩展的官方参考以获取更多选项。
使用 REPLACE 方法在具有重复键验证的 MySQL 表中插入值
谨慎使用此方法！与之前的扩展不同，它删除行并插入包含所需数据的新行。虽然这可能看起来是良性的，但如果在操作期间删除了行的唯一关系，则可能会出现问题。
REPLACE INTO Details (name, year_released) VALUES ('python', 1993);
SELECT * FROM Details ORDER BY id;
输出：
id name year_released
2 c++ 1985
3 Java 1995
7 python 1993
-----------------------------------------------------------------------------------------
20:56:48 REPLACE INTO Details (name, year_released) VALUES ('python', 1993) 2 row(s) affected 0.093 sec
现在，观察 Insert 操作是否执行。但是，id 已更改（由于删除和插入）。建议查看此扩展的官方参考以确定安全使用和额外选项。
使用 IGNORE 方法在具有重复键验证的 MySQL 表中插入值
IGNORE 方法对具有 duplicate keys 的行没有任何作用。但是，它不会为 INSERT 操作引发错误。此方法可以处理不允许更新并且不需要引发异常/错误的情况。
INSERT IGNORE INTO Details (name, year_released) VALUES ('python', 1991);
SELECT * FROM Details ORDER BY id;
输出：
id name year_released
2 c++ 1985
3 Java 1995
7 python 1993
-----------------------------------------------------------------------------------------
21:29:44 INSERT IGNORE INTO Details (name, year_released) VALUES ('python', 1991) 0 row(s) affected, 1 warning(s): 1062 Duplicate entry 'python' for key 'details.name' 0.157 sec
正如预期的那样，MySQL 不会引发任何错误。但是，该表保持不变。
Victor is an experienced Python Developer, Machine Learning Engineer and Technical Writer with interests across various fields of science and engineering. He is passionate about learning new technologies and skill and working on challenging problems. He enjoys teaching, intellectual discourse, and gaming, among other things.
LinkedIn GitHub