mysql遞增統計數量，mysql實作自增序列，教你如何實作線程安全的mysql自增統計

文章目錄

• ​​寫在前面​​
• ​​建立遞增主鍵​​
• ​​先查後加​​
• ​​在update中實作遞增​​

• ​​死鎖問題​​

• ​​使用自定義序列​​
• ​​參考資料​​

寫在前面

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();
}      

初始值：

執行結果：

死鎖問題

以上使用update實作遞增，在mysql5版本中，會導緻死鎖。

但是在mysql8版本中，是沒問題的。

這是一個坑！大家要注意。

不過，現在mysql大部分企業都用8版本的了，5版本已經很少用了。

使用自定義序列

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();
}      

經測試，完全經得起并發的考驗，不會有線程安全問題。

參考資料