MySQL插入数据前检测唯一性的一些语句用法总结

朴灿烈づ我的快乐病毒、 2022-06-05 00:22 386阅读 0赞

在写程序的时候经常碰到在向数据库中插入数据时，判断数据是否已存在。诸如有存在的数据时跳过，不存在的数据继续插入，要避免重复插入，又不想折腾两回数据库连接操作，这里可能会用到以下语句，现小结一下。

### INSERT IGNORE INTO ###

`IGNORE`是MySQL相对于标准SQL的扩展。如果在新表中有重复关键字，此种方法效率比较高,判断是否存在，存在会丢弃掉这行数据，不做任何插入，否则插入。当插入数据时，如出现错误时，如重复数据，将不返回错误，只以警告形式返回。所以使用ignore请确保语句本身没有问题，否则也会被忽略掉。这样不用校验是否存在了，有则忽略，无则添加。例如：

INSERT IGNORE INTO books (name) VALUES ('MySQL Manual')

### ON DUPLICATE KEY UPDATE ###

当primary或者unique重复时，则执行`UPDATE`语句，如`UPDATE`后为无用语句，如`id=id`，则同1功能相同，但错误不会被忽略掉。例如，为了实现重复的数据插入不报错，可使用一下语句：

INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDAT cut = cut +1

如果行作为新记录被插入，则受影响行的值为1；如果原有的记录被更新，则受影响行的值为2。如果列b也是唯一列，则`INSERT`与此`UPDATE`语句相当。

您可以在`UPDATE`子句中使用`VALUES(col_name)`函数从`INSERT...UPDATE`语句的`INSERT`部分引用列值。换句话说，如果没有发生重复关键字冲突，则`UPDATE`子句中的`VALUES(col_name)`可以引用被插入的`col_name`的值。本函数特别适用于多行插入。`VALUES()`函数只在`INSERT...UPDATE`语句中有意义，其它时候会返回`NULL`。例如：

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6) ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b)

### INSERT … SELECT … WHERE NOT EXISTS ###

根据`select`的条件判断是否插入，可以不光通过primary 和unique来判断，也可通过其它条件。例如：

INSERT INTO books (name) SELECT 'MySQL Manual' FROM dual WHERE NOT EXISTS (SELECT id FROM books WHERE id = 1)

### REPLACE INTO ###

需求表中有PrimaryKey，或者unique索引，如果数据库已经存在数据，则用新数据替换，如果没有数据效果则和`insert into`一样。(此种方法是利用替换的方法，有点似类于先删除再插入。如果存在primary or unique相同的记录，则先删除掉。再插入新记录。

REPLACE INTO books SELECT 1, 'MySQL Manual' FROM books

`REPLACE`语句会返回一个数，来指示受影响的行的数目。该数是被删除和被插入的行数的和。如果对于一个单行`REPLACE`该数为1，则一行被插入，同时没有行被删除。如果该数大于1，则在新行被插入前，有一个或多个旧行被删除。如果表包含多个唯一索引，并且新行复制了在不同的唯一索引中的不同旧行的值，则有可能是一个单一行替换了多个旧行。

注：`REPLACE`发现重复的先删除再插入，如果记录有多个字段，在插入的时候如果有的字段没有赋值，那么新插入的记录这些字段为空。

说在最后：很多情况下建立表唯一索引，能给我们省下很多麻烦事，想上面说的`REPLACE INTO`就必须是有唯一索引，当然，很多人喜欢用到的自增主键ID尽管也是唯一索引，我建议是能在其他经常出现在where条件屁股后面的字段，并且值是唯一的话可以加个索引甚至唯一索引，查询速度将会提高很多。

大家要根据自己的需求来确定，不一定就适合你现在的需求；我只是提示大家还有这么些个实用的小知识大家可以灵活运用到项目里边。