时间戳

首页 > 解决方案 > 过程执行失败 1221 - UPDATE 和 LIMIT 使用不正确

mysql - 过程执行失败 1221 - UPDATE 和 LIMIT 使用不正确

问题描述

这是我在 MySQL 数据库中的第一个存储过程。

我需要更新数千行，并且使用传统的 sql 查询数据库崩溃了。

我认为使用限制语法更新这数千行，但我有以下错误：

过程执行失败 1221 - UPDATE 和 LIMIT 使用不正确

你能帮助我吗 ？

BEGIN

REPEAT

    UPDATE `tbl_01` jjj
    JOIN (
        SELECT
            t1.idticket,
            MAX(t1.ass) AS date_hour_ass
        FROM
            tbl_02 t1
        GROUP BY
            t1.idticket
    ) AS kkk
    SET jjj.date_hour_ass = STR_TO_DATE(
        kkk.date_hour_ass,
        '%d/%m/%Y %H:%i'
    )
    WHERE
        jjj.date_hour_ass IS NULL
    AND jjj.idticket = kkk.idticket LIMIT 100000;

    UNTIL ROW_COUNT() = 0 END REPEAT;

    END

标签： mysqlstored-procedures

解决方案

MySQL 参考手册记录了 UPDATE 语句的这个限制。

https://dev.mysql.com/doc/refman/8.0/en/update.html

摘抄：

对于多表语法，ORDER BY不能LIMIT使用。

一种可能的解决方法是避免连接，并使用相关子查询。从性能的角度来看，这不是最佳的，但性能应该可以通过合适的可用索引来预测

... ON `tbl_02` (`idticket`,`ass`)

有点奇怪的是，我们得到了一个字符串的最大值，期望一个以 format 表示的日期值dd/mm/yyyy。

作为字符串值比较，'30/03/2019'大于'25/06/2019'。这与我们比较日期值得到的结果有很大不同。

6 月 25 日 ( '2019-06-25') “大于” 2019 年 3 月 3 日 ( '2019-03-30')

也许这就是我们想要的，最大的字符串值首先比较天值，然后再比较月份，带或不带前导零。我不知道我们想要达到什么目标。我猜我们实际上可能想要最大日期值，但这只是一个猜测。

  UPDATE `tbl_01` t
     SET t.date_hour_ass = ( SELECT MAX(STR_TO_DATE(s.ass,'%d/%m/%Y %H:%i'))
                               FROM `tbl_02` s
                              WHERE s.idticket = t.idticket
                           )
   WHERE t.date_hour_ass IS NULL
   LIMIT 100000

请注意，该LIMIT子句是对匹配行数的限制，而不是对更改的行数的限制。我们没有看到任何保证不会tbl_01有没有 NULL 值的行，即使在 UPDATE 语句处理它之后也是如此。例如，tbl_02 中可能没有匹配的行，因此子查询返回 NULL。

因此，该语句有可能匹配 100,000 行并更改零行，因此ROWS_AFFECTED()即使有更多行tbl_01可以更新，也可能返回 0。

一个丑陋的解决方法是在 WHERE 子句中包含相同子查询的条件。

  UPDATE `tbl_01` t
     SET t.date_hour_ass = ( SELECT MAX(STR_TO_DATE(s.ass,'%d/%m/%Y %H:%i'))
                               FROM `tbl_02` s
                              WHERE s.idticket = t.idticket
                           )
   WHERE t.date_hour_ass IS NULL
     AND
                           ( SELECT MAX(STR_TO_DATE(s.ass,'%d/%m/%Y %H:%i'))
                               FROM `tbl_02` s
                              WHERE s.idticket = t.idticket
                           )
         IS NOT NULL
   LIMIT 100000

那些tbl_01具有非 NULL 值date_hour_ass但子查询将返回 NULL 或与当前分配的值不同的值的行呢？我们是否关心更新这些行？

推荐阅读