在Mysql数据库里通过存储过程实现树形的遍历

25-02-07 16

在本文中，我们将给您介绍关于在Mysql数据库里通过存储过程实现树形的遍历的详细内容，此外，我们还将为您提供关于Linux下mysql通过存储过程实现批量生成记录、mssqlserver数据库存储过程

在本文中，我们将给您介绍关于在Mysql数据库里通过存储过程实现树形的遍历的详细内容，此外，我们还将为您提供关于Linux 下mysql通过存储过程实现批量生成记录、mssql server 数据库存储过程转换成mysql数据库(1/2)、MSSQL数据库:存储过程学习_MySQL、mysql 中批量插入数据（存储过程实现）的知识。

本文目录一览：

在Mysql数据库里通过存储过程实现树形的遍历
Linux 下mysql通过存储过程实现批量生成记录
mssql server 数据库存储过程转换成mysql数据库(1/2)
MSSQL数据库:存储过程学习_MySQL
mysql 中批量插入数据（存储过程实现）

在Mysql数据库里通过存储过程实现树形的遍历

关于多级别菜单栏或者权限系统中部门上下级的树形遍历，oracle中有connect by来实现，MysqL没有这样的便捷途径，所以MysqL遍历数据表是我们经常会遇到的头痛问题，下面通过存储过程来实现。

1，建立测试表和数据：

DROP TABLE IF EXISTS csdn.channel; 
CREATE TABLE csdn.channel ( 
id INT(11) NOT NULL AUTO_INCREMENT,cname VARCHAR(200) DEFAULT NULL,parent_id INT(11) DEFAULT NULL,PRIMARY KEY (id) 
) ENGINE=INNODB DEFAULT CHARSET=utf8; 
INSERT INTO channel(id,cname,parent_id) 
VALUES (13,'首页',-1),(14,'TV580',(15,'生活580',(16,'左上幻灯片',13),(17,'帮忙',14),(18,'栏目简介',17); 
DROP TABLE IF EXISTS channel;

2，利用临时表和递归过程实现树的遍历（MysqL的UDF不能递归调用）：

2.1，从某节点向下遍历子节点，递归生成临时表数据

-- pro_cre_childlist
DROP PROCEDURE IF EXISTS csdn.pro_cre_childlist
CREATE PROCEDURE csdn.pro_cre_childlist(IN rootId INT,IN nDepth INT)
DECLARE done INT DEFAULT 0;
DECLARE b INT;
DECLARE cur1 CURSOR FOR SELECT id FROM channel WHERE parent_id=rootId;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SET max_sp_recursion_depth=12;
INSERT INTO tmpLst VALUES (NULL,rootId,nDepth); 
OPEN cur1; 
FETCH cur1 INTO b; 
WHILE done=0 DO 
CALL pro_cre_childlist(b,nDepth+1); 
FETCH cur1 INTO b; 
END WHILE; 
CLOSE cur1;

2.2，从某节点向上追溯根节点，递归生成临时表数据

-- pro_cre_parentlist
DROP PROCEDURE IF EXISTS csdn.pro_cre_parentlist
CREATE PROCEDURE csdn.pro_cre_parentlist(IN rootId INT,IN nDepth INT) 
BEGIN 
DECLARE done INT DEFAULT 0; 
DECLARE b INT; 
DECLARE cur1 CURSOR FOR SELECT parent_id FROM channel WHERE id=rootId; 
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; 
SET max_sp_recursion_depth=12; 
INSERT INTO tmpLst VALUES (NULL,nDepth); 
OPEN cur1; 
FETCH cur1 INTO b; 
WHILE done=0 DO 
CALL pro_cre_parentlist(b,nDepth+1); 
FETCH cur1 INTO b; 
END WHILE; 
CLOSE cur1;

2.3，实现类似Oracle SYS_CONNECT_BY_PATH的功能，递归过程输出某节点id路径

-- pro_cre_pathlist
USE csdn
DROP PROCEDURE IF EXISTS pro_cre_pathlist
CREATE PROCEDURE pro_cre_pathlist(IN nid INT,IN delimit VARCHAR(10),INOUT pathstr VARCHAR(1000))
BEGIN 
DECLARE done INT DEFAULT 0; 
DECLARE parentid INT DEFAULT 0; 
DECLARE cur1 CURSOR FOR 
SELECT t.parent_id,CONCAT(CAST(t.parent_id AS CHAR),delimit,pathstr) 
FROM channel AS t WHERE t.id = nid; 
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; 
SET max_sp_recursion_depth=12; 
OPEN cur1; 
FETCH cur1 INTO parentid,pathstr; 
WHILE done=0 DO 
CALL pro_cre_pathlist(parentid,pathstr); 
FETCH cur1 INTO parentid,pathstr; 
END WHILE; 
CLOSE cur1; 
DELIMITER ;

2.4，递归过程输出某节点name路径

-- pro_cre_pnlist
USE csdn
DROP PROCEDURE IF EXISTS pro_cre_pnlist
CREATE PROCEDURE pro_cre_pnlist(IN nid INT,CONCAT(t.cname,pathstr; 
WHILE done=0 DO 
CALL pro_cre_pnlist(parentid,pathstr; 
END WHILE; 
CLOSE cur1; 
DELIMITER ;

2.5，调用函数输出id路径

-- fn_tree_path
DELIMITER
DROP FUNCTION IF EXISTS csdn.fn_tree_path
CREATE FUNCTION csdn.fn_tree_path(nid INT,delimit VARCHAR(10)) RETURNS VARCHAR(2000) CHARSET utf8 
BEGIN 
DECLARE pathid VARCHAR(1000); 
SET pathid=CAST(nid AS CHAR); 
CALL pro_cre_pathlist(nid,pathid); 
RETURN pathid; 
END

2.6，调用函数输出name路径

-- fn_tree_pathname
-- 调用函数输出name路径 
DELIMITER
DROP FUNCTION IF EXISTS csdn.fn_tree_pathname
CREATE FUNCTION csdn.fn_tree_pathname(nid INT,delimit VARCHAR(10)) RETURNS VARCHAR(2000) CHARSET utf8 
BEGIN 
DECLARE pathid VARCHAR(1000); 
SET pathid=''; 
CALL pro_cre_pnlist(nid,pathid); 
RETURN pathid; 
END
DELIMITER ;

2.7，调用过程输出子节点

-- pro_show_childLst 
DELIMITER
-- 调用过程输出子节点 
DROP PROCEDURE IF EXISTS pro_show_childLst
CREATE PROCEDURE pro_show_childLst(IN rootId INT) 
BEGIN 
DROP TEMPORARY TABLE IF EXISTS tmpLst; 
CREATE TEMPORARY TABLE IF NOT EXISTS tmpLst 
(sno INT PRIMARY KEY AUTO_INCREMENT,id INT,depth INT); 
CALL pro_cre_childlist(rootId,0); 
SELECT channel.id,CONCAT(SPACE(tmpLst.depth*2),'--',channel.cname) NAME,channel.parent_id,tmpLst.depth,fn_tree_path(channel.id,'/') path,fn_tree_pathname(channel.id,'/') pathname 
FROM tmpLst,channel WHERE tmpLst.id=channel.id ORDER BY tmpLst.sno; 
END

2.8，调用过程输出父节点

-- pro_show_parentLst
DELIMITER
-- 调用过程输出父节点 
DROP PROCEDURE IF EXISTS `pro_show_parentLst`
CREATE PROCEDURE `pro_show_parentLst`(IN rootId INT) 
BEGIN 
DROP TEMPORARY TABLE IF EXISTS tmpLst; 
CREATE TEMPORARY TABLE IF NOT EXISTS tmpLst 
(sno INT PRIMARY KEY AUTO_INCREMENT,depth INT); 
CALL pro_cre_parentlist(rootId,channel WHERE tmpLst.id=channel.id ORDER BY tmpLst.sno; 
END

3，开始测试：

3.1，从根节点开始显示，显示子节点集合：

MysqL> CALL pro_show_childLst(-1);
+----+-----------------------+-----------+-------+-------------+----------------------------+
| id | NAME | parent_id | depth | path | pathname |
+----+-----------------------+-----------+-------+-------------+----------------------------+
| 13 | --首页 | -1 | 1 | -1/13 | 首页/ |
| 16 | --左上幻灯片 | 13 | 2 | -1/13/16 | 首页/左上幻灯片/ |
| 14 | --TV580 | -1 | 1 | -1/14 | TV580/ |
| 17 | --帮忙 | 14 | 2 | -1/14/17 | TV580/帮忙/ |
| 18 | --栏目简介 | 17 | 3 | -1/14/17/18 | TV580/帮忙/栏目简介/ |
| 15 | --生活580 | -1 | 1 | -1/15 | 生活580/ |
+----+-----------------------+-----------+-------+-------------+----------------------------+
6 rows in set (0.05 sec)
Query OK,0 rows affected (0.05 sec)

3.2，显示首页下面的子节点

CALL pro_show_childLst(13);
MysqL> CALL pro_show_childLst(13);
+----+---------------------+-----------+-------+----------+-------------------------+
| id | NAME | parent_id | depth | path | pathname |
+----+---------------------+-----------+-------+----------+-------------------------+
| 13 | --首页 | -1 | 0 | -1/13 | 首页/ |
| 16 | --左上幻灯片 | 13 | 1 | -1/13/16 | 首页/左上幻灯片/ |
+----+---------------------+-----------+-------+----------+-------------------------+
2 rows in set (0.02 sec)
Query OK,0 rows affected (0.02 sec)
MysqL>

3.3，显示TV580下面的所有子节点

CALL pro_show_childLst(14);
MysqL> CALL pro_show_childLst(14);
| id | NAME | parent_id | depth | path | pathname |
| 14 | --TV580 | -1 | 0 | -1/14 | TV580/ |
| 17 | --帮忙 | 14 | 1 | -1/14/17 | TV580/帮忙/ |
| 18 | --栏目简介 | 17 | 2 | -1/14/17/18 | TV580/帮忙/栏目简介/ |
3 rows in set (0.02 sec)
Query OK,0 rows affected (0.02 sec)
MysqL>

3.4，“帮忙”节点有一个子节点，显示出来：

CALL pro_show_childLst(17);
MysqL> CALL pro_show_childLst(17);
| id | NAME | parent_id | depth | path | pathname |
| 17 | --帮忙 | 14 | 0 | -1/14/17 | TV580/帮忙/ |
| 18 | --栏目简介 | 17 | 1 | -1/14/17/18 | TV580/帮忙/栏目简介/ |
2 rows in set (0.03 sec)
Query OK,0 rows affected (0.03 sec)
MysqL>

3.5，“栏目简介”没有子节点，所以只显示最终节点：

MysqL> CALL pro_show_childLst(18);
+--| id | NAME | parent_id | depth | path | pathname |
| 18 | --栏目简介 | 17 | 0 | -1/14/17/18 | TV580/帮忙/栏目简介/ |
1 row in set (0.36 sec)
Query OK,0 rows affected (0.36 sec)
MysqL>

3.6，显示根节点的父节点

CALL pro_show_parentLst(-1);
MysqL> CALL pro_show_parentLst(-1);
Empty set (0.01 sec)
Query OK,0 rows affected (0.01 sec)
MysqL>

3.7，显示“首页”的父节点

CALL pro_show_parentLst(13);
MysqL> CALL pro_show_parentLst(13);
| id | NAME | parent_id | depth | path | pathname |
| 13 | --首页 | -1 | 0 | -1/13 | 首页/ |
1 row in set (0.02 sec)
Query OK,0 rows affected (0.02 sec)
MysqL>

3.8，显示“TV580”的父节点，parent_id为-1

CALL pro_show_parentLst(14);
MysqL> CALL pro_show_parentLst(14);
| id | NAME | parent_id | depth | path | pathname |
| 14 | --TV580 | -1 | 0 | -1/14 | TV580/ |
1 row in set (0.02 sec)
Query OK,0 rows affected (0.02 sec)

3.9，显示“帮忙”节点的父节点

CALL pro_show_parentLst(17);
MysqL> CALL pro_show_parentLst(17);
| id | NAME | parent_id | depth | path | pathname |
| 17 | --帮忙 | 14 | 0 | -1/14/17 | TV580/帮忙/ |
| 14 | --TV580 | -1 | 1 | -1/14 | TV580/ |
2 rows in set (0.02 sec)
Query OK,0 rows affected (0.02 sec)
MysqL>

3.10，显示最低层节点“栏目简介”的父节点

CALL pro_show_parentLst(18);
MysqL> CALL pro_show_parentLst(18);
| id | NAME | parent_id | depth | path | pathname |
| 18 | --栏目简介 | 17 | 0 | -1/14/17/18 | TV580/帮忙/栏目简介/ |
| 17 | --帮忙 | 14 | 1 | -1/14/17 | TV580/帮忙/ |
| 14 | --TV580 | -1 | 2 | -1/14 | TV580/ |
3 rows in set (0.02 sec)
Query OK,0 rows affected (0.02 sec)
MysqL>

以上所述是小编给大家介绍的在MysqL数据库里通过存储过程实现树形的遍历，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对编程小技巧网站的支持！

Linux 下mysql通过存储过程实现批量生成记录

Linux 下MysqL通过存储过程实现批量生成记录

在开发中有一个这样的需求：需要在数据库的表中生成大量的记录，这些记录只有很少的差别，只会在创建表的时候进行一次这个操作。当然，要是人工一条一条的做也不是不可以，也可以用外部的程序来完成，但是这里我选择使用MysqL的存储过程来实现。

0、相关版本信息

MysqL使用官方打包发布的5.6.29版本，安装在linux上

1、数据表结构

表有一个自增的主键，另外有一个字段是需要保存的有少许差异的信息，另外有一个字段是其他信息，创建表的代码：

DROP TABLE IF EXISTS `t_vpn_ip`;
CREATE TABLE `t_vpn_ip` (
  `r_id`       int(16)     NOT NULL  AUTO_INCREMENT,`ipaddr`      varchar(16)   NOT NULL  UNIQUE,`cc_id`       int(16)     NOT NULL  DEFAULT 0,PRIMARY KEY (`r_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

2、存储过程的创建、调用、删除

存储过程中使用循环来生成大量的记录。由于这个生成操作只执行一次，所以在调用后就将其删除。

/**
 * 实现对VPN地值使用情况表的填充
 * 172.16.0.1--172.16.255.254
 */
DROP PROCEDURE IF EXISTS `vpn_addr_fill`;
DELIMITER ;;
CREATE PROCEDURE `vpn_addr_fill`()
BEGIN
  declare i int;
  declare j int;
  SET i=0;
  WHILE i<=255 DO
    SET j=1;
    WHILE j<255 DO
      INSERT INTO t_vpn_ip(ipaddr) VALUES(concat("172.16.",i,".",j));
      SET j = j + 1;
    END WHILE;
    SET i = i + 1;
  END WHILE;
END
;;
DELIMITER ;

/**
 * 填充数据
 */
call vpn_addr_fill();

/**
 * “功成身退”，删除存储过程
 */
DROP PROCEDURE IF EXISTS `vpn_addr_fill`;

3、说明

这里生成的记录根据实际情况调整，调用存储过程后根据具体的情况决定是否需要删除存储过程。

感谢阅读，希望能帮助到大家，谢谢大家对本站的支持！

mssql server 数据库存储过程转换成mysql数据库(1/2)

mssql server 存储过程转换成数据库

DELIMITER $$ DROP PROCEDURE IF EXISTS ChangeSequence$$ CREATE PROCEDURE ChangeSequence ( _sign INT ,-- 0：上移 1：下移 TableName VARCHAR(50) ,-- 表名 ItemName VARCHAR(50) ,-- 主键字段名 ItemID INT , -- 主键ID SortName VARCHAR(50) , -- 排序ID TypeName VARCHAR(50) , -- 分类字段名 TypeValue VARCHAR(50) -- 分类值 ) BEGIN DECLARE _SQL VARCHAR(4000); DECLARE ThisSort INT; DECLARE PREVID INT; DECLARE NextID INT; DECLARE _Count INT; CREATE TEMPORARY TABLE _Tab ( _ItemID INT, _Sort INT ); SET _SQL=CONCAT(''INSERT INTO _Tab (_ItemID,_Sort) SELECT '' ,ItemName,SortName,'' FROM '',TableName) ; IF (TypeName'''' AND TypeValue'''') THEN SET _SQL=CONCAT(_SQL,'' where '',TypeName,''='',TypeValue); END IF; SET _SQL = CONCAT(_SQL,'' ORDER BY _Sort ASC '') ; SET @v_sql=_SQL; PREPARE stmt FROM @v_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET _SQL = '''';

MSSQL数据库:存储过程学习_MySQL

这个例子里面我们从两个表中取出头两行，然后合并到一个表中。

　　在现实中我们常常会遇到这样的情况，在一个数据库中存在两个表，假设表1储存着公司个产品本季度销售信息，表2储存着公司本季度欠款金额情况。在一个页面中我们想把这两个信息显示出来。通常的做法是在程序中进行两次SQL查询，返回两个结果集，在分别显示出来，非常麻烦。

　　下面是实现这个功能的代码：

　　CREATE PROCEDURE test
　　AS
　　 SET NOCOUNT ON --指示存储过程不返回查询影响的行数
　　DECLARE @col1c varchar(20),@col2c varchar(20), @index int
　　SET @index = 1
　　CREATE TABLE #tmptbl --创建一个临时表，用于储存我们的结果
　　 (
　　 colID int IDENT99vY(1,1) PRIMARY KEY CLUSTERED,
　　 col1 varchar(20),
　　 col2 varchar(20)
　　 )

　　 DECLARE cur1 CURSOR FOR SELECT TOP 2 customerid FROM orders
　　 DECLARE cur2 CURSOR FOR SELECT TOP 2 regiondescription FROM region
　　OPEN cur1
　　 OPEN cur2
　　 FETCH cur2 INTO @col2c
　　FETCH cur1 INTO @col1c
　　WHILE @@FETCH_STATUS = 0
　　 BEGIN
　　 INSERT INTO #tmptbl (col1, col2) VALUES (@col1c, @col2c)
　　 FETCH NEXT FROM cur1 INTO @col1c
　　 FETCH NEXT FROM cur2 INTO @col2c
　　 END
　　CLOSE cur1
　　CLOSE cur2
　　 DEALLOCATE cur1
　　 DEALLOCATE cur2
　　 SELECT * FROM #tmptbl
　　 DROP TABLE #tmptbl
　　GO

　　说明：

　　@@FETCH_STATUS，返回被fetch语句执行的最后游标状态。

　　返回值：0－FETCH语句执行成功
　　　　　　1－FETCH语句失败，或此行不再结果集中。
　　　　　　2－被提取的行不存在。

mysql 中批量插入数据（存储过程实现）

1. 创建表

CREATE TABLE layout_test (

col1 int NOT NULL,

col2 int NOT NULL,

PRIMARY KEY(col1),

KEY(col2)

);

目标：向表中批量插入 10000 条数据，col1 为（1-10000） col2 为 0-100 的随机数

2. 创建存储过程：

delimiter //

CREATE PROCEDURE test1(x INT(10),y INT(10))
BEGIN
DECLARE i INT DEFAULT x;
WHILE i< y DO
insert into layout_test VALUES(i,FLOOR(1 + RAND()*100 ));
set i = i +1;
end WHILE;
end //
delimiter;
3. 执行存储过程

CALL test1(1,1000);

4. 生成数据截图如下：

今天的关于在Mysql数据库里通过存储过程实现树形的遍历的分享已经结束，谢谢您的关注，如果想了解更多关于Linux 下mysql通过存储过程实现批量生成记录、mssql server 数据库存储过程转换成mysql数据库(1/2)、MSSQL数据库:存储过程学习_MySQL、mysql 中批量插入数据（存储过程实现）的相关知识，请在本站进行查询。

本文标签：