mysql 自定义序列

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE sequence (
    seq_name VARCHAR(50) NOT NULL,
    next_val INT NOT NULL,
    PRIMARY KEY (seq_name)
);

CREATE TRIGGER trg_before_insert_users
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    DECLARE next_id INT;
    UPDATE sequence SET next_val = LAST_INSERT_ID(next_val + 1) WHERE seq_name = 'user_seq';
    SET NEW.id = LAST_INSERT_ID();
END;

DELIMITER //

CREATE PROCEDURE next_seq_val(IN seq_name VARCHAR(50), OUT next_val INT)
BEGIN
    UPDATE sequence SET next_val = LAST_INSERT_ID(next_val + 1) WHERE seq_name = seq_name;
    SELECT LAST_INSERT_ID() INTO next_val;
END //

DELIMITER ;

ALTER TABLE users AUTO_INCREMENT = 100;