mysql 产生序列

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

INSERT INTO users (name) VALUES ('Alice');
INSERT INTO users (name) VALUES ('Bob');

SELECT * FROM users;

CREATE TABLE sequence (
    name VARCHAR(50) NOT NULL,
    current_value INT NOT NULL,
    increment INT NOT NULL DEFAULT 1,
    PRIMARY KEY (name)
);

INSERT INTO sequence (name, current_value) VALUES ('user_id_seq', 0);

DELIMITER $$
CREATE PROCEDURE nextval(IN seq_name VARCHAR(50))
BEGIN
    UPDATE sequence SET current_value = current_value + increment WHERE name = seq_name;
    SELECT current_value FROM sequence WHERE name = seq_name;
END$$
DELIMITER ;

CALL nextval('user_id_seq');

DELIMITER $$
CREATE FUNCTION nextval(seq_name VARCHAR(50)) RETURNS INT
BEGIN
    DECLARE current_value INT;
    UPDATE sequence SET current_value = current_value + increment WHERE name = seq_name;
    SELECT current_value INTO current_value FROM sequence WHERE name = seq_name;
    RETURN current_value;
END$$
DELIMITER ;

SELECT nextval('user_id_seq');

ALTER TABLE users AUTO_INCREMENT = 100;