mysql使用函数，查询所以父id，子id

来源：互联网发布：学而后知不足事例编辑：程序博客网时间：2024/05/08 11:53

mysql使用函数做递归层次查询

最近在做一个从oracle数据库到mysql数据库的移植，遇到一个这样的问题

在Oracle 中我们知道有一个 Hierarchical Queries 通过CONNECT BY 我们可以方便的查了所有当前节点下的所有子节点。但shi，在MySQL的目前版本中还没有对应的函数！！！

换句话来说，想要用mysql实现递归查询，根本做不到！！！

可是经过我数天茶不思饭不想的刻苦琢磨，终于想到了一个合理的，适用于mysql和其他sql的解决方案。

www.2cto.com

方案一出，就秋风扫落叶之势，席卷整个dao层~~~所到之处，所有问题迎刃而解，让所有问题都不再为问题都成为了我这个函数的炮灰而已。。。

话不多说待我把解决方法仔细道来~~~~~

下面是sql脚本，想要运行一下把下边的粘贴复制下来，做一个treenodes.sq直接运行便是。。。

Navicat MySQL Data Transfer

Source Server : mysql_demo3

Source Server Version : 50521

Source Host : localhost:3306

Source Database : test

Target Server Type : MYSQL

Target Server Version : 50521

File Encoding : 65001

www.2cto.com

Date: 2012-09-02 21:16:03

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------

-- Table structure for `treenodes`

-- ----------------------------

DROP TABLE IF EXISTS `treenodes`;

CREATE TABLE `treenodes` (

`id` int(11) NOT NULL,

`nodename` varchar(20) DEFAULT NULL,

`pid` int(11) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------

-- Records of treenodes

-- ----------------------------

INSERT INTO `treenodes` VALUES ('1', 'A', '0');

INSERT INTO `treenodes` VALUES ('2', 'B', '1');

INSERT INTO `treenodes` VALUES ('3', 'C', '1');

INSERT INTO `treenodes` VALUES ('4', 'D', '2');

INSERT INTO `treenodes` VALUES ('5', 'E', '2');

INSERT INTO `treenodes` VALUES ('6', 'F', '3');

INSERT INTO `treenodes` VALUES ('7', 'G', '6');

INSERT INTO `treenodes` VALUES ('8', 'H', '0');

INSERT INTO `treenodes` VALUES ('9', 'I', '8');

INSERT INTO `treenodes` VALUES ('10', 'J', '8');

INSERT INTO `treenodes` VALUES ('11', 'K', '8');

INSERT INTO `treenodes` VALUES ('12', 'L', '9');

INSERT INTO `treenodes` VALUES ('13', 'M', '9');

INSERT INTO `treenodes` VALUES ('14', 'N', '12');

INSERT INTO `treenodes` VALUES ('15', 'O', '12');

INSERT INTO `treenodes` VALUES ('16', 'P', '15');

INSERT INTO `treenodes` VALUES ('17', 'Q', '15');

www.2cto.com

---------------------------------------------------

上边是sql脚本，在执行select * 之后显示的结果集如下所示：

mysql> select * from treenodes;

+----+----------+------+

| id | nodename | pid |

+----+----------+------+

| 1 | A | 0 |

| 2 | B | 1 |

| 3 | C | 1 |

| 4 | D | 2 |

| 5 | E | 2 |

| 6 | F | 3 |

| 7 | G | 6 |

| 8 | H | 0 |

| 9 | I | 8 |

| 10 | J | 8 |

| 11 | K | 8 |

| 12 | L | 9 |

| 13 | M | 9 |

| 14 | N | 12 |

| 15 | O | 12 |

| 16 | P | 15 |

| 17 | Q | 15 |

+----+----------+------+

17 rows in set (0.00 sec)

树形图如下

1:A

+-- 2:B

| +-- 4:D

| +-- 5:E

+-- 3:C

+-- 6:F

+-- 7:G

8:H

+-- 9:I

| +-- 12:L

| | +--14:N

| | +--15:O

| | +--16:P

| | +--17:Q

| +-- 13:M

+-- 10:J

+-- 11:K

--------------------------------------------

如果给你一个这样的table，让你查询根节点为1下的所有节点记录（注意也包括根节点），，肿麽办？？？？？

可能有不少人想到connect by 函数，但是我灰常遗憾的告诉你，咱这儿是mysql！！！

好，客观您勒上眼，，我的解决办法是

利用函数来得到所有子节点号。

闲话少续，看我的解决方法

创建一个function getChildLst, 得到一个由所有子节点号组成的字符串.

mysql> delimiter //

mysql>

mysql> CREATE FUNCTION `getChildLst`(rootId INT)

-> RETURNS varchar(1000)

-> BEGIN

-> DECLARE sTemp VARCHAR(1000);

-> DECLARE sTempChd VARCHAR(1000);

-> SET sTemp = '$';

-> SET sTempChd =cast(rootId as CHAR);

-> WHILE sTempChd is not null DO

-> SET sTemp = concat(sTemp,',',sTempChd);

-> SELECT group_concat(id) INTO sTempChd FROM treeNodes where FIND_IN_SET(pid,sTempChd)>0;

-> END WHILE;

-> RETURN sTemp;

-> END

-> //

Query OK, 0 rows affected (0.00 sec)

mysql>

mysql> delimiter ;

www.2cto.com

使用我们直接利用find_in_set函数配合这个getChildlst来查找

mysql> select getChildLst(1);

+-----------------+

| getChildLst(1) |

+-----------------+

| $,1,2,3,4,5,6,7 |

+-----------------+

1 row in set (0.00 sec)

mysql> select * from treeNodes

-> where FIND_IN_SET(id, getChildLst(1));

+----+----------+------+

| id | nodename | pid |

+----+----------+------+

| 1 | A | 0 |

| 2 | B | 1 |

| 3 | C | 1 |

| 4 | D | 2 |

| 5 | E | 2 |

| 6 | F | 3 |

| 7 | G | 6 |

+----+----------+------+

7 rows in set (0.01 sec)

mysql> select * from treeNodes

-> where FIND_IN_SET(id, getChildLst(3));

+----+----------+------+

| id | nodename | pid |

+----+----------+------+

| 3 | C | 1 |

| 6 | F | 3 |

| 7 | G | 6 |

+----+----------+------+

3 rows in set (0.01 sec)

--------------------------------------------

只要按我的做，百发百中弹无虚发，遇到问题万变不离其宗直接粘贴复制就是。。。

补充：

还可以做嵌套查询：

select id,pid from treeNodes where id in(

select id from treeNodes where FIND_IN_SET(id, getChildLst(3))

);

子查询的结果集是

www.2cto.com

+--------+

----

+-------+

然后经过外层查询就是

id pid

3 1

6 3

6 6

---------

用SQLLog创建函数时需要用DELIMITER提示执行批量执行

查询所有子节点

DELIMITER $$
CREATE FUNCTION `getChildLst`(rootId INT)
RETURNS varchar(1000)
BEGIN
DECLARE sTemp VARCHAR(1000);
DECLARE sTempChd VARCHAR(1000);

SET sTemp = '$';
SET sTempChd =cast(rootId as CHAR);

WHILE sTempChd is not null DO
SET sTemp = concat(sTemp,',',sTempChd); //拼接字符串的(注意有一个为空结果就为空), 用SET sTemp = sTemp+‘,’+sTempChd不行
SELECT group_concat(uuid) INTO sTempChd FROM oll_user where FIND_IN_SET(PARENT_UUID,sTempChd)>0;
END WHILE;
RETURN sTemp;
END$$
DELIMITER ;

查询所有父id

DELIMITER $$
CREATE FUNCTION `getFatherLst`(rootId INT)
RETURNS varchar(1000)
BEGIN
DECLARE sTemp VARCHAR(1000);
DECLARE sTempFat VARCHAR(1000);
DECLARE TempId VARCHAR(1000);

SET sTemp = '$';

SET sTempFat =cast(rootId as CHAR);

WHILE sTempFat is not null and sTempFat != 0 DO
set TempId = (select PARENT_UUID from oll_user where UUID = sTempFat and PARENT_UUID !=0);//原来用into赋值直接死循环 - -！
set sTempFat = TempId;
if(TempId is not null) then
set sTemp = concat(sTemp,',',TempId);
end if;
END WHILE;
RETURN sTemp;
END$$
DELIMITER ;

函数类型

标量函数

常见的标量函数(只对单个值操作返回单个值的函数)

表值函数

返回一个张表

create function tvpoints()
returns table
as
return
(
select *from tb_users
);