业务场景:我以类似于链表的结构存储一些数据,现在需要遍历这一链表。每条数据用一个字段存它的下一条数据的id。 解决方法:一条一条取出加入数据到临时表之中之后再全部拿出。 实例数据表结构如下:
存储过程如下:
CREATE DEFINER=`root`@`localhost` PROCEDURE `NewProc`(in userid int)
BEGIN
#Routine body goes here...
declare prep int default 0 ;
-- 创建临时表若存在则不再次创建
CREATE TEMPORARY TABLE if not exists temp
(
`id` int(11) NOT NULL,
`uername` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`tel` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`emil` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`preid` int(11) NULL DEFAULT NULL
);
-- 删除表数据
truncate table temp;
Insert into temp select * from userinfo where id = userid;
select preid into prep from userinfo where id = userid;
while prep >=0 do
Insert into temp select * from userinfo where id = prep;
select preid into prep from userinfo where id = prep;
end while;
select * from temp order by id;
--
END