我在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
对于我的生活,我找不到一个问题,这个逻辑会导致计数跳过一个节拍。
改变:
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;
这是因为:
如果FETCH超过结果集中的最后一行,则目标字段或变量的值是不确定的,并且
NOTFOUND
属性返回TRUE
。
参考:(这是在Oracle游标上,但也适用于其他):
- 甲骨文:Fetch语句