mysql 生成指定前缀序列

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

DELIMITER $$

CREATE TRIGGER generate_id_before_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    SET NEW.id = (SELECT IFNULL(MAX(id), 0) + 1 FROM users);
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE generate_id(INOUT new_id INT)
BEGIN
    SET new_id = (SELECT IFNULL(MAX(id), 0) + 1 FROM users);
END$$

DELIMITER ;

START TRANSACTION;
SELECT MAX(id) + 1 INTO @new_id FROM users FOR UPDATE;
INSERT INTO users (id, name) VALUES (@new_id, 'John Doe');
COMMIT;