Mysql creates auto-increasing sequence

Create table¶

drop table if exists sequences;
create table sequences
(
  SEQUENCE_NAME varchar(80) not null comment'Auto-increasing sequence name'
    primary key,
  INCREMENT_BY int not null comment'step size',
  CURRENT_VALUE bigint not null comment'current value',
  MIN_VALUE bigint null comment'Minimum value',
  MAX_VALUE bigint null comment'Maximum value',
  COMMENTS varchar(200) null comment'Sequence description',
  CYCLE varchar(10) null comment'When the value is CYCLE, it represents a cycle'
);

Create function¶

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;

example¶

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

usage¶

select nextSeq('PHONE_NBR_SEQ')
from dual;