在写程序的时候经常碰到在向数据库中插入数据时,判断数据是否已存在。诸如有存在的数据时跳过,不存在的数据继续插入,要避免重复插入,又不想折腾两回数据库连接操作,这里可能会用到以下语句,现小结一下。
INSERT IGNORE INTO
IGNORE
是MySQL相对于标准SQL的扩展。如果在新表中有重复关键字,此种方法效率比较高,判断是否存在,存在会丢弃掉这行数据,不做任何插入,否则插入。当插入数据时,如出现错误时,如重复数据,将不返回错误,只以警告形式返回。所以使用ignore请确保语句本身没有问题,否则也会被忽略掉。这样不用校验是否存在了,有则忽略,无则添加。例如:
ON DUPLICATE KEY UPDATE
当primary或者unique重复时,则执行UPDATE
语句,如UPDATE
后为无用语句,如id=id
,则同1功能相同,但错误不会被忽略掉。例如,为了实现重复的数据插入不报错,可使用一下语句:
如果行作为新记录被插入,则受影响行的值为1;如果原有的记录被更新,则受影响行的值为2。如果列b也是唯一列,则INSERT
与此UPDATE
语句相当。
您可以在UPDATE
子句中使用VALUES(col_name)
函数从INSERT...UPDATE
语句的INSERT
部分引用列值。换句话说,如果没有发生重复关键字冲突,则UPDATE
子句中的VALUES(col_name)
可以引用被插入的col_name
的值。本函数特别适用于多行插入。VALUES()
函数只在INSERT...UPDATE
语句中有意义,其它时候会返回NULL
。例如:
INSERT … SELECT … WHERE NOT EXISTS
根据select
的条件判断是否插入,可以不光通过primary 和unique来判断,也可通过其它条件。例如:
REPLACE INTO
需求表中有PrimaryKey,或者unique索引,如果数据库已经存在数据,则用新数据替换,如果没有数据效果则和insert into
一样。(此种方法是利用替换的方法,有点似类于先删除再插入。如果存在primary or unique相同的记录,则先删除掉。再插入新记录。
REPLACE
语句会返回一个数,来指示受影响的行的数目。该数是被删除和被插入的行数的和。如果对于一个单行REPLACE
该数为1,则一行被插入,同时没有行被删除。如果该数大于1,则在新行被插入前,有一个或多个旧行被删除。如果表包含多个唯一索引,并且新行复制了在不同的唯一索引中的不同旧行的值,则有可能是一个单一行替换了多个旧行。
说在最后:很多情况下建立表唯一索引,能给我们省下很多麻烦事,想上面说的REPLACE INTO
就必须是有唯一索引,当然,很多人喜欢用到的自增主键ID尽管也是唯一索引,我建议是能在其他经常出现在where条件屁股后面的字段,并且值是唯一的话可以加个索引甚至唯一索引,查询速度将会提高很多。
大家要根据自己的需求来确定,不一定就适合你现在的需求;我只是提示大家还有这么些个实用的小知识大家可以灵活运用到项目里边。