mysql sequence循环

-- 创建表
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    order_number VARCHAR(20) NOT NULL
);

-- 创建触发器
DELIMITER $$
CREATE TRIGGER generate_order_number
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    DECLARE order_number VARCHAR(20);
    SET order_number = CONCAT('ORD', LPAD(NEW.order_id, 6, '0'));
    SET NEW.order_number = order_number;
END$$
DELIMITER ;

-- 创建序列表
CREATE TABLE sequence_table (
    seq_name VARCHAR(50) PRIMARY KEY,
    current_value INT NOT NULL,
    increment INT NOT NULL,
    max_value INT NOT NULL,
    cycle BOOLEAN NOT NULL
);

-- 初始化序列
INSERT INTO sequence_table (seq_name, current_value, increment, max_value, cycle)
VALUES ('order_seq', 1, 1, 999999, TRUE);

-- 创建获取序列值的存储过程
DELIMITER $$
CREATE PROCEDURE get_next_value(IN seq_name VARCHAR(50), OUT next_value INT)
BEGIN
    DECLARE current_value INT;
    DECLARE increment INT;
    DECLARE max_value INT;
    DECLARE cycle BOOLEAN;
    DECLARE new_value INT;

    SELECT current_value, increment, max_value, cycle INTO current_value, increment, max_value, cycle
    FROM sequence_table WHERE seq_name = seq_name;

    SET new_value = current_value + increment;

    IF new_value > max_value THEN
        IF cycle THEN
            SET new_value = increment;
        ELSE
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Sequence overflow';
        END IF;
    END IF;

    UPDATE sequence_table SET current_value = new_value WHERE seq_name = seq_name;
    SET next_value = new_value;
END$$
DELIMITER ;