返回顶部

收藏

MySQL序列解决方案

更多
-- 定义序列表
DROP TABLE IF EXISTS sequence;
CREATE TABLE sequence (
    name VARCHAR(50) NOT NULL,
    current_value INT NOT NULL,
    increment INT NOT NULL DEFAULT 1,
    PRIMARY KEY (name)
) ENGINE=InnoDB;

-- 获取当前序列号
DROP FUNCTION IF EXISTS currval;
DELIMITER $
CREATE FUNCTION currval (seq_name VARCHAR(50))
RETURNS INTEGER
CONTAINS SQL
BEGIN
    DECLARE value INTEGER;
    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 INTEGER
CONTAINS SQL
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 INTEGER)
RETURNS INTEGER
CONTAINS SQL
BEGIN
    UPDATE sequence SET current_value = value
    WHERE name = seq_name;
    RETURN currval(seq_name);
    END$
DELIMITER ;

-- 初始化数据
INSERT INTO sequence VALUES ('SAMPLE', 1, 1);

-- 测试
SELECT currval('SAMPLE');
SELECT nextval('SAMPLE');
SELECT nextval('SAMPLE');
SELECT setval('SAMPLE',150);
SELECT currval('SAMPLE');
SELECT nextval('SAMPLE');
SELECT nextval('SAMPLE');

标签:sql

收藏

0人收藏

支持

0

反对

0

发表评论