记一次自增主键id换为随机id

DROP TRIGGER IF EXISTS `insert_trigger`;

DELIMITER //
CREATE TRIGGER insert_trigger BEFORE INSERT ON user
FOR EACH ROW
BEGIN
DECLARE new_id INT;
DECLARE temp_id INT;
SET new_id = FLOOR(RAND() * 1000000000); -- 1000000000以内随机数结果为9位数的概率为0.999999999
-- 检查生成的ID是否已存在
SET temp_id = (SELECT COUNT(*) FROM user WHERE id = new_id);
WHILE temp_id > 0 DO
SET new_id = FLOOR(RAND() * 1000000000); -- 重新生成新的随机ID
SET temp_id = (SELECT COUNT(*) FROM user WHERE id = new_id);
END WHILE;

SET NEW.id = new_id; -- 重新生成新的随机ID
END //
DELIMITER ;