一. insert ignore into
当插入数据时，如出现错误时，如重复数据，将不返回错误，只以警告形式返回。所以使用ignore请确保语句本身没有问题，否则也会被忽略掉。例如：
INSERT IGNORE INTO books (name) VALUES ('张三')
这种方法很简便，但是有一种可能，就是加入不是因为重复数据报错，而是因为其他原因报错的，也同样被忽略了～
比如：某个字段被设置了 not null 并没有设置默认值，是为了防止插入时漏掉这个字段，但是使用INSERT IGNORE后，如果没有设置这个字段默认值，将不会报错，会自动设置一个没有用的默认值，如int，则自动默认值变为0，varchar则自动变为''（空字符串）。
二. on duplicate key update
当primary或者unique重复时，则执行update语句，如update后为无用语句，如id=id，则同1功能相同，但错误不会被忽略掉。例如，为了实现name重复的数据插入不报错，可使用一下语句：
INSERT INTO books (name) VALUES ('张三') ON duplicate KEY UPDATE id = id
这种方法有个前提条件，就是，需要插入的约束，需要是主键或者唯一约束。
三. insert … select … where not exist
根据select的条件判断是否插入，可以不光通过primary 和unique来判断，也可通过其它条件。例如：
INSERT INTO content (
detail,
status,
beginTime,
endTime)
SELECT
@detail,
1,
NULL,
NULL
FROM DUAL
WHERE NOT EXISTS(
SELECT contentId
FROM content
WHERE detail=@detail);
通用例子：
INSERT INTO table(column1,column2,column3 ...columnN)
SELECT value1,value2,value3 ...valueN
FROM dual
WHERE NOT EXISTS(
SELECT *
FROM table
WHERE value = ?
);
四. replace into
replace into 插入数据时，首先会根据主键(Primary Key )或者唯一索引(Unique)判断是否存在重复数据。
有重复数据，先删除此行数据，然后插入新的数据
无重复数据，直接插入新数据
REPLACE语句返回受影响的行数。这个行数是删除和插入的行的总和。如果计数为1，说明 REPLACE则插入一行并且不删除任何行。如果计数大于1，则在插入新行之前删除一个或多个旧行。
弊端：
- 把旧记录删除之后，插入的新记录只是插入了那些指定的字段，原本不想更新的字段，直接为默认值了，会导致数据丢失。
- replace中执行delete时, 在有外键的情况下会很麻烦，更新后新记录会产生新的id，导致这种关联丢失。
- replace每次要重新分配自增id，很容易导致id值范围不够用。
- 若是数据库存在主从关系，在主机器上进行了replace into操作之后，从机器上对应表的AUTO_INCREMENT是不会更新的，导致从机器转为主机器时，新插入数据会出现异常，直到AUTO_INCREMENT增加到原来主机器的值为止。
- replace into 匹配到条件以后会先delete然后insert,而不是执行update,效率较update低很多。
- 如果delete时定义的有触发器, 则会被执行;
- 副作用也会被传播到replica slave.
对于某些场景，比如三个字段(A, B, C), A为唯一索引, 重复的情况下你不想改动B, 则:
insert into table1(A, B, C) values(1,2,3) on duplicate key update C=3;
效率上来说, INSERT ... ON DUPLICATE KEY UPDATE 比replace 要好, 毕竟replace如果重复则 先删除再插入.
网友评论