mysql递归查询1418_sql跟mysql递归查询子节点

sql和mysql递归查询子节点

//SQL SqlServer:CTE函数处理递归(WITH语法) cte可以其他名字 注意匹配(所有cte全部替换) with cte as ( select a.typeid,a.superid,a.typename from ctype a where typeid=1 union all select k.typeid,k.superid,k.typename from ctype k inner join cte c on c.typeid = k.superid )select * from cte //MYSQL 放到命令行执行 CREATE FUNCTION `getChildDeptLst`(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 department where FIND_IN_SET(parentId,sTempChd)>0; END WHILE; RETURN sTemp; END select getChildLst(1); select * from departmentg where FIND_IN_SET(id, getChildDeptLst(1)); // //查询子项父id SELECT T2.id, T2 .name FROM ( SELECT @r AS _id, (SELECT @r := parentId FROM department WHERE id = _id) AS parentId, @l := @l + 1 AS lvl FROM (SELECT @r := 30, @l := 0) vars, department h WHERE @r <> 0) T1 JOIN department T2 ON T1._id = T2.id ORDER BY T1.lvl DESC

1

//SQL SqlServer:CTE函数处理递归(WITH语法)  cte可以其他名字 注意匹配(所有cte全部替换) with cte as   (   select a.typeid,a.superid,a.typename from ctype a where typeid=1  union all    select k.typeid,k.superid,k.typename  from ctype k inner join cte c on c.typeid = k.superid   )select * from cte  //MYSQL 放到命令行执行 CREATE FUNCTION `getChildDeptLst`(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 department  where FIND_IN_SET(parentId,sTempChd)>0;     END WHILE;   RETURN sTemp; END  select getChildLst(1);  select * from departmentg   where FIND_IN_SET(id, getChildDeptLst(1)); // //查询子项父id SELECT T2.id, T2 .name  FROM (  SELECT   @r AS _id,  (SELECT @r := parentId FROM department WHERE id = _id) AS parentId,  @l := @l + 1 AS lvl  FROM  (SELECT @r := 30, @l := 0) vars,  department h  WHERE @r <> 0) T1  JOIN department T2  ON T1._id = T2.id  ORDER BY T1.lvl DESC

欢迎大家阅读《sql跟mysql递归查询子节点》,跪求各位点评，by 搞代码

执行查询或者命令行报错

创建function时

出错信息：

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

原因：

这是我们开启了bin-log, 我们就必须指定我们的函数是否是

1 DETERMINISTIC 不确定的

2 NO SQL 没有SQl语句，当然也不会修改数据

3 READS SQL DATA 只是读取数据，当然也不会修改数据

4 MODIFIES SQL DATA 要修改数据

5 CONTAINS SQL 包含了SQL语句

其中在function里面，只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。如果我们开启了 bin-log, 我们就必须为我们的function指定一个参数。

在MySQL中创建函数时出现这种错误的解决方法：

set global log_bin_trust_function_creators=TRUE;

下面部分摘自

http://hi.baidu.com/alovn/blog/item/590412157d0c0c04972b43ce.html

原创文章，转载请注明： 转载自搞代码

微信 赏一包辣条吧~

支付宝 赏一听可乐吧~