最近需要将项目从Oracle数据库迁移到MySQL数据库,由于MySQL没有sequence,如下几种方法可以实现mysql自定义的sequence
1、创建表用于存储当前序列值
CREATE TABLE `TBL_SEQUENCE_1` (
`sequence_name` varchar(64) NOT NULL COMMENT '序列名称' ,
`value` bigint NOT NULL DEFAULT 0 COMMENT '当前值' ,
PRIMARY KEY (`sequence_name`)
)
ENGINE=InnoDB
DEFAULT 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 ;
第三种实现,乐观锁,最多尝试三次(应用需要判断序号是否为0,为0表示获取序号失败)
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 ;