1. 为什么要写一个“自动加分区”的函数1.1 分区表维护的真实痛点先说个我自己的经历。前几年在一家电商公司做DBA核心订单表每天新增几百万行单表数据量很快就冲到了几十亿。当时把订单表改成了按天分区的Range分区表每天凌晨手动执行一遍ALTER TABLE ... ADD PARTITION加第二天的分区。最开始几个月还好后来某天晚上加班改需求凌晨忘了跑加分区的脚本第二天早高峰直接炸了——大量SQL扫描全表数据库CPU飙到100%核心接口大面积超时。那次事故之后我做了两件事第一写了一个自动加分区的函数配合定时任务每天凌晨自动把未来N天的分区都补上第二把“提前补未来7天分区”作为硬性规范写进运维手册。今天就把这个函数的设计思路、完整代码和踩坑记录整理出来希望对正在被分区表维护折磨的同学有点帮助。所谓“自动添加分区表”的函数本质上就是把ALTER TABLE ... ADD PARTITION这事儿封装成一个可重复调用的存储函数由它根据当前日期自动计算需要创建的分区名与边界值一次调用就可以把未来N天的分区全部创建好。适合谁用凡是线上有Range分区表、需要按天或按月加分区、又不想靠人肉定时记这件事的团队都可以直接参考。1.2 分区表的基本背景与选型在深入函数之前先简单回顾一下MySQL分区表的基本概念。MySQL从5.1开始支持分区常见分区类型有四种分区类型适用场景说明RANGE分区按时间、按ID区间最常用适合订单、日志类数据MySQL 8.0还支持自动裁剪分区LIST分区按枚举值适合按地区、按状态分类的数据HASH分区均匀散列数据适合没有明显范围概念、只想分散IO的字段KEY分区类似HASH但基于MySQL内部哈希函数使用场景相对较少自动加分区这个需求主要发生在RANGE分区上尤其是按时间分区的表。为什么因为HASH和KEY分区是预先定义固定数量的分区不存在“加新分区”的概念LIST分区一般也是静态枚举很少动态变化只有RANGE分区的时间边界是不断往前滚动的今天用完了明天还得加这就是我们要解决的问题。这里有一个容易误解的点凌晨加分区和提前加分区性能影响完全不同。如果你在半夜低峰期执行一次ADD PARTITION对线上影响很小但如果因为漏执行导致业务高峰时新数据写不进去严格来说MySQL不会写不进去而是会插入到MAXVALUE分区或者直接报错取决于你的分区定义那才是灾难。所以更稳妥的做法是每天都把未来N天的分区提前建好让“加分区”这个动作永远跑在业务前面而不是和业务赛跑。2. 自动加分区函数的设计思路2.1 为什么选择函数而不是存储过程先说个很多人都问过的问题为什么用函数FUNCTION而不是存储过程PROCEDURE两者都能执行动态SQL都能被定时任务调用区别在于返回值。存储过程的典型特点是“不需要返回值”它更适合做一套完整的事务流程更新数据、记录日志、输出结果集。而函数的特点是“必须返回一个值”这恰好适合我们这类巡检型任务——执行完之后告诉我“这次创建了哪几个分区、用时多久、有没有出问题”这个结果可以方便地记录到日志表或者监控系统里。另一个实际考量是调用方式。存储过程需要用CALL procedure_name()调用函数可以直接用SELECT function_name()调用后者在对接定时任务、写监控脚本时更自然。我见过不少人把这种逻辑写成存储过程也没问题但个人经验是函数的形式更轻、更直观、更好排查。2.2 函数设计需要满足的五个核心要求在设计这个函数时我给自己定了几个硬性要求这些要求直接决定了代码结构幂等性——同一个函数同一天多次执行结果一致。如果分区已经存在要跳过而非报错。前瞻性——不只要加明天的分区而是要一次性补未来N天的分区参数可调。自适应边界——自动识别当前已存在的最大分区边界在此基础上无缝续接杜绝分区重叠。可观测性——函数返回一个可读的结果串告诉你创建了哪些分区出错时有清晰的错误信息。通用性——不写死在某一个表上表名和分区的时间字段作为参数传入。前两条尤其重要。幂等性保证了就算定时任务重复执行也不会出问题前瞻性让你不用天天惦记着去维护每周跑一次或者每天跑一次都行。第三条“自适应边界”听起来玄乎其实核心就是先查information_schema.PARTITIONS表找到该表当前最大的分区名再从这个边界开始往后创建。这里顺便说一下MySQL 8.0的一个特性它原生支持RANGE分区的自动扩展在定义分区时可以省略上界MySQL会自动管理。但实际使用中仍有不少限制比如只支持按数值型连续区间、不支持带业务含义的命名规则等。所以对很多存量系统来说自己写函数仍然是更可控的方案。后面讲的实现以通用写法为主你在MySQL 5.7和8.0里都能跑通。3. 函数实现与完整代码解析3.1 思路前置DATEDIFF定位法具体实现前先把我最核心的一个设计思想讲透。假设你的分区是按天切的分区名的规则是p20260328分区边界是2026-03-29。我们要判断“该不该加分区”最直接的办法是数天数。以当前日期为基准如果一张表的“下一分区”还没建那它最大的分区边界距今的天数一定小于你预设的“提前量”。比如你希望永远提前7天建好分区那么当最大的分区边界与今天之间的天数不足7时就需要补建。补建的时候还需要注意分区边界值并不是从今天开始算的而是从当前最大分区边界开始算的否则会出现分区边界跳变或重叠。用DATEDIFF有两个好处第一判断逻辑清晰一眼能看懂第二边界计算直接用日期加减天然符合人类的“明天、后天、大后天”思维。缺点只有一个——如果你的分区表不是按天而是按小时、按周、按月这个函数的逻辑需要相应调整。我后面会给出按天版本的完整实现并按月版本的使用提示。3.2 完整代码与逐段注释下面给出一个可以直接部署的版本。这里以MySQL 5.7/8.0通用的写法为例。DELIMITER $$ DROP FUNCTION IF EXISTS auto_add_partition$$ CREATE FUNCTION auto_add_partition( p_schema_name VARCHAR(64), -- 库名 p_table_name VARCHAR(64), -- 表名 p_advance_days INT -- 提前建分区的天数建议7~30 ) RETURNS VARCHAR(2000) READS SQL DATA DETERMINISTIC BEGIN DECLARE v_max_partition_name VARCHAR(64) DEFAULT ; DECLARE v_max_partition_value VARCHAR(64) DEFAULT ; DECLARE v_current_date DATE DEFAULT CURDATE(); DECLARE v_partition_name VARCHAR(64); DECLARE v_partition_value VARCHAR(64); DECLARE v_sql_text VARCHAR(3000); DECLARE v_msg VARCHAR(2000) DEFAULT ; DECLARE v_days INT DEFAULT 0; DECLARE v_count INT DEFAULT 0; -- 1. 找到当前表最大的分区名与分区边界 SELECT PARTITION_NAME, PARTITION_DESCRIPTION INTO v_max_partition_name, v_max_partition_value FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA p_schema_name AND TABLE_NAME p_table_name AND PARTITION_NAME IS NOT NULL ORDER BY PARTITION_ORDINAL_POSITION DESC LIMIT 1; -- 2. 如果表还没有任何分区直接返回提示 IF v_max_partition_name IS NULL THEN RETURN TABLE_HAS_NO_PARTITION; END IF;这段代码做了三件事从元数据表里取最大的分区名和边界值判空准备循环。这里有个细节很多人会忽略——PARTITION_DESCRIPTION在RANGE分区里是分区的上界值比如分区p20260328的描述是20260329表示这个分区存放的是[20260328, 20260329)之间的数据。理解了这一点后面动态计算新分区边界就不会写错。继续往下看核心循环部分-- 3. 从当前最大分区边界开始逐天往后补分区 WHILE v_days p_advance_days DO SET v_days v_days 1; -- 计算新分区的边界日期注意是在现有最大边界上加天数 SET v_partition_value DATE_FORMAT( DATE_ADD(v_max_partition_value, INTERVAL 1 DAY), %Y%m%d ); SET v_partition_name CONCAT(p, v_partition_value); -- 4. 幂等性检查分区已存在则跳过 SELECT COUNT(1) INTO v_count FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA p_schema_name AND TABLE_NAME p_table_name AND PARTITION_NAME v_partition_name; IF v_count 0 THEN SET v_sql_text CONCAT( ALTER TABLE , p_schema_name, ., p_table_name, ADD PARTITION (PARTITION , v_partition_name, VALUES LESS THAN (, v_partition_value, )) ); SET sql v_sql_text; PREPARE stmt FROM sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET v_msg CONCAT(v_msg, , , v_partition_name); END IF; -- 更新下一次循环的基准值 SET v_max_partition_value v_partition_value; END WHILE; -- 5. 给返回消息去掉首字母多余的逗号 IF LENGTH(v_msg) 0 THEN SET v_msg SUBSTRING(v_msg, 3); END IF; RETURN CONCAT(CREATED: , IF(v_msg , NONE, v_msg)); END$$ DELIMITER ;核心逻辑就这么多。v_max_partition_value一开始从元数据表取出来是字符串比如20260329在DATE_ADD(v_max_partition_value, INTERVAL 1 DAY)这一步MySQL会自动把字符串转成日期类型所以可以直接参与日期运算。这个隐式转换在MySQL里是可靠的但为了可读性你也可以显式加一层STR_TO_DATE(v_max_partition_value, %Y%m%d)。我在生产环境里两种写法都验证过效果一样建议喜欢严谨风格的同学加一层显式转换。3.3 关键设计点详解为什么用PARTITION_DESCRIPTION做基准这一小节需要重点理解。很多初学者会犯一个错误——用当前日期作为基准去加分区比如今天3月28日就直接加p20260329。如果这张表的最大分区边界恰好是3月20日中间3月21日到3月28日之间就出现断档了新数据根本写不进去。反过来如果这张表的最大分区边界已经到4月10日了说明某个同事手动多加了几个分区你再从今天开始加新分区和已有分区就重叠了MySQL直接报错。所以正确的基准永远是“表当前的实际情况”而不是“今天是什么日子”。ORDER BY PARTITION_ORDINAL_POSITION DESC LIMIT 1这个查询拿到的就是表当前真实的“分区分界线”从这条线往后推才是安全的。这里要补充一个容易踩的坑在MySQL 8.0里information_schema.PARTITIONS表中低版本字段的含义没变但查询效率会随着分区数量增加略微变慢。对绝大多数分区表来说几十个分区的元数据查询耗时在毫秒级不用太在意。但如果你的表有几千个分区真有人这么干建议把元数据查询放到存储过程中单独处理减少频率。通常分区数控制在200个以内是合理的按天分区大概就是半年到一年的量级。4. 常见问题与排查技巧实录4.1 问题一DATEDIFF函数在MySQL 5.7和8.0中的差异严格说DATEDIFF在两个版本里行为一致但有个细节容易引发疑惑DATEDIFF只比较日期部分忽略时间部分。如果你的分区边界带时间比如2026-03-29 00:00:00DATEDIFF返回的结果和带不带时间是相同的因为时间部分不影响日期差值。但如果你拿它去做精准的“还有多少小时”判断就会踩坑——在自动加分区的场景里我们只关心“差几天”所以DATEDIFF完全够用。我见过有人在函数里写TIMESTAMPDIFF(HOUR, NOW(), v_max_partition_value)想精细控制结果绕了一大圈既复杂又容易出错。经验是按天分区就用DATEDIFF按小时分区才需要考虑TIMESTAMPDIFF。4.2 问题二函数执行权限与二进制日志存储函数在MySQL里有个比较麻烦的权限设置如果你开启了二进制日志binlogMySQL默认要求函数声明为DETERMINISTIC或者指定NO SQL、READS SQL DATA之一否则会拒绝创建。上面代码里两种都写了就是为了兼容这种环境。另外如果你的数据库从库是从binlog同步的函数内部用PREPARE执行动态SQL在主库上执行没问题从库回放时一般也能正常执行。但要注意函数内部如果依赖了主库的当前时间CURDATE主从之间有时延的话从库回放时可能计算出不同的分区边界。严格的生产环境建议让主从的时间保持一致或者干脆只读库不做这类写操作。实际排查中还有一个更隐蔽的问题MySQL 8.0默认日志格式是ROW部分动态SQL在从库回放时的行为与STATEMENT格式不同。我自己遇到过一次从库回放报错原因是分区表上同时存在外键约束加分区时会触发相关表的检查。解决方案是确保分区表不要加外键尤其不要和MyISAM等非事务表的查询混在一起。4.3 问题三分区达到上限怎么办一个很常见但容易被忽略的问题——单表的分区数量是有上限的。MySQL 5.7的分区上限是8192个8.0也是同样的限制。按天分区的话8192个分区大约相当于22年好像很远但按月分区同时保留历史数据的话几年就能冲到几百上千个分区。我在生产环境就遇到过一张按月分区的归档表分区数量超过了2000个虽然没到8192上限但元数据查询和维护已经开始变慢。后面做了两个优化一是把超过归档时间的历史分区合并或直接删除用DROP PARTITION二是把提前量从30天改到7天。这两个优化都能让分区数量和元数据查询成本保持在一个健康范围。这里也顺便回答一个热搜里很常见的问题——“手机系统分区删了要还原分区表吗”。这和MySQL分区虽然完全是两回事但思路是一样的分区表的分区元数据就是整个存储结构的“地图”删掉了地图数据还在但无从定位MySQL里如果你执行了ALTER TABLE ... REMOVE PARTITIONING分区信息也没了数据会全部并入主表逻辑上表还在但物理上失去了分区的隔离和管理能力。所以做任何分区操作前备份元数据是保命的第一步。4.4 问题四函数调用返回慢或超时如果你的auto_add_partition函数一次要加几十个分区每个ALTER TABLE都会触发一次在线DDL。MySQL 5.6以后支持在线DDL大部分情况下不会阻塞表的读写但分区操作是否真的“在线”取决于存储引擎和具体操作类型。在InnoDB下执行ADD PARTITION通常是在线操作不过做之前还是建议看一眼performance_schema里的锁等待情况。典型超时场景是这样的一张表同时被多个连接写入某个连接执行ALTER TABLE等待元数据锁后续新连接全部排队。函数执行慢不是函数本身的问题而是DDL在等待锁。遇到这种问题我的排查顺序是先看SHOW PROCESSLIST确认是否有卡住的DDL再看information_schema.INNODB_TRX是否有长事务最后才是检查函数逻辑。函数写得再快也挡不住DDL自身对锁的依赖。4.5 问题五JOIN多张分区表时加分区失败高并发系统里你可能同时维护多张分区表比如订单表、订单明细表、日志表。把它们都串进一个函数里批量加分区遇到最常见的问题是其中一张表已经被手动加过分区了或者某张表的表结构异常导致整个事务回滚。我的建议是一个函数调用只处理一张表多张表通过多次调用来完成。你可以写一个管理日志表记录每次调用的返回结果让定时任务的每次调用都是独立的、可重试的。这比把多张表硬塞进一个函数里要稳得多——一次失败不会影响其他表排查问题也有据可查。5. 运维实战与扩展用法5.1 结合事件调度器实现真正的“自动”函数本身只是个工具要让它“自动”还得配合定时调度。MySQL自带的事件调度器Event Scheduler完全可以胜任这个任务。开启方式如下SET GLOBAL event_scheduler ON; CREATE EVENT ev_auto_add_partition_order ON SCHEDULE EVERY 1 DAY STARTS 2026-03-29 03:00:00 ON COMPLETION PRESERVE ENABLE DO BEGIN DECLARE v_result VARCHAR(2000); SET v_result auto_add_partition(mydb, t_order, 7); INSERT INTO partition_log(table_name, result, create_time) VALUES (t_order, v_result, NOW()); END;这个事件每天凌晨3点执行一次每次把未来7天的分区补好并把结果写入日志表。这样只要事件调度器是开启的你就不用再靠人工盯了。注意几个细节凌晨3点是个比较合适的时间尽量避开业务高峰和备份窗口。ON COMPLETION PRESERVE表示事件执行完不自动删除否则只跑一次就没了。日志表建议定期清理别让日志表也变成一个大表——这多少有点讽刺。如果你的团队已经有成熟的调度平台XXL-Job、Airflow、云厂商的定时任务等也可以直接用它去调用SELECT auto_add_partition(...)效果是一样的而且调度平台通常带失败报警比事件调度器更直观。5.2 扩展按月分区的自动维护按天分区的函数改造成按月分区非常简单核心就是把日期的递增粒度从 “天” 改成 “月”。逻辑上你只需要注意两点第一分区名建议用p202603这种格式而不是混入天数第二INTERVAL从1 DAY改成1 MONTHDATE_FORMAT的格式改成%Y%m。下面是一段按月版本的核心循环示意完整函数可以照着改SET v_partition_value DATE_FORMAT( DATE_ADD(v_max_partition_value, INTERVAL 1 MONTH), %Y%m ); SET v_partition_name CONCAT(p, v_partition_value);同样需要留意的是PARTITION_DESCRIPTION存储的边界值在不同版本下可能有细微差异建议在测试库上先跑一次验证确保日期格式与你的分区命名规则一致。5.3 性能影响与锁机制观察最后聊一下生产环境中的性能观察。加分区操作本质上是在表的元数据上做变更对InnoDB表来说ADD PARTITION不算真正的全表重建但也不是完全零开销。我实测过一次给一张2亿行的订单表加分区整个过程大约5秒期间表的读写没有被完全阻塞但有一个短暂的元数据锁窗口DDL提交瞬间对正在执行的DML有一定影响。所以即便函数逻辑正确、调度合理也建议在低峰期跑。如果你担心DDL影响可以在调用函数的调度任务里加上锁等待超时和重试机制。简单做法是用GET_LOCK做进程间互斥避免同一时刻有多个会话在加同一个表的分区SELECT GET_LOCK(lock_add_partition_t_order, 10); -- 调用自动加分区函数 SELECT RELEASE_LOCK(lock_add_partition_t_order);这个技巧在多个定时任务或人工误触发同时存在时特别好用能保证同一张表的分区操作是串行的不会互相打架。6. 实操心得与最终建议写到这里把几个记忆深刻的体会分享给读者。第一自动化的核心是“提前量”不是“及时性”。我最初设计这个函数时只提前1天结果遇到节假日、跨月、航班延误这些场景还是偶尔会出问题。改成提前7天后两年的生产运行里再没出过漏分区的故障。提前量本质上是用一点冗余换巨大的容错空间这个投入非常划算。第二永远让函数的结果可见。函数每次执行返回的结果建议都写进日志表哪怕只是“CREATED: NONE”这样的无操作信息。几个月后排查问题时这些日志就是最好的证据链能帮你快速分辨是调度没触发、函数没执行还是分区本来就不需要加。第三测试环境先跑通生产再上。这个函数虽然不大但涉及动态SQL、元数据查询、日期计算任何一个字符串拼接出错都可能导致生产事故。建议先在测试库上建一张不重要的表把分区边界调整到接近临界值然后反复调用函数验证幂等性和边界计算的正确性确认无误后再部署到生产。写在最后这个函数我前后迭代了三个版本第一版只处理单表写死逻辑第二版加了参数化支持第三版才完善了幂等检查、返回值日志和按月扩展。经历过的教训是自动化维护工具本身也要有“自愈”能力——如果你写出来的函数自己都需要人工盯着才能保证不出错那它就没有真正解决你的问题。希望这套思路和代码能帮你在分区表的维护工作上彻底松一口气。
