Mysql创建自增序列

创建表
drop table if exists sequences;
create table sequences
(
  SEQUENCE_NAME varchar(80)  not null comment '自增序列名称'
    primary key,
  INCREMENT_BY  int          not null comment '步长',
  CURRENT_VALUE bigint       not null comment '当前值',
  MIN_VALUE     bigint       null comment '最小值',
  MAX_VALUE     bigint       null comment '最大值',
  COMMENTS      varchar(200) null comment '序列描述',
  CYCLE         varchar(10)  null comment '当值为CYCLE时,代表循环'
);



创建函数
drop function if exists nextSeq;
create function nextSeq(a_seq_name varchar(55))
  returns bigint
  BEGIN
    DECLARE seq_val BIGINT;
    DECLARE min_val BIGINT;
    DECLARE max_val BIGINT;
    DECLARE cycle_val VARCHAR(10);
    SET seq_val = -1;
    IF EXISTS(SELECT 1
              FROM sequences holdlock
              WHERE SEQUENCE_NAME = a_seq_name)
    THEN
      SELECT
        CURRENT_VALUE + INCREMENT_BY,
        MIN_VALUE,
        MAX_VALUE,
        CYCLE
      INTO seq_val, min_val, max_val, cycle_val
      FROM sequences
      WHERE SEQUENCE_NAME = a_seq_name FOR UPDATE;
      IF seq_val > max_val
      THEN
        IF cycle_val = 'CYCLE'
        THEN
          SET seq_val = min_val;
        ELSE
          SIGNAL SQLSTATE '42000'
          SET MESSAGE_TEXT ='error:1000,sequence beyond the max value ';
        END IF;
      END IF;
      UPDATE sequences
      SET CURRENT_VALUE = seq_val
      WHERE SEQUENCE_NAME = a_seq_name;
    ELSE
      SIGNAL SQLSTATE '42000'
      SET MESSAGE_TEXT ='error:1001,Query was empty,sequence name not found ';
    END IF;
    RETURN seq_val;
  END;

样例
insert into sequences(sequence_name, increment_by, current_value, min_value, max_value, comments, cycle)
value ('PHONE_NBR_SEQ', 1, 1000000000, 1000000000, 1999999999, '手机号码序列', null);

使用：
select nextSeq('PHONE_NBR_SEQ')
from dual;