迹忆客 专注技术分享

传统 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 不会引发任何错误。但是，该表保持不变。