mysql如何创建sequence

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

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

INSERT INTO sequence_table (seq_name, next_val) VALUES ('my_sequence', 1);

DELIMITER $$
CREATE TRIGGER my_trigger
BEFORE INSERT ON my_table
FOR EACH ROW
BEGIN
    DECLARE next_val INT;
    UPDATE sequence_table SET next_val = LAST_INSERT_ID(next_val + 1) WHERE seq_name = 'my_sequence';
    SET NEW.id = LAST_INSERT_ID();
END$$
DELIMITER ;

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

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

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

-- 调用存储过程生成序列值
CALL next_seq_val('my_sequence', @next_val);
INSERT INTO my_table (id, name) VALUES (@next_val, 'John Doe');

DELIMITER $$
CREATE PROCEDURE next_seq_val(IN seq_name VARCHAR(50), OUT next_val INT)
BEGIN
    START TRANSACTION;
    SELECT next_val INTO @current_val FROM sequence_table WHERE seq_name = seq_name FOR UPDATE;
    UPDATE sequence_table SET next_val = @current_val + 1 WHERE seq_name = seq_name;
    SET next_val = @current_val + 1;
    COMMIT;
END$$
DELIMITER ;