需求
某个系统,每天会创建若干单,生成一个数据表,统计每天创建了多少个单。
分析
我们可以以天数为唯一索引,如果当天有数据,就更新,如果没有数据,就新增
创表语句:
其中我们把date作为唯一键
CREATE TABLE `TABLENAME` (
`ID` int NOT NULL AUTO_INCREMENT,
`count` int NULL DEFAULT NULL,
`date` date NOT NULL,
PRIMARY KEY (`ID`) USING BTREE,
UNIQUE INDEX `unique_01`(`date`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;
执行SQL
这里我们是用 ON DUPLICATE KEY UPDATE ,如果没有今天的数据就创建,如果有今天的数据就新增
INSERT INTO countcurrentdateissues_copy1 (count, date) VALUES(1,CURRENT_DATE) ON DUPLICATE KEY UPDATE count = count + 1;
到这里就结束了吗?没有!如果按照上面的方式编写代码,那么将会发现一个神奇的现象,每次新插入的数据的主键ID都是上一天的主键ID+上一天的count。
这里需要注意一点的是,mysql 的有这么一个配置innodb_autoinc_lock_mode。
这个有3中模式,0, 1 和 2, mysql5的默认配置是1,
- 0是每次分配自增id的时候都会锁表.
- 1只有在bulk insert的时候才会锁表,简单insert的时候只会使用一个light-weight mutex,比0的并发性能高
- 2.没有仔细看
数据库默认是1的情况下,就会发生上面的那种现象,每次使用insert into .. on duplicate key update 的时候都会把简单自增id增加,不管是发生了insert还是update
而改为0后,就解决了这个问题,只有实际的发生insert的时候才增加,但是每次都会锁表,并发性不太好.如果项目并发高,不建议修改此项
最简单的规避方法:
ALTER TABLE TABLENAME AUTO_INCREMENT = 1;
来源:
MySQL使用on duplicate key update引起主键不连续自增 https://blog.csdn.net/eleanoryss/article/details/82997899
mysql ON DUPLICATE KEY UPDATE 引起自增ID变化的解决办法 https://blog.csdn.net/qq_33578832/article/details/87883371