MySQL CURSOR 循环在存储过程中添加额外的传递

Question

我在 mysql 存储过程中遇到游标获取循环问题。我的存储过程运行一个重新排序过程，该过程运行良好，直到排序的最后一个记录的顺序编号跳过一位数字。例如，如果我有 10 条记录，并且排序过程从 1 开始，则结果记录中应显示从 1 到 10 的所有数字。但是，我的存储过程会跳过最后一次计数(在上述情况下为 10)，并对最终记录重新编号 11，因此计数从 9 变为 11。无论涉及多少条记录，情况都是如此。

该过程的逻辑相当简单:

我有一个保存产品类型记录的表，其中有一个 sort_order 列，用于根据常规批处理周期中的使用情况对记录重新排序。

CREATE TABLE `PRODUCT_TYPE` (
  `PRODUCT_TYPE_ID` int(11) NOT NULL AUTO_INCREMENT,
  `PRODUCT_TYPE_NAME` varchar(45) NOT NULL,
  `PRODUCT_CATEGORY_ID` int(11) DEFAULT NULL,
  `LIFESPAN_MONTHS` int(11) DEFAULT NULL,
  `USER_ID` int(11) DEFAULT NULL,
  `UPDATED_BY` int(11) DEFAULT NULL,
  `UPDATED_DATE` datetime DEFAULT NULL,
  `CREATED_DATE` datetime DEFAULT NULL,
  `CREATED_BY` int(11) DEFAULT NULL,
  `REVIEWED` bit(1) NOT NULL DEFAULT b'0',
  `SORT_ORDER` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`PRODUCT_TYPE_ID`),
  KEY `fk_PRODUCT_TYPE_PRODUCT_CATEGORY1_idx` (`PRODUCT_CATEGORY_ID`),
  KEY `fk_PRODUCT_TYPE_USERS1_idx` (`USER_ID`),
  KEY `fk_PRODUCT_TYPE_USERS2_idx` (`UPDATED_BY`),
  KEY `fk_PRODUCT_TYPE_USERS3_idx` (`CREATED_BY`),
  CONSTRAINT `fk_PRODUCT_TYPE_PRODUCT_CATEGORY1` FOREIGN KEY (`PRODUCT_CATEGORY_ID`) REFERENCES `PRODUCT_CATEGORY` (`PRODUCT_CATEGORY_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_PRODUCT_TYPE_USERS1` FOREIGN KEY (`USER_ID`) REFERENCES `USERS` (`USER_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_PRODUCT_TYPE_USERS2` FOREIGN KEY (`UPDATED_BY`) REFERENCES `USERS` (`USER_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_PRODUCT_TYPE_USERS3` FOREIGN KEY (`CREATED_BY`) REFERENCES `USERS` (`USER_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=61 DEFAULT CHARSET=latin1;

我每晚运行以下存储过程，以使用 sort_order 列记录订单，根据对每种类型的引用数量对产品类型记录重新排序。

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `REORDER_MANUFACTURERS`()
BEGIN

DECLARE DONE BOOL;
DECLARE MID INT;
DECLARE MNAME VARCHAR(255);
DECLARE USES INT;
DECLARE SORT_ORDER_COUNTER INT;

DECLARE CUR CURSOR FOR SELECT M.MANUFACTURER_ID, M.MANUFACTURER_NAME, COUNT(U.UNIT_ID) AS USES 
FROM MANUFACTURERS M LEFT JOIN mydb.UNITS U ON  M.MANUFACTURER_ID = U.MANUFACTURER_ID
GROUP BY M.MANUFACTURER_ID, M.MANUFACTURER_NAME
ORDER BY USES DESC, MANUFACTURER_NAME;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE = TRUE;

SET SORT_ORDER_COUNTER = 0;

OPEN CUR;

READ_LOOP: LOOP

    FETCH CUR INTO MID, MNAME, USES;

    UPDATE MANUFACTURERS SET SORT_ORDER = SORT_ORDER_COUNTER WHERE MANUFACTURER_ID = MID;

    IF DONE THEN
      LEAVE READ_LOOP;
    END IF;

    SET SORT_ORDER_COUNTER = SORT_ORDER_COUNTER + 1;

END LOOP;

CLOSE CUR;

END

在我的一生中，我找不到这种逻辑的问题会导致计数跳动。任何帮助将不胜感激。

Answer 1

更改:

FETCH CUR INTO MID, MNAME, USES;

UPDATE MANUFACTURERS SET SORT_ORDER = SORT_ORDER_COUNTER 
    WHERE MANUFACTURER_ID = MID;

IF DONE THEN
  LEAVE READ_LOOP;
END IF;

致:

FETCH CUR INTO MID, MNAME, USES;

IF DONE THEN
  LEAVE READ_LOOP;
END IF;

UPDATE MANUFACTURERS SET SORT_ORDER = SORT_ORDER_COUNTER 
    WHERE MANUFACTURER_ID = MID;

这是因为:

If you FETCH past the last row in the result set, the values of the target fields or variables are indeterminate and the NOTFOUND attribute returns TRUE.

引用:(这是针对 Oracle 游标的，但也适用于其他游标):

• Oracle: Fetch Statement