mysql递增统计数量，mysql实现自增序列，教你如何实现线程安全的mysql自增统计

写在前面

oracle是有自增序列的机制的，而mysql并没有。
那么mysql想要实现字段的自增从而统计数量，或者实现一个自增序列需要怎么做呢？

一、创建递增主键（不推荐）

该方法……怎么说呢，不可取。
递增主键的方式，怎么着也不能用来做业务逻辑用。

二、先查后加（不推荐）

TeacherModel teacher = teacherMapper.selectOne(id);

Teacher teacher = new Teacher();
teacher.setId(id);
teacher.setLikeCount(teacher.getLikeCount() + 1);
teacherMapper.updateLikeCount(teacher);

以上可能是部分新同学实现自增统计的例子，那么问题来了，以上代码会有什么问题？

答：线程安全问题。

先查，后加，如果是单线程下是没问题的，但是统计数量通常并不是一个用户维度来统计的，所以当并发量提上来之后，就不可避免的会出现数据比预想少的问题。

三、在update中实现递增

1.我们准备一条数据

INSERT INTO `springboot`.`test_teacher`(`id`, `name`, `class_id`, `like_count`) VALUES ('1', '青岛大学2', '1', 0);

2.实现like_count字段的递增

UPDATE test_teacher t 
SET like_count = ( SELECT a2.like_count + 1 FROM ( SELECT a1.like_count FROM test_teacher a1 WHERE a1.id = 1 ) a2 ) 
WHERE
	t.id =1;

我们发现，like_count 字段加了1：

每执行一次上面的sql语句，like_count字段都会加1。

3.set后面为什么要加那么多层select？

当我们简化为以下语句的时候：

UPDATE test_teacher t 
SET like_count = ( SELECT a1.like_count + 1 FROM test_teacher a1 WHERE a1.id = 1 )
WHERE
	t.id =1;

mysql就会报错：
You can’t specify target table ‘t’ for update in FROM clause

不能在update更新中在select本表，这种解决方案就是再套一层select查询。

4.并发安全问题

我们在程序中测试一下：

mapper：

<update id="likeCount">
	UPDATE test_teacher t
	SET like_count = ( SELECT a2.like_count + 1 FROM ( SELECT a1.like_count FROM test_teacher a1 WHERE a1.id = #{id} ) a2 )
	WHERE
		t.id =#{id};
</update>

程序：

for (int i = 0;i<30;i++) {
    new Thread(() -> {
        schoolDao.likeCount("1");
    }).start();
}

初始值：

执行结果：

1、死锁问题

以上使用update实现递增，在mysql5版本中，会导致死锁。

但是在mysql8版本中，是没问题的。

这是一个坑！大家要注意。

不过，现在mysql大部分企业都用8版本的了，5版本已经很少用了。

但是，如果使用ON DUPLICATE KEY UPDATE的方式，仍然存在死锁问题，比如：

insert into `school` ( id, name, like_count)
		values ('1', '北大', '0')
			ON DUPLICATE KEY UPDATE
			`like_count` = `like_count` + 1

但是这种死锁，springboot会自己处理，不会影响应用，只不过计数会少一次。

2、update优化

上面的update语句很明显是很啰嗦的，下面可以进行一下优化：

<update id="likeCount">
	UPDATE test_teacher t
	SET like_count = like_count +1
	WHERE
		t.id =#{id};
</update>

四、使用自定义序列

1.创建序列表

CREATE TABLE `sequence` (
  `name` varchar(50) NOT NULL COMMENT '序列的名字',
  `current_value` int(11) NOT NULL COMMENT '序列的当前值',
  `increment` int(11) NOT NULL DEFAULT '1' COMMENT '序列的自增值',
  PRIMARY KEY (`name`)
) ENGINE=InnoDB;

2.创建-获取序列当前值的方法

DROP FUNCTION IF EXISTS currval; 
DELIMITER $ 
CREATE FUNCTION currval (seq_name VARCHAR(50)) 
     RETURNS INTEGER
     LANGUAGE SQL 
     DETERMINISTIC 
     CONTAINS SQL 
     SQL SECURITY DEFINER 
     COMMENT '获取指定序列当前值'
BEGIN
     DECLARE value INTEGER; 
     SET value = 0; 
     SELECT current_value INTO value 
          FROM sequence
          WHERE name = seq_name; 
     RETURN value; 
END
$ 
DELIMITER ;

3.创建-获取序列下一个值的方法

DROP FUNCTION IF EXISTS nextval; 
DELIMITER $ 
CREATE FUNCTION nextval (seq_name VARCHAR(50)) 
     RETURNS INTEGER 
     LANGUAGE SQL 
     DETERMINISTIC 
     CONTAINS SQL 
     SQL SECURITY DEFINER 
     COMMENT '获取序列下一个值' 
BEGIN 
     UPDATE sequence 
          SET current_value = current_value + increment 
          WHERE name = seq_name; 
     RETURN currval(seq_name); 
END 
$ 
DELIMITER ;

4.创建-更新序列当前值的方法

DROP FUNCTION IF EXISTS setval; 
DELIMITER $ 
CREATE FUNCTION setval (seq_name VARCHAR(50), value INTEGER) 
     RETURNS INTEGER 
     LANGUAGE SQL 
     DETERMINISTIC 
     CONTAINS SQL 
     SQL SECURITY DEFINER 
     COMMENT '更新序列当前值' 
BEGIN 
     UPDATE sequence 
          SET current_value = value 
          WHERE name = seq_name; 
     RETURN currval(seq_name); 
END 
$ 
DELIMITER ;

5.测试执行

INSERT INTO sequence VALUES ('testSeq', 0, 1);-- 添加一个sequence名称和初始值0，以及自增幅度1
SELECT SETVAL('testSeq', 10);-- 设置指定sequence的初始值
SELECT CURRVAL('testSeq');-- 查询指定sequence的当前值
SELECT NEXTVAL('testSeq');-- 查询指定sequence的下一个值

6.实战测试

UPDATE test_teacher t
		SET like_count = NEXTVAL('testSeq')
		WHERE
			t.id =1;

每次执行，like_count 字段数据都会加1。

7.并发下测试执行

<update id="likeCount">
	UPDATE test_teacher t
	SET like_count = NEXTVAL('testSeq')
	WHERE
		t.id =#{id};
</update>

for (int i = 0;i<30;i++) {
    new Thread(() -> {
        schoolDao.likeCount("1");
    }).start();
}

经测试，完全经得起并发的考验，不会有线程安全问题。

重要的是，在mysql5.0版本中，也不会出现死锁问题！

参考资料

https://blog.csdn.net/wangxueying5172/article/details/118712728
https://www.bilibili.com/read/cv14411398/