最近遇到了一个用户写的存储过程的执行结果不符合预期的问题，因为我对 PL 不熟，所以排查问题折腾了半天，浪费了很多时间。这里简单描述一下问题，顺便记录一些 PL 相关的基础知识。

问题

首先，给大家出一道和存储过程相关的题目，大家可以借此机会，自测一下自己的 PL 水平如何。有个存储过程是这样写的：

CREATE DEFINER = root@% PROCEDURE `10th_log`.`proc_clear_index_daily_test`
()

READS SQL DATA BEGIN
    -- 定义变量
    DECLARE done INT DEFAULT FALSE;
    DECLARE tablename VARCHAR(32); -- 创建游标，并存储数据
    DECLARE indexname VARCHAR(32); -- 创建游标，并存储数据
    DECLARE cur CURSOR FOR SELECT TABLE_NAME,INDEX_NAME FROM `information_schema`.`STATISTICS` where TABLE_SCHEMA='10th_log' and TABLE_NAME like CONCAT('tbl_%',date_format(DATE_SUB(NOW(), INTERVAL 9 DAY), "%Y%m%d")) and INDEX_NAME in ('index_time','index_account');
    -- 游标中的内容执行完后将 done 设置为 true
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
    OPEN cur;
    WHILE (not done) DO
        FETCH cur INTO tablename, indexname;
        IF tablename is not null THEN
          set @sql_del_index = CONCAT('drop index ',indexname,' on ',tablename,';');
          -- 输出数据到控制台
          SELECT CONCAT('drop index ',indexname,' on ',tablename,';');
          select done;
          PREPARE sql_p FROM @sql_del_index;
          EXECUTE sql_p; 
          DEALLOCATE PREPARE sql_p;
        END IF;
    END WHILE;
    CLOSE cur;
END

上面这个存储过程看上去比较简单，像我这种不太懂 PL 的同学大概也能猜出他想删除一部分过期的索引（完全不了解存储过程的同学可以看下这个，完全不了解游标的同学可以看下这个）。

存储过程中的 `information_schema`.`STATISTICS` 是 MySQL 模式下和索引相关的字典视图，可以查出有哪些表上有哪些索引：

obclient [test]> create table tbl_1(c1 int, c2 int, index index_time(c1), index index_account(c2));
Query OK, 0 rows affected (0.643 sec)

obclient [test]> create table tbl_2(c1 int, c2 int, index index_time(c1), index index_account(c2));
Query OK, 0 rows affected (0.252 sec)

obclient [test]> SELECT TABLE_NAME,INDEX_NAME FROM `information_schema`.`STATISTICS` 
								 where TABLE_SCHEMA='test' and TABLE_NAME like 'tbl_%' and INDEX_NAME in ('index_time','index_account');
+------------+---------------+
| TABLE_NAME | INDEX_NAME    |
+------------+---------------+
| tbl_1      | index_time    |
| tbl_1      | index_account |
| tbl_2      | index_time    |
| tbl_2      | index_account |
+------------+---------------+
4 rows in set (0.495 sec)

   大家在往下看之前，强烈建议先思考下这两个问题：

   （1）这个存储过程这样写能不能达到用户删除过期索引的目的？

   （2）这个存储过程在执行过程中会不会报错？

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

解答

刚才为了避免大家不去思考，直接看到问题的答案，所以水了一下文章的篇幅。

现在回答一下上面这个问题，答案挺有意思：虽然上面这个存储过程的执行会报错，但是用户想删的索引都已经被删除了（实际已经达到了用户删索引的目的了）。

报错的信息是：ERROR 1091 (42000): Can't DROP 'index_account'; check that column/key exists。存储过程尝试删除不存在的索引，所以报错了。

不知道大家能不能看懂 DECLARE ... HANDLER 这个东西，反正我当时是没看太懂。这个语句是用于指定处理一个或多个异常条件的处理程序，如果出现某一条件，就会执行指定的语句，详见：官网文档。

所以 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true; 的含义就是说在存储过程的执行过程中，如果遇到游标到达数据集末尾，出现找不到更多行可用（NOT FOUND）的这个异常时，就会触发把 done 设置成 true。上面这个官网文档里说的 “数据末尾” 实际不是指结果集的最后一行，而是最后一行的再后面一行，类似于 C++ 里面的 iterator::end。

往上翻太麻烦了，再贴一下这个存储过程，顺便简化一下：

BEGIN
    -- 定义变量
    DECLARE done INT DEFAULT FALSE;
    DECLARE tablename VARCHAR(32); -- 创建游标，并存储数据
    DECLARE indexname VARCHAR(32); -- 创建游标，并存储数据
    DECLARE cur CURSOR FOR SELECT TABLE_NAME,INDEX_NAME FROM `information_schema`.`STATISTICS` where TABLE_SCHEMA='10th_log' and TABLE_NAME like CONCAT('tbl_%',date_format(DATE_SUB(NOW(), INTERVAL 9 DAY), "%Y%m%d")) and INDEX_NAME in ('index_time','index_account');
    -- 游标中的内容执行完后将 done 设置为 true
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
    OPEN cur;
    WHILE (not done) DO
        FETCH cur INTO tablename, indexname;
        SELECT CONCAT('drop index ',indexname,' on ',tablename,';');
        SELECT done;
        // 执行上面拼出来的这条 drop index 的 DDL，过程略
    END WHILE;
    CLOSE cur;
END

其中 cursor 指向的是 SELECT TABLE_NAME,INDEX_NAME FROM `information_schema`.`STATISTICS` 查出来的结果集，结果集如下：

+------------+---------------+
| TABLE_NAME | INDEX_NAME    |
+------------+---------------+
| tbl_1      | index_time    |
| tbl_1      | index_account |
| tbl_2      | index_time    |
| tbl_2      | index_account |
+------------+---------------+

当第四次执行 FETCH cur INTO tablename, indexname，cursor 会指向上面这个结果集的最后一行，因为还不是异常状态，所以 done 这时还是 false，所以 while 还会再走进去一次。

这次 while 走进去的时候，FETCH cur INTO tablename, indexname 就会报 warning 了，这个 warning 就是 NOT FOUND。所以这个时候 done 的值才会被触发改成 false（也就是 0），但是 tablename, indexname 并没有被更新，还会继续拼出 drop index 的完整 DDL，且 DDL 和上次拼出来的一样，都是 drop index index_account on tbl_2。

也就是说，第四次在 while 里 fetch cur 之后，done = 0，执行的 DDL 是 drop index index_account on tbl_2。第五次在 while 里 fetch cur 之后，fetch 到 iter_end 了，done = 1，执行的 DDL 还是 drop index index_account on tbl_2。这时候最后一个索引被删了两遍，所以会报错要删除的索引不存在，删除不了。

PL 的研发同学鸿瑞给出的修改方式是：每次 drop 完 index 后，把 tablename 和 indexname 置成空就好了。这样即使会第五次进 while 循环，也不会走动下面的 drop index 里，详见：

PL 相关内容学习笔记

   这里直接详见官网文档上的内容吧，内容还是非常详尽的~

马后炮儿

   问题在 PL 研发同学协助解决之后，又去问了下 chatgpt，感觉 gpt 的回答还挺神奇的。

   问上面这个存储过程含义是啥？gpt 的回答：

   问上面这个存储过程为什么会把最后一个索引删两次？gpt 的回答：

   AI 发展的太快了，最近让 chatgpt 去分析了几次代码中和 SQL 中存在的问题，它居然都能秒回答。感觉快要失业了……