MySQL获取自增序列

谁借莪１个温暖的怀抱￠ 2024-04-19 12:10 11阅读 0赞

因为业务要求，需要在MySQL数据库中，获取下一个自增主键的值。

原先采用的方法是：

select AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = '数据库名称' AND TABLE_NAME = '表名称' limit 1

但是这样写的话，在8.0以后的版本是取不到值的，必须在每一次需要获取自增序列之前，多执行一条表分析的语句。然后语句就会变成下面这样：

ANALYZE TABLE 表名;
    select auto_increment FROM information_schema.tables 
    WHERE TABLE_SCHEMA = '库名' AND TABLE_NAME = '表名' limit 1;

这样写会造成一个问题，当表中的数据较多的时候，会造成查询的速度变慢。

因此，就要使用另外一个策略，就是自己创建一个自增序列。

这里参考了[ mysql 中创建自增的序列（Sequence）][mysql _Sequence]

由于mysql和oracle不太一样，不支持直接的sequence，所以需要创建一张table来模拟sequence的功能。

## 1.创建sequence表 ##

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

## 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名称和初始值，以及自增幅度
    
    SELECT SETVAL('testSeq', 10);--设置指定sequence的初始值
    
    SELECT CURRVAL('testSeq');--查询指定sequence的当前值
    
    SELECT NEXTVAL('testSeq');--查询指定sequence的下一个值

这里，testSeq可以认为是一个表名，我们可以通过insert语句插入指定表的第一个序列，然后使用NEXTVAL方法，不断更新这一列数据，来获取下一个序列的值。可以通过这张表，来实现N张表的自增序列的统一管理。

[mysql _Sequence]: https://blog.csdn.net/u012373815/article/details/78179411