on duplicate用处:数据库中某唯一索引已经存在了则更新,不存在插入
- ON DUPLICATE KEY UPDATE为Mysql特有语法
- affected rows为了区别是update还是insert,当update的时候affected rows会是2
-- update
INSERT INTO `test_database`.`user` ( `username`, `password`, `nick_name`, `sex`, `FAVORABLE_RATE`, `register_date` )
VALUES
( '10@qq.com', '1ee04e0b1cb5af7367c80c22e42efd8b', '土豆-10', 1, 0.00, '2017-06-23 14:24:23' )
ON DUPLICATE KEY UPDATE `password` = '123';
> Affected rows: 2
> 时间: 0.006s
-- insert
INSERT INTO `test_database`.`user` ( `username`, `password`, `nick_name`, `sex`, `FAVORABLE_RATE`, `register_date` )
VALUES
( '10111111@qq.com', '1ee04e0b1cb5af7367c80c22e42efd8b', '土豆-10', 1, 0.00, '2017-06-23 14:24:23' )
ON DUPLICATE KEY UPDATE `password` = '123'
> Affected rows: 1
> 时间: 0.001s
-
多次update后,如果执行insert,自增的主键会剧增,这是由于无论进行insert还是update,都会导致id自增
这和innodb_autoinc_lock_mode
有关,innodb_autoinc_lock_mode中有3种模式,0,1,2,数据库默认是1的情况下,就发生了这种情况。所以要解决id剧增问题,要么是将innodb_autoinc_lock_mode改为0, 要么是将sql拆开,目前公司里的是这样的方法开进行的,但是多余的是在开始又去查询该记录是否存在,这一次insertOrUpdate总共写了三条sql。拆分成两个动作,先更新,更新无效再插入(使用) -
innodb_autoinc_lock_mode
tradition(innodb_autoinc_lock_mode=0) 模式:
在这一模式下,所有的insert语句(“insert like”) 都要在语句开始的时候得到一个表级的auto_inc锁,在语句结束的时候才释放这把锁,由于在这种模式下auto_inc锁一直要保持到语句的结束,所以这个就影响到了并发的插入。
consecutive(innodb_autoinc_lock_mode=1) 模式:
这一模式也是mysql的默认模式,这个模式的好处是auto_inc锁不要一直保持到语句的结束,只要语句得到了相应的值后就可以提前释放锁
interleaved(innodb_autoinc_lock_mode=2) 模式:
由于这个模式下已经没有了auto_inc锁,所以这个模式下的性能是最好的;但是它也有一个问题,就是对于同一个语句来说它所得到的auto_incremant值可能不是连续的。
由于用户访问量较大,0模式虽然只有实际的发生insert的时候才增加,但是每次都会在语句执行期间锁表,并发性不太好。
解决方法:
- 拆分sql
- 修改innodb_autoinc_lock_mode参数为0
∴目前决定的方式是拆分成两个动作,先更新,更新无效再插入