mysql表自动生成数值型随机id

mysql表自动生成数值型随机id

原因：

1. 不想用id自动加一的，因为容易猜到
2. 不想牺牲性能

建表

CREATE TABLE session_id(auto_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, id BIGINT UNSIGNED NOT NULL UNIQUE KEY);

建触发器

DELIMITER $$

DROP TRIGGER `trigger_increase_session_id_insert`$$

CREATE
    TRIGGER `trigger_increase_session_id_insert` BEFORE INSERT ON `session_id`
    FOR EACH ROW BEGIN
	DECLARE rnd BIGINT UNSIGNED; 
	r: REPEAT
		SET rnd = CAST(FLOOR(RAND() * CAST(4294967296 AS UNSIGNED)) AS UNSIGNED) * CAST(FLOOR(RAND() * CAST(4294967295 AS UNSIGNED)) AS UNSIGNED) + CAST(FLOOR(RAND() * 4294967295) AS UNSIGNED);
	UNTIL NOT EXISTS( SELECT 1 FROM `session_id` WHERE id = rnd ) END REPEAT r; 	
	SET new.id = rnd;
    END;
$$

DELIMITER ;

测试

INSERT session_id VALUES();
SELECT id FROM session_id WHERE auto_id = LAST_INSERT_ID();