虚谷数据库自增列序列调整方法

最新推荐文章于 2025-03-25 11:01:05 发布

Spyro丶

最新推荐文章于 2025-03-25 11:01:05 发布

阅读量623

点赞数 5

CC 4.0 BY-SA版权

分类专栏：虚谷数据库文章标签：数据库

本文链接：https://blog.csdn.net/qq_44528523/article/details/144187083

虚谷数据库专栏收录该内容

11 篇文章

订阅专栏

在虚谷数据库中建表时，指定自增列后，会自动生成一个对应的序列。自增列的初始值、步长、最大值、最小值等都可以通过调整其对应序列的属性从而实现对自增列属性的调整。

一、简单示例

根据系统表查询对应序列信息：

--建表(这里在TEST库，SYSDBA模式下创建)
CREATE TABLE test(id int IDENTITY(1,1));
--在系统库下使用SYSDBA查询系统表中该自增列对应序列的属性，用户需按照实际情况查询对应级别的系统表
SELECT d.db_id,s.schema_id,t.table_id,se.seq_id,se.seq_name,se.is_cycle,se.curr_val,se.max_val,se.min_val,se.step_val
FROM sys_databases d,sys_schemas s,sys_tables t,sys_columns c,sys_sequences se
WHERE d.db_id=s.db_id AND s.schema_id=t.schema_id AND t.table_id=c.table_id AND c.serial_id=se.seq_id
AND d.db_name='TEST'
AND s.schema_name='SYSDBA'
AND t.table_name='TEST';--建表时不加双引号的小写会自动转为大写，系统表中存的表名为大写

查询结果如下图：
在这里插入图片描述

*XUGU的系统表有 4 类，分别以 SYS、DBA、ALL、USER 这四个关键字开头。
SYS 开头的系统表只能在系统库中使用，这些系统表返回所有数据库对象信息，且只能由 SYSDBA 用户查询；
DBA 开头的系统表包含当前库下所有的对象信息；
ALL 开头的系统表包含当前库当前用户拥有的对象信息；
USER 开头的系统表包含当前用户的所有对象信息。

查询结果中的SEQ_NAME字段即为TEST表自增列对应的序列名，之后就可以通过调整该序列的属性以调整自增列id的属性。

在TEST库下使用SYSDBA执行示例：
1、改变序列当前值为101(下次自增插入为101)
ALTER SEQUENCE $SYS_SEQ_1049369 START WITH 101;
2、改变序列增长步长为5
ALTER SEQUENCE $SYS_SEQ_1049369 INCREMENT BY 5;

二、实际使用中遇到问题解决

目前由于兼容性稍有差异，存在部分自增迁移到虚谷数据库之后无法正常插入值的情况(普遍存在的异常都是违反唯一值约束)，这是由于使用的旧版迁移工具迁移或者跳值插入/更新后并未修改自增对应的序列初始值。下面是库级的自增序列校准过程：

create or replace procedure correct_seq_curr_val(is_crr boolean default false,db_ids int default current_db_id)
/*
使用说明：
该方法适用于没有业务执行的场景，由于是ddl修改，业务场景会出现加锁失败的问题。
创建用户为SYSDBA，使用用户也为SYSDBA，避免权限不足问题。
直接使用SYSDBA用户
1）执行 call correct_seq_curr_val 或 exec correct_seq_curr_val 会自动统计当前库的序列值
2）执行 call correct_seq_curr_val(true) 或 exec correct_seq_curr_val(true) 校正当前库的序列值
3) 登录SYSTEM库执行 call correct_seq_curr_val(false,0) 或 exec correct_seq_curr_val(false,0) 统计所有库序列值信息
一、参数介绍
1、is_crr 表示是否允许校正序列值当前值，默认为false，表示不校正进行统计信息，用于评估校正的数量与难度；true 则直接校正
2、db_id 表示指定校正的库id，默认为当前库；0 表示所有库，用于评估执行对象。
二、功能介绍
第一个参数含义：
false) 统计信息
1、序列值所属对象表；
2、序列值所属表的数据量 rows ，仅当第二个参数为 当前库id 时展示
3、序列值校正值（最大值加一） seq_correct_val ，仅当第二个参数为 当前库id 时展示
4、序列值当前值 curr_val ，仅当第二个参数为 0 时展示
true) 校正
alter sequence schema_name.seq_name start with 校正值;
第二个参数含义：
0) 统计需要调整的序列值对象，快速返回使用的序列值信息
!0) 会对所属表进行统计，较耗时间，使用了select count(*),max(XX) 方式统计，统计使用了 并行参数，默认为10
*/
AS
STARTTIME DATETIME;
ENDTIME DATETIME;
RUNTIME NUMERIC(10,4);
err_01 EXCEPTION;
err_02 EXCEPTION;
cro_num BIGINT;
sqls VARCHAR;
cnt BIGINT;
cro_val BIGINT;
new_val BIGINT;
pall BIGINT;
min_val BIGINT;
BEGIN
STARTTIME:=sysdate;
pall:=10;  -- 设置默认并发为10，可根据实际环境进行修改
cro_num:=0;
if (db_ids=0) THEN
	IF (is_crr is false) then
		-- identity 自增序列值 与 自定义sequence 作为默认值
		send_msg('统计 【所有库】 所有被使用的序列值信息');
		send_msg('---------------------------------');
		send_msg('|db_name  |schema_name  |table_name  |col_name  |seq_name  |curr_val  |');
		for i in (select db_name,schema_name,table_name,col_name,case when c.is_serial is true then sq.seq_name else substr(replace(def_val,'"."NEXTVAL"'),2) end seqs_name,curr_val,db_id,schema_id,seq_id,sq.is_sys from sys_columns c inner join sys_tables t on c.db_id=t.db_id and c.table_id=t.table_id inner join sys_schemas s on t.db_id=s.db_id and t.schema_id=s.schema_id inner join sys_databases d on c.db_id=d.db_id inner join sys_sequences sq on (c.serial_id=sq.seq_id or substr(replace(c.def_val,'"."NEXTVAL"'),2)=sq.seq_name) and c.db_id=sq.db_id ORDER BY t.db_id,t.schema_id,sq.seq_id) loop
		    cro_num:=cro_num+1;
            send_msg('|'||i.db_name||'  |'||i.schema_name||'  |'||i.table_name||'  |'||i.col_name||'  |'||i.seqs_name||'  |'||i.curr_val||'  |');
		end loop;
		send_msg('总共有'||cro_num||'个序列值被使用，需要进行校对');
	ELSE
		RAISE err_01;
	END IF;

elseif (db_ids=current_db_id) THEN
	IF (is_crr is false) then
		send_msg('统计 【'||CURRENT_DB||'库】 所有被使用的序列值信息');
		send_msg('---------------------------------');
		send_msg('|schema_name  |table_name  |col_name  |seq_name  |rows  |seq_correct_val  |');
		for i in (select db_name,schema_name,table_name,col_name,case when c.is_serial is true then sq.seq_name else substr(replace(def_val,'"."NEXTVAL"'),2) end seqs_name,db_id,schema_id,seq_id,sq.is_sys from dba_columns c inner join dba_tables t on c.db_id=t.db_id and c.table_id=t.table_id inner join dba_schemas s on t.db_id=s.db_id and t.schema_id=s.schema_id inner join dba_databases d on c.db_id=d.db_id inner join dba_sequences sq on (c.serial_id=sq.seq_id or substr(replace(c.def_val,'"."NEXTVAL"'),2)=sq.seq_name) and c.db_id=sq.db_id ORDER BY t.schema_id,sq.seq_id) loop
			sqls:='select count(*),nvl(max('||i.col_name||'),0)+1 from "'||i.schema_name||'"."'||i.table_name||'" parallel '||pall||';';
			execute immediate sqls returning into cnt,cro_val;
			cro_num:=cro_num+1;
			send_msg('|'||i.schema_name||'  |'||i.table_name||'  |'||i.col_name||'  |'||i.seqs_name||'  |'||cnt||'  |'||cro_val||'  |');
		end loop;
		send_msg('总共有'||cro_num||'个序列值被使用，需要进行校对');
	ELSEIF (is_crr is true) then
		send_msg('校对 【'||CURRENT_DB||'库】 所有被使用的序列值信息');
		send_msg('---------------------------------');
		send_msg('|schema_name  |table_name  |col_name  |seq_name  |rows  |seq_correct_val  |new_seq_val');
		for i in (select db_name,schema_name,table_name,col_name,case when c.is_serial is true then sq.seq_name else substr(replace(def_val,'"."NEXTVAL"'),2) end seqs_name,db_id,schema_id,seq_id,sq.is_sys,sq.min_val,sq.max_val from dba_columns c inner join dba_tables t on c.db_id=t.db_id and c.table_id=t.table_id inner join dba_schemas s on t.db_id=s.db_id and t.schema_id=s.schema_id inner join dba_databases d on c.db_id=d.db_id inner join dba_sequences sq on (c.serial_id=sq.seq_id or substr(replace(c.def_val,'"."NEXTVAL"'),2)=sq.seq_name) and c.db_id=sq.db_id ORDER BY t.schema_id,sq.seq_id ) loop
			sqls:='select count(*),nvl(max('||i.col_name||'),0)+1 from "'||i.schema_name||'"."'||i.table_name||'" parallel '||pall||';';
			execute immediate sqls returning into cnt,cro_val;
			min_val:=cro_val-1;
			if (i.min_val>cro_val or i.max_val<cro_val) then 
				if i.is_sys is true then
					execute immediate('alter sequence "SYSDBA"."'||i.seqs_name||'" start with '||cro_val||' minvalue '||min_val||' maxvalue 9223372036854775807;');
				ELSE
					execute immediate('alter sequence "'||i.schema_name||'"."'||i.seqs_name||'" start with '||cro_val||' minvalue '||min_val||' maxvalue 9223372036854775807;');
				end if;
			ELSE
				if i.is_sys is true then
					execute immediate('alter sequence "SYSDBA"."'||i.seqs_name||'" start with '||cro_val||';');
				ELSE
					execute immediate('alter sequence "'||i.schema_name||'"."'||i.seqs_name||'" start with '||cro_val||';');
				end if;
			end if;
		    SELECT curr_val INTO new_val FROM dba_sequences WHERE db_id=i.db_id AND schema_id=i.schema_id AND seq_id=i.seq_id;
			send_msg('|'||i.schema_name||'  |'||i.table_name||'  |'||i.col_name||'  |'||i.seqs_name||'  |'||cnt||'  |'||cro_val||'  |'|| new_val ||'  |');
			cro_num:=cro_num+1;
		end loop;
		send_msg('总共有'||cro_num||'个序列值完成校对');
	END IF;
ELSE
	RAISE err_02;
end if;

ENDTIME:=sysdate;
RUNTIME:=(unix_timestamp(ENDTIME)-unix_timestamp(STARTTIME))/1000;
send_msg('---------------------------------');
send_msg('开始执行时间：'||to_char(STARTTIME,'yyyy-mm-dd hh24:mi:sssss'));
send_msg('结束执行时间：'||to_char(ENDTIME,'yyyy-mm-dd hh24:mi:sssss'));
send_msg('总共执行时间：'||RUNTIME||' S');
send_msg('---------------------------------');

EXCEPTION WHEN err_01 THEN send_msg('无法对所有库的被使用的序列值进行校正，请进入需要校对的库，创建此存储过程，再执行 "call correct_seq_curr_val(true);"');
WHEN err_02 THEN send_msg('无法分析指定库，函数第二个参数仅支持0或当前库ID值，建议省略第二个参数来评估或校正序列值！');

end correct_seq_curr_val;

复制存储过程sql的并在需要调整自增的库中创建，然后执行exec correct_seq_curr_val(true)即可自动对当前库所有的自增序列进行调整(自动将序列当前值设置为对应自增列字段最大值+1)。

xugu自增对比oracle和mysql差异性参考：https://blog.csdn.net/m0_50013822/article/details/137827589?fromshare=blogdetail&sharetype=blogdetail&sharerId=137827589&sharerefer=PC&sharesource=qq_44528523&sharefrom=from_link