MySQL存储过程循环超时

Question

我有一个名为 maptable 的表，如下所示:

occ_id     name               position
0124       Mark Jones         2
           Hanna Smith        3 
           Frank Williams     4
0238       Henry Bassett      5

我正在尝试“填写”缺少的 occ_id 列(这意味着 Hanna Smith 和 Frank Williams 也应该有 occ_id 0124)。

我想出了以下程序来完成这项工作，但有大约 6000 条记录，每当我运行它时就会超时:

CREATE DEFINER=`root`@`localhost` PROCEDURE `mapjoinid_1`()
MODIFIES SQL DATA
BEGIN
        DECLARE n INT DEFAULT 0;
        DECLARE i INT DEFAULT 0;
        SELECT COUNT(*) FROM maptable WHERE occ_id = '' INTO n;
        SET i=0;
                WHILE i < n DO
                UPDATE maptable m JOIN maptable mt ON (m.position - 1) = mt.position SET m.occ_id = mt.occ_id;
                SET i = i + 1;
                END WHILE;
        End

是否有更有效的方法来循环或重复 update 语句，并且处理时间不会太长？

Answer 1

效率问题很可能来自于 while 循环内的 Join 声明。 MySQL 并不是特别擅长运行这些类型的函数，因此解决这个问题的一种方法是在 MySQL 更新中执行嵌套选择: MySQL - UPDATE query based on SELECT Query 。如果您不打算重复运行此过程(就像使用存储过程一样)，那么我认为这是最有效的操作方案。

超时也可能来自您的变量声明:仔细检查所有变量是否已递增并正确存储。