MySQL中的insert ignore into, replace into等的一些用法

innodb锁-insert导致的死锁

结论: 多线程写，并且有重复数据，会因唯一约束检查，导致加间隙锁，当多个事务都需要向其它线程持有锁写数据时，死锁发生

解决方案：

1. 不用insert ignore 和 ON DUPLICATE KEY UPDATE，把 SQL 拆开，判断好需要插入还是更新再操作；
2. 单线程操作；
3. 降隔离级别（未测试）

CREATE TABLE `books` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `createTime` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `NewIndex` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

注意，除非表有一个PRIMARY KEY或UNIQUE索引，否则，使用以下四个语句没有意义，与使用单纯的INSERT INTO相同。

1.insert ignore into

当插入数据时，如出现错误时，如重复数据，将不会插入数据也不会返回错误(Duplicate entry 'MySQL Manual' for key 'NewIndex',不包括字段不存在等其他错误)，只以警告形式返回,执行后返回数字0。使用ignore请确保语句本身没有问题，否则也会被忽略掉。

比如：某个字段被设置了 not null 并没有设置默认值，是为了防止插入时漏掉这个字段，但是使用INSERT IGNORE后，如果没有设置这个字段默认值，将不会报错，会自动设置一个没有用的默认值，如int，则自动默认值变为0，varchar则自动变为''（空字符串）。

INSERT  IGNORE INTO books (name,createTime) VALUES ('MySQL Manual',now());

2.on duplicate key update(推荐使用???)

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

INSERT INTO books (name,createTime) VALUES ('MySQL Manual',now()) ON duplicate KEY UPDATE id = id;

3.insert … select … where not exist

根据select的条件判断是否插入，可以不光通过primary 和unique来判断，也可通过其它条件。但是貌似不支持批量插入. 例如：

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

4.replace into

如果存在primary or unique相同的记录，则先删除掉 , 再插入新记录 , 受影响的行的数为被删除和被插入的行数的和。

REPLACE INTO books(id,name,createTime) SELECT 1, 'MySQL Manual',now() FROM books;

弊端：

1. 把旧记录删除之后，插入的新记录只是插入了那些指定的字段，原本不想更新的字段，直接为默认值了，会导致数据丢失。
2. replace中执行delete时, 在有外键的情况下会很麻烦，更新后新记录会产生新的id，导致这种关联丢失。
3. replace每次要重新分配自增id，很容易导致id值范围不够用。
4. 若是数据库存在主从关系，在主机器上进行了replace into操作之后，从机器上对应表的AUTO_INCREMENT是不会更新的，导致从机器转为主机器时，新插入数据会出现异常，直到AUTO_INCREMENT增加到原来主机器的值为止。
5. replace into 匹配到条件以后会先delete然后insert,而不是执行update,效率较update低很多。
6. 如果delete时定义的有触发器, 则会被执行;
7. 副作用也会被传播到replica slave.