mysql store procedure 遇到的奇葩问题

【声明】本文为AdamsLee原创,转载请注明出自围炉网并保留本文有效链接:mysql store procedure 遇到的奇葩问题, 转载请保留本声明!

直接执行store procedure竟然与debug时出来的结果不一致

CREATE DATABASE `demo` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */;

CREATE TABLE `game` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `user_id` int(11) NOT NULL,

  `round_no` varchar(45) COLLATE utf8_bin NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `gameorder` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `round_no` varchar(45) COLLATE utf8_bin NOT NULL,

  `game_order` int(11) NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO `game` (`id`,`user_id`,`round_no`) VALUES (1,1,'第一轮');

INSERT INTO `game` (`id`,`user_id`,`round_no`) VALUES (2,2,'第二轮');

INSERT INTO `game` (`id`,`user_id`,`round_no`) VALUES (3,2,'第一轮');

INSERT INTO `game` (`id`,`user_id`,`round_no`) VALUES (4,3,'第二轮');

INSERT INTO `game` (`id`,`user_id`,`round_no`) VALUES (5,4,'第二轮');

INSERT INTO `game` (`id`,`user_id`,`round_no`) VALUES (6,3,'第一轮');

INSERT INTO `gameorder` (`id`,`round_no`,`game_order`) VALUES (1,'第一轮',1);

INSERT INTO `gameorder` (`id`,`round_no`,`game_order`) VALUES (2,'第二轮',2);

INSERT INTO `gameorder` (`id`,`round_no`,`game_order`) VALUES (3,'第三轮',3);

CREATE DEFINER=`root`@`localhost` PROCEDURE `report`(IN roundNo VARCHAR(45))

begin

    declare userId int;

    declare gameOrder int;

    declare tmpGameOrder int;

    declare tmpRoundNo VARCHAR(45);

    declare stopFlag int; 

    declare award_cursor CURSOR for select user_id from `demo`.`game` where round_no = roundNo;

    DECLARE CONTINUE HANDLER FOR NOT FOUND set stopFlag=1; 

    DROP TABLE IF EXISTS `demo`.`tmp_report`;

    CREATE TEMPORARY TABLE `demo`.`tmp_report` (

    `round_no` VARCHAR(45) NOT NULL,

    `game_order` INT NOT NULL,

    `user_id` INT NOT NULL);

    select game_order into gameOrder

    from `demo`.`gameorder` 

    where round_no = roundNo;

    insert into `demo`.`tmp_report`(`round_no`, `game_order`, `user_id`)

    select a.`round_no`, s.`game_order`,a.`user_id`

    from `demo`.`game` as a inner join `demo`.`gameorder` as s on a.round_no = s.round_no 

    where a.round_no = roundNo;

    open award_cursor;

    REPEAT  

        fetch award_cursor into userId;

        #try to find previous game's roundNo

        set tmpGameOrder = gameOrder – 1;

        select round_no into tmpRoundNo

        from `demo`.`gameorder` 

        where game_order = tmpGameOrder;

        while (exists(select * from `demo`.`game` where user_id = userId and round_no = tmpRoundNo limit 1))

        do

            insert into `demo`.`tmp_report`(`round_no`, `game_order`, `user_id`)

            select a.`round_no`, s.`game_order`,a.`user_id`

            from `demo`.`game` as a inner join `demo`.`gameorder` as s on a.round_no = s.round_no 

            where a.round_no = tmpRoundNo and a.user_id = userId;

            set tmpGameOrder = tmpGameOrder – 1;

            set tmpRoundNo = null;

            #get previous round

            select round_no into tmpRoundNo

            from `demo`.`gameorder` 

            where game_order = tmpGameOrder;

        end while;

    UNTIL stopFlag = 1

    END REPEAT;

    CLOSE award_cursor; 

    SELECT *

    from `demo`.`tmp_report` order by game_order;

    DROP TABLE `demo`.`tmp_report`;

end

call `demo`.`report`('第二轮');

第一轮 1 2
第二轮 2 2
第二轮 2 3
第二轮 2 4

此条目发表在未分类分类目录,贴了标签。将固定链接加入收藏夹。