好好学MySQL：MySQL中的游标

1. 需求

已知app表有两个字段：id和name，build_history表有四个字段：id、app_id、image_path和create_time。
app构建后会生成镜像，镜像地址存在于build_history表中的image_path字段。

现在想要查找出每个app的最新的五个镜像，该怎样实现？

2. 思路

思路一：使用select拿到所有的app id，然后使用shell脚本生成很多条sql语句，每条sql语句的查询结果插入到一个结果表中。具体参考《Shell实用脚本》循环读取多列文本部分。

思路二：使用mysql cursor，拿到所有的app id，然后循环读取build_image中的内容，插入到结果表中。本文中采用mysql cursor的方法，实现我们的需求。

3. 代码实现

-- create table
drop table if exists latest_image;
CREATE TABLE test.latest_image (
    id INT auto_increment NOT null primary key,
	app_id INT NULL,
	app_name varchar(128) NULL,
	app_image varchar(512) NULL
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8
COLLATE=utf8_general_ci;

-- create procedure
drop procedure if exists get_latest;
CREATE PROCEDURE get_latest (num int(2))
BEGIN

declare appid int default 0;
declare appname varchar(128) default '';
declare appimage varchar(512) default '';
declare finished int default 0;

	-- declare cursor for app
declare appcursor CURSOR FOR 
SELECT id,name FROM app;

	-- declare NOT FOUND handler
declare CONTINUE HANDLER 
FOR NOT FOUND SET finished = 1;

OPEN appcursor;
get_item: LOOP
	FETCH appcursor INTO appid,appname;
	IF finished = 1 THEN 
		LEAVE get_item;
	END IF;
	-- select appid,appname;
	-- select id,app_id,image_path from build_history where app_id=appid order by create_time desc limit 5;
	-- insert into latest_image(`app_id`,'app_name','app_image') values(appid,appname,appimage);
	insert into latest_image(`app_id`,`app_image`)
	select app_id as app_id,image_path as app_image 
	from build_history 
	where app_id=appid order by create_time desc limit num;
END LOOP get_item;
CLOSE appcursor;

END

-- call procedure
call get_latest(5);