mysql查询带序列号

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

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com');

SELECT * FROM users;

DELIMITER //

CREATE FUNCTION generate_sequence()
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE seq INT;
    SET seq = (SELECT IFNULL(MAX(id), 0) + 1 FROM users);
    RETURN seq;
END //

DELIMITER ;

CREATE TABLE users_custom_seq (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

INSERT INTO users_custom_seq (id, name, email) VALUES (generate_sequence(), 'Alice', 'alice@example.com');
INSERT INTO users_custom_seq (id, name, email) VALUES (generate_sequence(), 'Bob', 'bob@example.com');

SELECT * FROM users_custom_seq;

ALTER TABLE table_name AUTO_INCREMENT = 1;

START TRANSACTION;
SELECT @seq := IFNULL(MAX(id), 0) + 1 FROM users FOR UPDATE;
INSERT INTO users (id, name, email) VALUES (@seq, 'Alice', 'alice@example.com');
COMMIT;

DELIMITER //

CREATE PROCEDURE insert_user(IN name VARCHAR(100), IN email VARCHAR(100))
BEGIN
    DECLARE seq INT;
    START TRANSACTION;
    SELECT IFNULL(MAX(id), 0) + 1 INTO seq FROM users FOR UPDATE;
    INSERT INTO users (id, name, email) VALUES (seq, name, email);
    COMMIT;
END //

DELIMITER ;

CALL insert_user('Alice', 'alice@example.com');