在 MySQL 表中使用重复键验证插入值

数据库 2周前 12

传统 SQL 的 INSERT 语句不会针对现有数据库表执行其参数/值的输入验证。在插入过程中发现重复键时，有时会导致错误。

这在 MySQL 中通过 INSERT 的扩展与 ON DUPLICATE KEY UPDATE、REPLACE 和 IGNORE 等语句进行处理。

为了说明这些方法，让我们创建一个名为 programming_languages 的示例数据库。

-- Here goes the definition of the database
CREATEDATABASEprogramming_languages;USEprogramming_languages;-- Creating a table
CREATETABLEDetails(idINTNOTNULLAUTO_INCREMENT,nameVARCHAR(25)UNIQUE,-- Making name column unique
year_releasedVARCHAR(5),PRIMARYKEY(id));

输出：

20:22:03CREATEDATABASEprogramming_languages1row(s)affected0.219sec20:22:03USEprogramming_languages0row(s)affected0.000sec20:22:03CREATETABLEDetails(idINTNOTNULLAUTO_INCREMENT,nameVARCHAR(25)UNIQUE,year_releasedVARCHAR(5),PRIMARYKEY(id))0row(s)affected0.625sec

现在，详细信息表将填充如下值。

-- Names of popular programming languages and their release year
INSERTINTODetails(name,year_released)VALUES('python',1991),('c++',1985),('Java',1995);SELECT*FROMDetailsORDERBYid;-- Previewing table

输出：

idnameyear_released1python19912c++19853Java1995-----------------------------------------------------------------------------------------
20:23:24INSERTINTODetails(name,year_released)VALUES('python',1991),('c++',1985),('Java',1995)3row(s)affectedRecords:3Duplicates:0Warnings:00.109sec20:23:24SELECT*FROMDetailsORDERBYidLIMIT0,10003row(s)returned0.000sec/0.000sec

我们尝试在表中插入一个名为 python 的行。正如预期的那样，此操作会导致错误。

-- Trying to insert a new value
INSERTINTODetails(name,year_released)VALUES('python',1992)

输出：

20:27:31INSERTINTODetails(name,year_released)VALUES('python',1992)ErrorCode:1062.Duplicateentry'python'forkey'details.name'0.046sec

使用 `ON DUPLICATE KEY` 更新方法在具有重复键验证的 MySQL 表中插入值

此方法要么插入新值（如果表中不存在），要么更新现有行。因此，如果一行包含与新插入相同的数据，MySQL 不会进行任何更改。但是，如果其数据与插入查询不同，它会更新该行。如果该行不存在，则插入该行。

INSERTINTODetails(name,year_released)VALUES('python',1992)asVONDUPLICATEKEYUPDATEname=V.name,year_released=V.year_released;SELECT*FROMDetailsORDERBYid;-- Checking the output

输出：

idnameyear_released1python19922c++19853Java1995-----------------------------------------------------------------------------------------
20:47:35INSERTINTODetails(name,year_released)VALUES('python',1992)asVONDUPLICATEKEYUPDATEname=V.name,year_released=V.year_released2row(s)affected0.172sec20:49:23SELECT*FROMDetailsORDERBYidLIMIT0,10003row(s)returned0.000sec/0.000sec

从输出中观察到，year_released 列已针对 python 进行了更新，并且日志表明两 (2) 行受到影响（典型的行更新操作）。可以参考此扩展的官方参考以获取更多选项。

使用 `REPLACE` 方法在具有重复键验证的 MySQL 表中插入值

谨慎使用此方法！与之前的扩展不同，它删除行并插入包含所需数据的新行。虽然这可能看起来是良性的，但如果在操作期间删除了行的唯一关系，则可能会出现问题。

REPLACEINTODetails(name,year_released)VALUES('python',1993);SELECT*FROMDetailsORDERBYid;

输出：

idnameyear_released2c++19853Java19957python1993-----------------------------------------------------------------------------------------
20:56:48REPLACEINTODetails(name,year_released)VALUES('python',1993)2row(s)affected0.093sec

现在，观察 Insert 操作是否执行。但是，id 已更改（由于删除和插入）。建议查看此扩展的官方参考以确定安全使用和额外选项。

使用 `IGNORE` 方法在具有重复键验证的 MySQL 表中插入值

IGNORE 方法对具有 duplicate keys 的行没有任何作用。但是，它不会为 INSERT 操作引发错误。此方法可以处理不允许更新并且不需要引发异常/错误的情况。

INSERTIGNOREINTODetails(name,year_released)VALUES('python',1991);SELECT*FROMDetailsORDERBYid;

输出：

idnameyear_released2c++19853Java19957python1993-----------------------------------------------------------------------------------------
21:29:44INSERTIGNOREINTODetails(name,year_released)VALUES('python',1991)0row(s)affected,1warning(s):1062Duplicateentry'python'forkey'details.name'0.157sec

正如预期的那样，MySQL 不会引发任何错误。但是，该表保持不变。

声明：本站所有文章，如无特殊说明或标注，均为本站原创发布，任何个人或组织，在未征得本站同意时，禁止复制、盗用、采集、发布本站内容到任何网站。本站所有源码与软件均为原作者提供，仅供学习和研究使用。如您对本站的相关版权有任何异议，或者认为侵犯了您的合法权益，请及时通知我们处理。