mysql 5.6 存储过程_MySQL 5.6 MRR 的存储过程完美诠释

DELIMITER $$

USE `ytt`$$

DROP PROCEDURE IF EXISTS `sp_range_scan5_5`$$

CREATE DEFINER=`admin`@`%` PROCEDURE `sp_range_scan5_5`()

BEGIN

– Sample sql statement is below.

– select log_time from person where nick_name = 'Lucy';

DECLARE i INT UNSIGNED DEFAULT 0;

DECLARE cnt INT UNSIGNED DEFAULT 0;

SET @result = '';

SELECT COUNT(1) INTO cnt FROM person WHERE nick_name = 'Lucy';

loop1:WHILE i < cnt

DO

SET @stmt = CONCAT('select id into @v_id from person where nick_name = ''Lucy'' order by nick_name asc limit ',i,',1');

PREPARE s1 FROM @stmt;

EXECUTE s1;

SET @result = CONCAT(@result,'select log_time from person where id = @v_id');

SET @result = CONCAT(@result,' union all ');

SET i = i + 1;

END WHILE loop1;

SET @result = SUBSTR(@result,1,CHAR_LENGTH(@result)-CHAR_LENGTH(' union all '));

PREPARE s1 FROM @result;

EXECUTE s1;

DROP PREPARE s1;

SET @result = NULL;

END$$

DELIMITER ;