mysql 查询增加序列号

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

DELIMITER $$

CREATE TRIGGER generate_sequence_number
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_sequence_number(INOUT sequence_number INT)
BEGIN
    SET sequence_number = (SELECT IFNULL(MAX(id), 0) + 1 FROM users);
END$$

DELIMITER ;

-- 使用锁
BEGIN;
SELECT MAX(id) + 1 INTO @sequence_number FROM users FOR UPDATE;
INSERT INTO users (id, name) VALUES (@sequence_number, 'John Doe');
COMMIT;

-- 逻辑删除
ALTER TABLE users ADD COLUMN is_deleted BOOLEAN DEFAULT FALSE;

-- 手动调整序列号
UPDATE users SET id = id - 1 WHERE id > 100 AND is_deleted = TRUE;