mysql 存储过程递归
2011-08-31 16:47
在Oracle中可以使用CONNECT BY子句可以轻松的实现递归查询，在MSSQLServer和DB2中则可以使用WITH子句来实现递归查询，MYSQL中即不支持CONNECT BY子句也不支持WITH子句，所以要实现递归查询就必须使用其他的方式来变通实现， 而且实现方案也随需求的不同而有差异。
下为mysql 使用程序过程的 递归实例（互联网整理），
1.创建表
DROP TABLE IF EXISTS `item_category`;
CREATE TABLE `item_category` (
`id` int(11) NOT NULL auto_increment,
`catId` int(11) default NULL,
`parentId` int(11) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=REDUNDANT;
2,插入数据
INSERT INTO `item_category` VALUES (1,1,0);
INSERT INTO `item_category` VALUES (2,2,1);
INSERT INTO `item_category` VALUES (3,3,1);
INSERT INTO `item_category` VALUES (4,4,2);
INSERT INTO `item_category` VALUES (5,5,3);
3.创建存储过程
DELIMITER //
drop procedure if exists findLChild//
CREATE PROCEDURE findLChild(iid bigint(20),layer bigint(20))
BEGIN
create temporary table if not exists tmp_table (id bigint(20));
SET@@max_sp_recursion_depth=99;
call iterative(iid,layer);
select * from tmp_table;
drop temporary table if exists tmp_table;
END;//
DELIMITER ;
DELIMITER //
drop procedure if exists iterative//
CREATE PROCEDURE iterative(iid bigint(20),layer bigint(20))
BEGIN
declare tid bigint(20) default -1;
declare cur1 CURSOR FOR select catId from item_category where parentId=iid;
declare CONTINUE HANDLER FOR SQLSTATE '02000' SET tid = null;
if layer > 0 then
OPEN cur1;
FETCH cur1 INTO tid;
WHILE(tid is not null)
DO
insert into tmp_table values(tid);
call iterative(tid,layer-1);
FETCH cur1 INTO tid;
END WHILE;
end if;
END;//
DELIMITER ;
4.调用
call findLChild(1,50);
5 java 调用
用JAVA执行存储过程并取得结果集的代码如下：
Connection con = new Connection();----假设这里已经取得一个有效连接.
ResultSet rs=null;
CallableStatement cstm = con.prepareCall("{CALL testrs(?)}");
cstm.setInt(1,2);----将存储过程的第一个参数填充值2.
boolean bl = cstm.execute();-----执行存储过程
while(bl){----若存储过程成功执行了，并且有结果集返回了，那么bl就是true
rs=cstm.getResultSet()----取得一个结果集，
while(rs.next()){
System.out.println(rs.getInt(1));--- 随便输出一个值。
}
bl=cstm.getMoreResultSet();----若还能取得结果集，则bl=true，这样就回到循环首部，继续取得结果集进行处理，若不再有结果集了，则bl=false。结果循环。
这样，只要三次循环，这个存储过程的三个结果集就可以被分别渠道并处理了。
|
树形结构的表如下：DROP TABLE IF EXISTS `survey`.`tree_test`;CREATE TABLE `survey`.`tree_test` ( `form_id` varchar(32) CHARACTER SET utf8 NOT NULL, `parent_form_id` varchar(32) CHARACTER SET utf8 DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;INSERT INTO tree_test(form_id,parent_form_id) VALUES('001','000');INSERT INTO tree_test(form_id,parent_form_id) VALUES('002','001');INSERT INTO tree_test(form_id,parent_form_id) VALUES('003','001');INSERT INTO tree_test(form_id,parent_form_id) VALUES('004','003');INSERT INTO tree_test(form_id,parent_form_id) VALUES('005','002');INSERT INTO tree_test(form_id,parent_form_id) VALUES('006','002');保存中间结果的临时表如下：CREATE TEMPORARY TABLE IF NOT EXISTS treeRecurisiveTest_tmp( form_id VARCHAR(32), parent_form_id VARCHAR(32) ) ENGINE=INNODB DEFAULT CHARSET=utf8;存储过程如下：DELIMITER $$USE `survey`$$DROP PROCEDURE IF EXISTS `treeRecurisiveTest`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `treeRecurisiveTest`(IN pid VARCHAR(32))BEGIN DECLARE v_done1 INT DEFAULT 0; DECLARE v_form_id VARCHAR(32); DECLARE v_parent_form_id VARCHAR(32); DECLARE cur1 CURSOR FOR SELECT t.form_id,t.parent_form_id FROM tree_test AS t WHERE t.parent_form_id = pid; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET v_done1 = 1; SET max_sp_recursion_depth = 12; OPEN cur1; FETCH cur1 INTO v_form_id,v_parent_form_id; WHILE v_done1 <> 1 DO INSERT INTO treeRecurisiveTest_tmp(form_id,parent_form_id) VALUES(v_form_id,v_parent_form_id); CALL `survey`.`treeRecurisiveTest`(v_form_id); FETCH cur1 INTO v_form_id,v_parent_form_id; END WHILE; CLOSE cur1; SELECT * FROM treeRecurisiveTest_tmp; END$$DELIMITER ;
1、调用存储过程，传入顶级节点的FORM_ID
TRUNCATE TABLE treeRecurisiveTest_tmp;
CALL treeRecurisiveTest('001');
2、查看存储过程的返回结果
form_id form_name
002 001
005 002
3、存储过程调用后，直接用SELECT * FROM treeRecurisiveTest_tmp; 返回的结果。
form_id form_name
002 001
005 002
006 002
003 001
004 003
请教大家：为什么第2步中，存储过程直接返回的结果只有2条记录呢？
谢谢帮忙！
当工作在很大的表上时，您可能偶尔需要运行很多查询获得一个大量数据的小的子集，不是对整个表运行这些查询，而是让MySQL每次找出所需的少数记录，将记录选择到一个临时表可能更快些，然后多这些表运行查询。
创建临时表很容易，给正常的CREATE TABLE语句加上TEMPORARY关键字:
CREATE TEMPORARY TABLE tmp_table (
name VARCHAR(10) NOT NULL,
value INTEGER NOT NULL
)
临时表将在您连接MySQL期间存在。当您断开时，MySQL将自动删除表并释放所用的空间。当然您能够在仍然连接的时候删除表并释放空间。
DROP TABLE tmp_table
假如在您创建名为tmp_table临时表时名为tmp_table的表在数据库中已存在，临时表将有必要屏蔽(隐藏)非临时表tmp_table。
假如您声明临时表是个HEAP表，MySQL也允许您指定在内存中创建他:
CREATE TEMPORARY TABLE tmp_table (
name VARCHAR(10) NOT NULL,
value INTEGER NOT NULL
) TYPE = HEAP
因为HEAP表存储在内存中，您对他运行的查询可能比磁盘上的临时表快些。然而，HEAP表和一般的表有些不同，且有自身的限制。详见MySQL参考手册。
正如前面的建议，您应该测试临时表看看他们是否真的比对大量数据库运行查询快。假如数据很好地索引，临时表可能一点不快。
delimiter ||
create procedure sp_test1(
IN pageno int, IN pagesize int,
OUT pagecount int
)
BEGIN
declare idlower bigint;
declare idupper bigint;
declare totalreccount int;
drop table if exists tmp_table21;
CREATE TEMPORARY TABLE tmp_table21(
rowid bigint auto_increment primary key,
userid bigint
);
insert into tmp_table21 (userid) select ID from restcomments;
set idlower=(pageno-1)*pagesize+1;
set idupper=pageno*pagesize;
select * from tmp_table21;
/*计算总页数*/
select count(*) from tmp_table21 into totalreccount;
set pagecount=totalreccount;
end||
delimiter ;
注意:
引言：某客户新上线一个项目，利用存储过程处理用户登录相关事务。在存储过程中，需要对用户数据进行处理，于是他们采用临时表(temporary table)来做这个动作，先创建一个临时表，然后插入数据，处理；由于是采用连接池方式，担心临时表被复用，于是在最后删除该临时表。该客户采用16G的2950机器做mysql db server，利用loadrunner进行模拟登录测试，发现并发量达到2,30万之后，就再也上不去了，而且峰值不是很稳定的处于30多万的级别上。
一开始以为是机器性能达到了极限，经过询问各种状况后，认为应该还可以得到改进和优化。经过现场分析后，发现在测试达到峰值时，会有大量的 "waiting for table"，以及大量的 create temporary table 和 drop table 的线程在等待。很明显，瓶颈在于频繁的创建和删除临时表，mysql需要频繁的处理打开和关闭表描述符，才会导致了上面的问题。还好他们采用了连接池，否则情况将会更糟糕。建议他们把最后的 drop table 改成 truncate table，把临时表清空了，也就不会担心下一次调用时临时表不为空了，省去了频繁的处理表文件描述符，并发用户数也稳定的保持在了40多万。
label_proc:
begin
declare v_category_inner_code int;
declare v_rootid int default null;
declare v_tempid int default null;
declare v_listid varchar(1000) default 'aaa';
set v_category_inner_code = p_category_inner_code;
while (v_category_inner_code is not null)
do
begin
if exists (select * from pu_productcategory where category_inner_code = v_category_inner_code) then
select category_inner_code,up_inner_code into v_tempid,v_category_inner_code from pu_productcategory where category_inner_code = v_category_inner_code;
else
select v_category_inner_code;
set v_category_inner_code = null;
end if;
end;
end while;
select * from pu_productcategory where category_inner_code = v_category_inner_code;
end label_proc
- 如上代码：mySQL存储过程 需传入一个参数p_category_inner_code int,数据表结构主要就一个category_inner_code分类编号 和上up_inner_code上级分类编号，我想根据p_category_inner_code 传入的参数递归查询
如上代码：mySQL存储过程 需传入一个参数p_category_inner_code int,数据表结构主要就一个category_inner_code分类编号 和上up_inner_code上级分类编号，我想根据p_category_inner_code 传入的参数递归查询
- 出该分类的最根级分类，就一个while简单的循环不错那里了，就是没结果出来，好象变成死循环了，我感觉我做的没错啊，希望高手来解决，本人mysql新手。
出该分类的最根级分类，就一个while简单的循环不错那里了，就是没结果出来，好象变成死循环了，我感觉我做的没错啊，希望高手来解决，本人mysql新手。
呵呵,自己解决了，错在这步:select category_inner_code,up_inner_code into v_tempid,v_category_inner_code from pu_productcategory where category_inner_code = v_category_inner_code;原本是想根据v_category_inner_code来取记录并把up_inner_code 给v_category_inner_code赋值一步完成,下次递归循环查询的就是ID为这条记录的上级分类,但是MYSQL这里却不能很好的处理这个v_category_inner_code作为条件又给它赋值的操作,语法是没错误的，但是结果就是不出来，运行时还提示的结果超过多行错误!
Mysql游标在操作小数据量时比较方便，效率可观，但操作大数据量，速度比较慢，甚至直接产生系统错误。
一般说来，当操作的数据超过1万条时，就避免用游标吧。
为了测试游标性能，写了下面一个游标对IDC_Gather_Info表中数据进行遍历
- CREATE DEFINER=`root`@`%` PROCEDURE `debug`(IN `beginTime` int, IN `checkTime` int)
- BEGIN
- DECLARE t_id VARCHAR(64) DEFAULT '';
- DECLARE t_item TINYINT DEFAULT 0;
- DECLARE t_result VARCHAR(8192) DEFAULT '';
-
- DECLARE cursorDone INT DEFAULT 0;
- DECLARE cur CURSOR FOR SELECT Asset_Id, Check_Item, Check_Result from IDC_Gather_Info WHERE Check_Time > beginTime AND Check_Time <= checkTime;
- DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET cursorDone = 1;
-
- OPEN cur;
- cursorLoop:LOOP
- FETCH cur INTO t_id, t_item, t_result;
- IF cursorDone = 1 THEN
- LEAVE cursorLoop;
- END IF;
- END LOOP;
- CLOSE cur;
- END
CREATE DEFINER=`root`@`%` PROCEDURE `debug`(IN `beginTime` int, IN `checkTime` int)
BEGIN
DECLARE t_id VARCHAR(64) DEFAULT '';
DECLARE t_item TINYINT DEFAULT 0;
DECLARE t_result VARCHAR(8192) DEFAULT '';
DECLARE cursorDone INT DEFAULT 0;
DECLARE cur CURSOR FOR SELECT Asset_Id, Check_Item, Check_Result from IDC_Gather_Info WHERE Check_Time > beginTime AND Check_Time <= checkTime;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET cursorDone = 1;
OPEN cur;
cursorLoop:LOOP
FETCH cur INTO t_id, t_item, t_result;
IF cursorDone = 1 THEN
LEAVE cursorLoop;
END IF;
END LOOP;
CLOSE cur;
END
下面是当表中数据分别为15万、5万、1万时游标的表现：
1.数据量15万，存储过程执行失败，提示错误：Incorrect key file for table '/tmp/#sql_3044_0.MYI';try to repair it
2.数据量5万，执行成功，耗时31.051s
3.数据量1万，执行成功，耗时1.371s
下面使用临时表替换游标：
- CREATE DEFINER=`root`@`%` PROCEDURE `debug`(IN `beginTime` int, IN `checkTime` int)
- BEGIN
- DECLARE t_id VARCHAR(64) DEFAULT '';
- DECLARE t_item TINYINT DEFAULT 0;
- DECLARE t_result VARCHAR(8192) DEFAULT '';
-
- DECLARE maxCnt INT DEFAULT 0;
- DECLARE i INT DEFAULT 0;
-
- DROP TABLE IF EXISTS Gather_Data_Tmp;
- CREATE TEMPORARY TABLE Gather_Data_Tmp(
- `Tmp_Id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
- `Asset_Id` VARCHAR(16) NOT NULL,
- `Check_Item` TINYINT(1) NOT NULL,
- `Check_Result` VARCHAR(8192) NOT NULL,
- PRIMARY KEY (`Tmp_Id`)
- )ENGINE=MyISAM DEFAULT CHARSET=utf8;
-
- SET @tSql = CONCAT('INSERT INTO Gather_Data_Tmp (`Asset_Id`, `Check_Item`, `Check_Result`)
- SELECT Asset_Id, Check_Item, Check_Result
- FROM IDC_Gather_Info
- WHERE Check_Time > ',beginTime,' AND Check_Time <= ',checkTime);
- PREPARE gatherData FROM @tSql;
- EXECUTE gatherData;
-
- SELECT MIN(`Tmp_Id`) INTO i FROM Gather_Data_Tmp;
- SELECT MAX(`Tmp_Id`) INTO maxCnt FROM Gather_Data_Tmp;
-
- WHILE i <= maxCnt DO
- SELECT Asset_Id, Check_Item, Check_Result INTO t_id, t_item, t_result FROM Gather_Data_Tmp WHERE Tmp_Id = i;
- SET i = i + 1;
- END WHILE;
- END
CREATE DEFINER=`root`@`%` PROCEDURE `debug`(IN `beginTime` int, IN `checkTime` int)
BEGIN
DECLARE t_id VARCHAR(64) DEFAULT '';
DECLARE t_item TINYINT DEFAULT 0;
DECLARE t_result VARCHAR(8192) DEFAULT '';
DECLARE maxCnt INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DROP TABLE IF EXISTS Gather_Data_Tmp;
CREATE TEMPORARY TABLE Gather_Data_Tmp(
`Tmp_Id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`Asset_Id` VARCHAR(16) NOT NULL,
`Check_Item` TINYINT(1) NOT NULL,
`Check_Result` VARCHAR(8192) NOT NULL,
PRIMARY KEY (`Tmp_Id`)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET @tSql = CONCAT('INSERT INTO Gather_Data_Tmp (`Asset_Id`, `Check_Item`, `Check_Result`)
SELECT Asset_Id, Check_Item, Check_Result
FROM IDC_Gather_Info
WHERE Check_Time > ',beginTime,' AND Check_Time <= ',checkTime);
PREPARE gatherData FROM @tSql;
EXECUTE gatherData;
SELECT MIN(`Tmp_Id`) INTO i FROM Gather_Data_Tmp;
SELECT MAX(`Tmp_Id`) INTO maxCnt FROM Gather_Data_Tmp;
WHILE i <= maxCnt DO
SELECT Asset_Id, Check_Item, Check_Result INTO t_id, t_item, t_result FROM Gather_Data_Tmp WHERE Tmp_Id = i;
SET i = i + 1;
END WHILE;
END
1.数据量15万，执行成功，耗时8.928s
2.数据量5万，执行成功，耗时2.994s
3.数据量1万，执行成功，耗时0.634s
可以看到Mysql的游标在处理大一点的数据量时还是比较乏力的，仅适合用于操作几百上千的小数据量。
MySQL使用递归存储过程实现树状结构
- 感谢作者:佚名 来源:http://www.zxbc.cn/ 日期:2008-12-22 影响数:332 好评: 0 差评:0
-
分类:MySQL 关键字:
1 创建一个数据表
查看复制到剪切板打印
CREATE TABLE `tree` (
`id` int(10) NOT NULL,
`fid` int(10) NOT NULL
)；
id 是编号
fid 是上级编号， 顶层是0或者你自己定义
2 创建临时表
create temporary table if not exists tmp_table(id bigint(20),fid bigint(20),lvl int)//
3 编写存储过程
查看复制到剪切板打印
DELIMITER //
drop procedure if exists useCursor //
-- 建立存储过程
-- JAVA世纪网
www.java2000.net 老紫竹
CREATE PROCEDURE useCursor(iid bigint(20),lvl int)
BEGIN
-- 局部变量定义
declare tid bigint(20) default -1 ;
declare tfid bigint(20) default -1 ;
-- 游标定义
declare cur1 CURSOR FOR select id,fid from tree where fid=iid ;
-- 游标介绍定义
declare CONTINUE HANDLER FOR SQLSTATE '02000' SET tid = null,tfid=null;
SET @@max_sp_recursion_depth = 10;
-- 开游标
OPEN cur1;
FETCH cur1 INTO tid,tfid;
WHILE ( tid is not null )
DO
insert into tmp_table values(tid,tfid,lvl);
-- 树形结构数据递归收集到建立的临时表中
call useCursor(tid,lvl+1);
FETCH cur1 INTO tid,tfid ;
END WHILE;
END;//
DELIMITER ;
4 调用方式，并删除临时表
call useCursor(2,0);
select * from tmp_table ;
5 删除临时表
drop temporary table if exists tmp_table ;
143
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
