新增时约束
mysql在存在主键冲突或者唯一键冲突的情况下,根据插入策略不同,一般有以下三种避免方法。
- 注意,除非表有一个PRIMARY KEY或UNIQUE索引,否则,使用以下三个语句没有意义,与使用单纯的INSERT INTO相同。
1、insert ignore
2、replace into
replace into 首先尝试插入数据到表中。 如果发现表中已经有此行数据(根据主键或者唯一索引判断)则先删除此行数据,然后插入新的数据,否则,直接插入新数据。
使用replace into,你必须具有delete和insert权限
3、insert on duplicate key update
使用insert into,你必须具有insert和update权限
如果有新记录被插入,则受影响行的值显示1;如果原有的记录被更新,则受影响行的值显示2;如果记录被更新前后值是一样的,则受影响行数的值显示0
*INSERT…ON DUPLICATE KEY UPDATE产生死锁
insert … on duplicate key 在执行时,innodb引擎会先判断插入的行是否产生重复key错误,
如果存在,在对该现有的行加上S(共享锁)锁,如果返回该行数据给mysql,然后mysql执行完duplicate后的update操作,
然后对该记录加上X(排他锁),最后进行update写入。如果有两个事务并发的执行同样的语句,
那么就会产生死锁,如:*
mysql在存在主键冲突或者唯一键冲突的情况下,根据插入策略不同,一般有以下三种避免方法。
- 注意,除非表有一个PRIMARY KEY或UNIQUE索引,否则,使用以上三个语句没有意义,与使用单纯的INSERT INTO相同。
1、insert ignore
2、replace into
replace into 首先尝试插入数据到表中。 如果发现表中已经有此行数据(根据主键或者唯一索引判断)则先删除此行数据,然后插入新的数据,否则,直接插入新数据。
使用replace into,你必须具有delete和insert权限
3、insert on duplicate key update
使用insert into,你必须具有insert和update权限
如果有新记录被插入,则受影响行的值显示1;如果原有的记录被更新,则受影响行的值显示2;如果记录被更新前后值是一样的,则受影响行数的值显示0
*INSERT…ON DUPLICATE KEY UPDATE产生死锁
insert … on duplicate key 在执行时,innodb引擎会先判断插入的行是否产生重复key错误,
如果存在,在对该现有的行加上S(共享锁)锁,如果返回该行数据给mysql,然后mysql执行完duplicate后的update操作,
然后对该记录加上X(排他锁),最后进行update写入。如果有两个事务并发的执行同样的语句,
那么就会产生死锁,如:*
结论
- 这三种方法都能避免主键或者唯一索引重复导致的插入失败问题。
- insert ignore能忽略重复数据,只插入不重复的数据。
- replace into和insert … on duplicate key update,都是替换原有的重复数据,区别在于replace into是删除原有的行后,在插入新行,如有自增id,这个会造成自增id的改变;insert … on duplicate key update在遇到重复行时,会直接更新原有的行,具体更新哪些字段怎么更新,取决于update后的语句。
注意逻辑删除
如果表使用了逻辑删除, 那么所有的唯一性约束都应该加上表对应的 delete
字段, 切记不可以单独 只设置一个字段的 唯一索引.