本文共 2278 字,大约阅读时间需要 7 分钟。
最近需要将项目从DB2数据库迁移到MySQL数据库,由于MySQL没有sequence,于是上网找了下MySQL的sequence实现,基本都是基于存储过程和函数实现的,下面第一、二两种实现是从网上看到的,三、四、五三种实现是自己的想法。第五种终极实现亲测可行,但是没有经过高并发检验。
CREATE TABLE `TBL_SEQUENCE_1` ( `sequence_name` varchar(64) NOT NULL COMMENT '序列名称' , `value` bigint NOT NULL DEFAULT 0 COMMENT '当前值' , PRIMARY KEY (`sequence_name`))ENGINE=InnoDBDEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci;
DELIMITER //CREATE FUNCTION NEXT_VAL(sequence_name varchar(64)) RETURNS bigint BEGIN declare current_val bigint; set current_val = 0; update t_sequence t set t.value = t.value + 1 where t.sequence_name = sequence_name; select t.value into current_val from t_sequence t where t.sequence_name = sequence_name; return current_val;END//DELIMITER ;
DELIMITER //CREATE FUNCTION NEXT_VAL(sequence_name varchar(64)) RETURNS bigint BEGIN declare current_val bigint; set current_val = 0; select t.value into current_val from t_sequence t where t.sequence_name = sequence_name for update; update t_sequence t set t.value = t.value + 1 where t.sequence_name = sequence_name; return current_val + 1;END//DELIMITER ;
DELIMITER //CREATE FUNCTION NEXT_VAL(sequence_name VARCHAR(64)) RETURNS BIGINT BEGIN DECLARE current_val BIGINT; DECLARE tryTimes INT; SET tryTimes = 0; WHILE tryTimes < 3 DO SELECT t.value INTO current_val FROM TBL_SEQUENCE_1 t WHERE t.sequence_name = sequence_name; UPDATE TBL_SEQUENCE_1 t SET t.value = t.value + 1 WHERE t.sequence_name = sequence_name AND t.value=current_val; IF ROW_COUNT() > 0 THEN RETURN current_val + 1; END IF; SET tryTimes = tryTimes + 1; END WHILE; RETURN 0;END//DELIMITER ;
--一个序列采用多条记录,每次获取序号的时候随机挑选一条记录进行更新(使用排它锁,多条记录能有效减少锁竞争)--比如使用10条记录,每条记录初始值1-10,增长步长为10
DELIMITER //CREATE FUNCTION NEXT_VAL(sequence_name VARCHAR(64)) RETURNS BIGINT BEGIN UPDATE TBL_SEQUENCE_1 t, (SELECT @current_val:=`value` FROM TBL_SEQUENCE_1 t2 WHERE t2.sequence_name=sequence_name) t3 SET t.value = t.value + 1 WHERE t.sequence_name =sequence_name AND @current_val=t.value; RETURN @current_val+1;END//DELIMITER ;
转载地址:http://jgqai.baihongyu.com/