mysql实现sequence效果

-- Sequence 管理表
DROP TABLE IF EXISTS sequence;
CREATE TABLE sequence (
      name VARCHAR(50) NOT NULL COMMENT '序列名',
      current_value BIGINT NOT NULL COMMENT '当前序列值',
      increment INT NOT NULL DEFAULT 1 COMMENT '步长',
      PRIMARY KEY (name)
) ENGINE=InnoDB;

-- 取当前值的函数
DROP FUNCTION IF EXISTS currval;
DELIMITER $
CREATE FUNCTION currval (seq_name VARCHAR(50))
    RETURNS BIGINT
    LANGUAGE SQL
    DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT '取当前的sequence'
BEGIN
    DECLARE value BIGINT;
    SET value = 0;
    SELECT current_value INTO value
    FROM sequence
    WHERE name = seq_name;
    RETURN value;
END
$
DELIMITER ;

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

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

/*
-- 测试
INSERT INTO sequence VALUES ('DOC_CHANNEL_SEQUENCE', 0, 1);
SELECT SETVAL('DOC_CHANNEL_SEQUENCE', 0);
SELECT CURRVAL('DOC_CHANNEL_SEQUENCE');
SELECT NEXTVAL('DOC_CHANNEL_SEQUENCE');
*/