MySQL存储过程实战：用like条件批量删除表名的配置与验证

1. 为什么批量删表这件事值得单独写个存储过程MySQL 里删除表本身不复杂DROP TABLE一条语句就完事。真正麻烦的是「批量」和「模糊匹配」这两个词凑到一起的时候。比如你手上有个测试库里面躺着tmp_order_202401、tmp_order_202402、tmp_order_202403这种按月生成的临时表几十上百张手动一张张删既费时又容易漏。再比如某些业务会按租户或按批次建表表名统一带某个前缀清理时你只记得前缀记不住完整表名。这时候直觉会想到用LIKE去匹配表名然后循环删。但 MySQL 的DROP TABLE不支持WHERE table_name LIKE ...这种写法它只认具体的表名。所以必须先从information_schema.TABLES里把符合条件的表名查出来再拼成动态 SQL 逐条执行。这个「查出来再拼 SQL 再执行」的流程用存储过程封装是最合适的一次编写反复调用还能把安全校验固化进去。这篇面向的是 DBA 和后端开发者场景就是测试库或预发环境里按LIKE条件批量清理表。我会给出一个可复制的存储过程骨架重点讲清楚动态 SQL 拼接、游标遍历、以及那个很多人踩过的坑——为什么LIKE条件里直接写concat会失效。最后演示怎么在测试库验证删除结果以及万一删错了怎么回滚。顺带提一句这类脚本如果通过统一的 API 通道去触发凭证管理会省心很多。TaoToken 的 Key/API 通道可以把脚本调用凭证集中管起来官网在 https://taotoken.net/?utm_sourcetaotoken_aicg_blog_end 后面接入部分会说到。2. 前置准备库、权限与 TaoToken 通道2.1 环境与权限确认先确认你的 MySQL 版本5.7 和 8.0 在information_schema的字段上基本一致本文的写法两边都能跑。执行存储过程需要CREATE ROUTINE权限删除表需要对应库的DROP权限。如果你用的是受限账号先让管理员授权-- 查看当前用户权限 SHOW GRANTS FOR CURRENT_USER(); -- 授予创建存储过程和删除表的权限按需替换库名 GRANT CREATE ROUTINE, ALTER ROUTINE, EXECUTE ON testdb.* TO your_user%; GRANT DROP ON testdb.* TO your_user%; FLUSH PRIVILEGES;这里有个容易忽略的点存储过程默认的SQL SECURITY是DEFINER也就是以创建者的权限执行。如果你用高权限账号创建、低权限账号调用删除动作会以创建者身份跑这点在多人协作的测试库里要留意。2.2 用 TaoToken 统一管理脚本调用凭证批量删表这种操作通常不会让人天天手动敲命令更多是挂在某个运维脚本或内部平台里通过 API 触发。这时候凭证散落在各个脚本里就很危险。TaoToken 提供统一的 Key/API 通道可以把这类脚本的调用凭证集中管理避免明文写死在代码里。接入方式很简单先到控制台创建 API Key控制台入口https://taotoken.net/console?utm_sourcetaotoken_aicg_blog_endutm_contentconsoleutm_campaignrewriteAPI Keys 管理https://taotoken.net/api-keys?utm_sourcetaotoken_aicg_blog_endutm_contentapi-keysutm_campaignrewrite创建好 Key 之后脚本里通过环境变量注入而不是硬编码。API 基础地址是 https://taotoken.net/api 注意这个地址不带 UTM 参数直接用于请求即可。如果你后续要把删表脚本包装成一个带自然语言描述的运维助手可以走模型对话通道模型对话https://taotoken.net/model-chat?utm_sourcetaotoken_aicg_blog_endutm_contentmodel-chatutm_campaignrewrite长期跑编码类或 Agent 类任务的话Coding Plan 更合适Coding Planhttps://taotoken.net/coding-plan?utm_sourcetaotoken_aicg_blog_endutm_contentcoding-planutm_campaignrewrite接入文档在 https://taotoken.net/doc?utm_sourcetaotoken_aicg_blog_endutm_contentdocutm_campaignrewrite 里面有完整的鉴权和请求示例。下面进入正题先把存储过程写出来。3. 可复制的存储过程骨架与动态 SQL 拼接3.1 先看那个「LIKE 条件写 concat 失效」的坑原始写法里有一句被注释掉的concat(, table_prefix, %)作者发现把它作为游标定义条件时不能用。原因在于游标定义里的WHERE table_name LIKE table_prefix中table_prefix是一个变量MySQL 在解析游标时把它当作一个字符串值来比较而不是当作一个可以再拼接的表达式。你写LIKE concat(...)解析器在游标声明阶段并不执行这个函数导致条件不成立或报错。正确的做法是在调用存储过程时就把完整的LIKE模式传进来比如tmp_order_%而不是传前缀再在内部拼%。这样游标里的LIKE table_prefix直接拿到的就是带通配符的完整模式匹配正常。下面这个骨架就是这么处理的。3.2 完整存储过程骨架DELIMITER // DROP PROCEDURE IF EXISTS drop_table_like // CREATE PROCEDURE drop_table_like( IN p_schema VARCHAR(64), -- 目标库名 IN p_pattern VARCHAR(128), -- 完整 LIKE 模式如 tmp_order_% IN p_dry_run TINYINT -- 1只打印不删除0真正删除 ) BEGIN DECLARE v_tname VARCHAR(128) DEFAULT ; DECLARE v_sql VARCHAR(512) DEFAULT ; DECLARE v_done INT DEFAULT 0; DECLARE v_count INT DEFAULT 0; -- 游标查出所有匹配的表名 DECLARE cur_tnames CURSOR FOR SELECT table_name FROM information_schema.TABLES WHERE table_schema p_schema AND table_name LIKE p_pattern; DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done 1; -- 安全校验库名和模式不能为空 IF p_schema IS NULL OR p_schema THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT schema 不能为空; END IF; IF p_pattern IS NULL OR p_pattern THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT pattern 不能为空; END IF; OPEN cur_tnames; read_loop: LOOP FETCH cur_tnames INTO v_tname; IF v_done 1 THEN LEAVE read_loop; END IF; SET v_count v_count 1; IF p_dry_run 1 THEN -- 预演模式只输出将要删除的表名 SELECT CONCAT([DRY-RUN] 将删除: , p_schema, ., v_tname) AS msg; ELSE -- 真正删除动态 SQL SET v_sql CONCAT(DROP TABLE , p_schema, ., v_tname, ); SET stmt v_sql; PREPARE s FROM stmt; EXECUTE s; DEALLOCATE PREPARE s; SELECT CONCAT([DROPPED] , p_schema, ., v_tname) AS msg; END IF; END LOOP; CLOSE cur_tnames; SELECT CONCAT(共匹配 , v_count, 张表) AS summary; END // DELIMITER ;几个关键设计点值得说明。第一加了p_dry_run参数默认先跑预演确认匹配范围无误再真正删这是防误删的第一道闸。第二动态 SQL 里表名用反引号包起来避免表名含特殊字符时语法出错。第三SIGNAL SQLSTATE 45000用于参数为空时主动抛错比静默返回更安全。3.3 调用方式-- 第一步预演看看会匹配到哪些表 CALL drop_table_like(testdb, tmp_order_%, 1); -- 第二步确认无误后真正删除 CALL drop_table_like(testdb, tmp_order_%, 0);注意p_pattern传的是完整模式tmp_order_%不是前缀tmp_order。这就是绕开那个concat坑的关键。4. 在测试库验证删除结果与回滚方案4.1 造数据与验证先在测试库造几张临时表模拟真实场景CREATE DATABASE IF NOT EXISTS testdb; USE testdb; CREATE TABLE tmp_order_202401 (id INT PRIMARY KEY, amt DECIMAL(10,2)); CREATE TABLE tmp_order_202402 (id INT PRIMARY KEY, amt DECIMAL(10,2)); CREATE TABLE tmp_order_202403 (id INT PRIMARY KEY, amt DECIMAL(10,2)); CREATE TABLE keep_order_202401 (id INT PRIMARY KEY, amt DECIMAL(10,2));先跑预演确认只匹配到tmp_order_开头的三张表keep_order_不受影响CALL drop_table_like(testdb, tmp_order_%, 1);输出应该是三条[DRY-RUN]记录summary 显示「共匹配 3 张表」。确认后执行真正删除CALL drop_table_like(testdb, tmp_order_%, 0);再查一次information_schema验证结果SELECT table_name FROM information_schema.TABLES WHERE table_schema testdb AND table_name LIKE tmp_order_%;返回空集说明三张表已删除。同时确认keep_order_202401还在SELECT table_name FROM information_schema.TABLES WHERE table_schema testdb AND table_name LIKE keep_order_%;4.2 回滚方案DROP TABLE是不可逆的删了就是删了没有ROLLBACK能救回来。所以回滚方案必须在删除之前准备好而不是删除之后。实操上有两个层次第一层是删除前的备份。在跑真正删除之前先把要删的表结构和数据导出mysqldump -u root -p testdb tmp_order_202401 tmp_order_202402 tmp_order_202403 backup_tmp_order.sql如果表很多可以用脚本从information_schema查出表名列表再拼mysqldump参数。备份文件就是你的回滚依据删错了直接source backup_tmp_order.sql恢复。第二层是事务边界。存储过程里如果涉及多步操作可以用START TRANSACTION包起来但注意DROP TABLE属于 DDL在 MySQL 里 DDL 会隐式提交事务对它无效。所以别指望用事务回滚删表备份才是唯一可靠的回滚手段。如果你希望把「备份 删除」做成一个带审批的流程可以通过 TaoToken 的 API 通道触发把备份结果和删除日志统一记录。接入文档里有请求示例https://taotoken.net/doc?utm_sourcetaotoken_aicg_blog_endutm_contentdocutm_campaignrewrite5. 本篇常见错误排查5.1 游标里 LIKE 条件不生效现象预演时 summary 显示「共匹配 0 张表」但库里明明有匹配的表。原因几乎都是p_pattern没带%或者传成了前缀。检查你调用时传的是tmp_order_%还是tmp_order。游标里的LIKE需要完整模式不会自动补%。5.2 动态 SQL 报语法错误现象执行时报You have an error in your SQL syntax。多半是表名或库名含特殊字符而拼接时没加反引号。骨架里已经用包住了库名和表名如果你改写了拼接逻辑记得保留反引号。另外PREPARE的语句名不要和已有变量冲突用s或stmt这种短名即可。5.3 权限不足导致删除失败现象预演正常真正删除时报DROP command denied。这是调用账号缺少DROP权限。注意存储过程的SQL SECURITY DEFINER特性删除动作以创建者身份执行。如果创建者也没权限就会失败。用SHOW CREATE PROCEDURE drop_table_like查看 definer 是谁再决定是授权还是改SQL SECURITY INVOKER。5.4 误删了不该删的表现象keep_order_开头的表也被删了。检查p_pattern是不是写得太宽比如写成了%order_%。预演模式就是为这个准备的永远先跑p_dry_run 1把匹配列表看一遍再删。如果已经误删立刻用之前的mysqldump备份恢复。5.5 存储过程创建时报 delimiter 错误现象在客户端里直接粘贴整段CREATE PROCEDURE报错。这是因为存储过程体内有分号客户端会把第一个分号当成语句结束。必须先用DELIMITER //改分隔符创建完再DELIMITER ;改回来。如果你用的是某些 GUI 工具它可能自带分隔符处理按工具的说明操作。6. 把删表脚本接入统一通道到这里存储过程本身已经能跑了。实际运维中这类脚本往往需要被定时任务或内部平台调用凭证管理就成了问题。TaoToken 的统一 Key/API 通道可以把脚本调用凭证集中起来避免每个脚本各自维护一套密钥。具体做法是在控制台创建 API Key通过环境变量注入到执行脚本里脚本调用时带上这个 Key。API 基础地址是 https://taotoken.net/api 不带 UTM 参数。如果你要把删表操作包装成自然语言触发的运维助手走模型对话通道模型对话https://taotoken.net/model-chat?utm_sourcetaotoken_aicg_blog_endutm_contentmodel-chatutm_campaignrewrite如果是长期跑的编码或 Agent 任务Coding Plan 更省心Coding Planhttps://taotoken.net/coding-plan?utm_sourcetaotoken_aicg_blog_endutm_contentcoding-planutm_campaignrewriteKey 的创建和管理在API Keyshttps://taotoken.net/api-keys?utm_sourcetaotoken_aicg_blog_endutm_contentapi-keysutm_campaignrewrite完整的鉴权和请求格式参考接入文档接入文档https://taotoken.net/doc?utm_sourcetaotoken_aicg_blog_endutm_contentdocutm_campaignrewrite最后提醒一句实操经验预演模式一定要养成习惯每次改p_pattern都先跑p_dry_run 1。我见过太多人图省事直接删结果模式写宽了把正式表也带进去。备份加预演这两步做到位批量删表就没那么可怕了。