这是我原始代码的抽象,因为它对你们来说更容易阅读.
我是Mysql Storage程序的新手,也是Cursors的新手.
发生的事情是,当我在查询上设置ORDER BY指令时,Cursor没有正确地对结果进行排序.
这是表格重现问题的所有结构和数据.
记录表:
DROP TABLE IF EXISTS `log`;
CREATE TABLE `log` (
`key` text NOT NULL,
`value` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
测试表:
DROP TABLE IF EXISTS `test1`;
CREATE TABLE `test1` (
`ID` bigint(8) unsigned NOT NULL AUTO_INCREMENT,
`price` float(16,8) NOT NULL,
PRIMARY KEY (`ID`),
KEY `price` (`price`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1;
测试表数据:
INSERT INTO `test1` (`price`)
VALUES (100),(200),(300),(400),(300),(200),(100);
查询:
SELECT *
FROM `test1`
ORDER BY price DESC;
预期结果可以直接查询:
> 4 – 400.00000000
> 5 – 300.00000000
> 3 – 300.00000000
> 6 – 200.00000000
> 2 – 200.00000000
> 7 – 100.00000000
> 1 – 100.00000000
存储过程
DROP PROCEDURE IF EXISTS `test_proc1`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `test_proc1`()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE ID BIGINT(8);
DECLARE price FLOAT(16,8);
DECLARE cur1 CURSOR FOR
SELECT * FROM `test1` ORDER BY price DESC; #Exact Query
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
START TRANSACTION;
OPEN cur1;
#Cleaning log
TRUNCATE `log`;
read_loop:
LOOP
FETCH cur1 INTO ID,price;
IF done = 1 THEN
LEAVE read_loop;
END IF;
#Inserting data to log
INSERT INTO `log`
VALUES (ID,price);
END LOOP read_loop;
CLOSE cur1;
COMMIT;
#Bring log for result
SELECT * FROM log;
END;;
DELIMITER ;
通话程序
CALL test_proc1();
CURSOR与我在顶部发布的查询完全相同,您可以在存储过程中检查.但当我循环通过它时,我得到了另一个订单.
> 15 100.00000000
> 21 100.00000000
> 16 200.00000000
> 20 200.00000000
> 17 300.00000000
> 19 300.00000000
> 18 400.00000000
这是怎么回事?有人可以帮我吗?
我也试过像这样嵌套查询而根本没有修复.
SELECT * FROM(
SELECT *
FROM `test1`
ORDER BY price DESC) AS tmp_tbl