Kingbase 数据库批量清库命令【重置序列】

Kingbase 数据库批量清库命令PL/SQL脚本版安全高效本文整理Kingbase数据库批量清库实用脚本采用PL/SQL存储过程语法支持批量清空指定业务表、自动重置主键序列避免手动逐表删除的繁琐操作同时规避误删风险适配Kingbase各主流版本V8/V9兼顾安全性与高效性。核心优势无需逐表执行DELETE/TRUNCATE命令批量配置表名即可一键执行自动重置主键自增序列适配seq、id_seq等常见序列命名规则清库后表结构保持不变无需手动重建序列大幅提升清库效率。一、核心清库脚本通用版脚本说明通过声明表名数组批量对指定表执行「清空数据重置主键序列」操作支持任意数量业务表可根据实际需求添加/删除表名采用TRUNCATE TABLE ... CASCADE可自动删除关联表的引用数据若有外键关联无需手动删除关联表。DO$$DECLARE-- 在这里批量添加需要清空的表名用逗号分隔替换为自身业务表名tablestext[]:ARRAY[-- 业务模块1基础配置表biz_base_config,biz_base_dict,biz_base_param,biz_base_region,-- 业务模块2业务主表及关联表biz_business_main,biz_business_detail,biz_business_rel,biz_business_log,-- 业务模块3数据统计相关表biz_statistics_data,biz_statistics_report,biz_statistics_summary,-- 业务模块4通知消息相关表biz_notice_info,biz_message_record,biz_notify_receive,-- 扩展表按需添加/删除biz_extend_data1,biz_extend_data2];tbltext;-- 循环变量存储当前遍历的表名BEGIN-- 循环遍历所有表名逐个执行清空和序列重置操作FOREACH tblINARRAYtablesLOOP-- 1. 清空表数据CASCADE级联删除关联表数据无关联可省略EXECUTETRUNCATE TABLE ||quote_ident(tbl)|| CASCADE;;-- 2. 自动找到主键序列并重置为1万能通用适配seq、id_seq等命名规则-- 前提表主键字段名为「id」若主键名不同需替换引号内的「id」EXECUTESELECT pg_catalog.setval(pg_get_serial_sequence(||quote_literal(tbl)||, id), 1);;-- 打印执行日志便于核对执行结果RAISE NOTICE✅ 执行成功表【%】已清空数据并重置主键序列为1,tbl;ENDLOOP;RAISE NOTICE 所有指定表清库操作完成共处理 % 张表,array_length(tables,1);END$$;二、脚本核心功能说明1. 批量清空表数据使用TRUNCATE TABLE替代DELETE优势如下执行速度更快TRUNCATE直接清空表数据不记录事务日志适合大批量数据清库效率远高于DELETE级联删除添加CASCADE关键字若当前表与其他表有外键关联会自动清空关联表的引用数据无需手动处理外键依赖表结构保留仅清空数据表结构、字段属性、索引、主键约束均保持不变无需重建表。2. 自动重置主键序列脚本中pg_get_serial_sequence(tbl, id)会自动获取表主键id字段对应的序列名无论序列命名为「表名_seq」「表名_id_seq」均能自动匹配setval(..., 1)将序列起始值重置为1避免清库后主键自增序列从原有最大值继续增长。注意若表主键字段名不是「id」如「主键名」「表名_id」需将脚本中id替换为实际主键字段名如biz_id。3. 执行日志反馈通过RAISE NOTICE打印每一张表的执行结果以及最终处理的表总数执行后可清晰核对是否所有表都已成功清库便于排查异常。三、使用步骤直接上手操作登录Kingbase数据库通过数据库客户端如DBeaver、Navicat或命令行登录目标数据库需拥有「TRUNCATE、ALTER SEQUENCE」权限修改脚本表名将脚本中tables text[] : ARRAY[...]内的示例表名替换为需要清库的实际业务表名用逗号分隔可任意添加/删除表名核对主键字段确认所有表的主键字段均为「id」若有例外修改脚本中id为实际主键名执行脚本将修改后的完整脚本复制到数据库客户端执行即可执行过程中会打印每一步的执行日志验证结果执行完成后可通过SELECT * FROM 表名;核对表数据是否清空通过SELECT nextval(序列名);验证主键序列是否重置为1。四、关键注意事项权限要求执行脚本的数据库用户需拥有「TRUNCATE TABLE」清空表和「ALTER SEQUENCE」重置序列权限否则会执行失败数据备份清库前务必全量备份数据库或相关业务表TRUNCATE操作不可逆一旦执行数据无法恢复外键处理若表存在外键关联建议使用TRUNCATE ... CASCADE避免因外键约束导致清库失败若无需级联删除可删除CASCADE关键字先手动删除关联表数据序列适配脚本仅适配「主键为自增序列」的表若表主键为非序列类型如UUID可删除脚本中「重置主键序列」的相关执行语句版本适配支持Kingbase V8、V9等主流版本若版本较低可删除pg_catalog.前缀简化为setval(pg_get_serial_sequence(...), 1);批量控制若需清库的表数量较多超过50张可分批次执行脚本拆分表名数组避免一次性执行导致数据库卡顿。五、常见问题排查问题1执行脚本提示「权限不足」解决方案给当前用户授予对应权限执行以下SQL需管理员权限-- 授予当前用户TRUNCATE和ALTER SEQUENCE权限替换username为实际用户名GRANTTRUNCATEANYTABLETOusername;GRANTALTERANYSEQUENCETOusername;问题2提示「找不到序列」或「序列不存在」原因表主键不是自增序列或主键字段名不是「id」或序列命名不符合Kingbase默认规则。解决方案若表无自增序列删除脚本中「重置主键序列」的EXECUTE语句若主键字段名不是「id」替换脚本中id为实际主键字段名若序列命名特殊可手动指定序列名替换「重置序列」语句为EXECUTE SELECT setval(序列名, 1);;。问题3因外键约束清库执行失败解决方案在TRUNCATE语句后添加CASCADE关键字脚本默认已添加或先手动删除外键关联表的数据再执行脚本。六、扩展用法1. 跳过指定表的序列重置若部分表无需重置主键序列可添加条件判断示例-- 循环内添加判断跳过biz_base_dict表的序列重置FOREACH tblINARRAYtablesLOOPEXECUTETRUNCATE TABLE ||quote_ident(tbl)|| CASCADE;;-- 跳过指定表IFtbl!biz_base_dictTHENEXECUTESELECT pg_catalog.setval(pg_get_serial_sequence(||quote_literal(tbl)||, id), 1);;ENDIF;RAISE NOTICE✅ 执行成功表【%】已清空数据,tbl;ENDLOOP;2. 仅清空数据不重置序列删除脚本中「重置主键序列」的EXECUTE语句仅保留TRUNCATE操作适合无需重置序列的场景。七、总结本脚本专为Kingbase数据库设计解决了手动逐表清库效率低、序列重置繁琐、外键处理复杂等问题通过批量配置表名即可实现一键清库序列重置兼顾安全性与高效性。适用于开发环境、测试环境的定期清库也可用于生产环境的业务数据批量清理执行前务必做好备份。使用时只需替换表名数组、核对主键字段即可快速上手大幅减少数据库清库的操作成本和出错概率。