在MySQL中实现序列的效果

放晴的那天14

已于 2022-06-30 22:30:02 修改

阅读量9.3k

点赞数 1

文章标签： mysql 数据库 oracle

于 2022-06-30 22:22:34 首次发布

本文链接：https://blog.csdn.net/weixin_47372115/article/details/125547640

版权

本文介绍了如何在MySQL中模拟Oracle的序列功能，由于MySQL自身自增功能的限制，作者通过创建seq_table表并定义currval和nextval函数来实现序列。currval返回当前序列值，nextval则在事务中递增并返回新的序列值，确保并发环境下的唯一性。此方法适用于需要在MySQL中实现类似Oracle序列的场景。

摘要由CSDN通过智能技术生成

背景

最近有个项目的数据库从Oracle切换到了MySQL，这时要实现递增的序号就有点麻烦了。之前在Oracle中使用数据库自带的序列对象，可以很方便的实现这个功能（不过这里也有个坑，Oracle中的序列只是保证在不使用循环的情况下产生不重复的序号，并不保证严格递增）。而MySQL中虽然提供了字段的自增功能，但是使用起来有诸多限制，所以这里选择自己实现序列功能。

实现步骤

1.建立序列表

drop table if exists seq_table;
create table seq_table (       
seq_name        VARCHAR(50) NOT null comment '序列名称',
current_val     INT         NOT null default 0 comment '当前值',
increment_val   INT         NOT NULL DEFAULT 1 comment '增加值',
PRIMARY KEY (seq_name)
) ENGINE=InnoDB  CHARSET=utf8mb4 COLLATE = utf8mb4_general_ci;

2.实现currval

类似于Oracle中的currval，但是可以在当前的session中直接调用，如果查询一个不存在的序列会抛出一个异常：

drop function if exists currval;
delimiter $$
create function currval(v_seq_name varchar(50)) returns int
begin
	declare seq_value int;
	declare no_seq_found condition for sqlstate '40000';
	select current_val into seq_value
	from seq_table 
	where seq_name = v_seq_name;
	if seq_value is null then
		SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'No sequence found';
	end if;
	return seq_value;
end $$
delimiter ;

3.实现nextval

类似于Oracle中的nextval，注意在使用时需要确保两点：一是当前session的autocommit已经关闭，二是在service层调用时要保证它加入到一个事务当中，即如果使用spring框架的话使用默认的传播级别。否则可能会出现并发环境下取到同一个值的问题

drop function if exists nextval;
delimiter $$
create function nextval(v_seq_name varchar(50)) returns int
begin
	declare seq_value int;
	declare no_seq_found condition for sqlstate '40000';
	select current_val into seq_value
	from seq_table 
	where seq_name = v_seq_name for update;
	if seq_value is null then
		SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'No sequence found';
	end if;
	update seq_table set current_val = current_val + increment_val 
	where seq_name = v_seq_name;
	return seq_value;
end $$
delimiter ;