mysql-level query in MySQL for all child nodes of the query tree

2010-12-09  来源:本站原创  分类:Database  人气:111 

1, to build a function

2, with the statement query

First step

CREATE FUNCTION `getChildLst` (rootId INT) / / Note the single quotes red
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 sinykk_url_sort where FIND_IN_SET (parent_id, sTempChd)> 0;
END WHILE;
RETURN sTemp;
END

The second step

select * from sinykk_url_sort where FIND_IN_SET (id, getChildLst (1273));

相关文章
  • MySQL, for all child nodes of the query tree 2010-07-20

    In Oracle, we know that there is a Hierarchical Queries by CONNECT BY can easily search all of the current node to all child nodes. Regrettably, in the current version of MySQL do not correspond to the function. In MySQL, is limited if the level of,

  • MySQL in mysql query for the tree-level all the child nodes of the query 2010-12-09

    1, the first building function 2, with the statement to query First step CREATE FUNCTION `getChildLst` (rootId INT) / / note single quotes in red RETURNS varchar (1000) BEGIN DECLARE sTemp VARCHAR (1000); DECLARE sTempChd VARCHAR (1000); SET sTemp =

  • mysql-level query in MySQL for all child nodes of the query tree 2010-12-09

    1, to build a function 2, with the statement query First step CREATE FUNCTION `getChildLst` (rootId INT) / / Note the single quotes red RETURNS varchar (1000) BEGIN DECLARE sTemp VARCHAR (1000); DECLARE sTempChd VARCHAR (1000); SET sTemp = '$'; SET s

  • Loading child nodes on demand using jsTree 2010-06-01

    This study includes: 1, jsTree basic use; Second, on-demand loading child nodes, that each time a node to start only when the server load to its child nodes. jsTree is a tree based on jQuery plug-ins, the project home page: http://www.jstree.com/ Thi

  • MYSQL query the parent node of all nodes, sorted by level of stored procedures 2011-10-12

    Query the parent node of all nodes, sorted by level of stored procedures Parent-child relationship table structure: `Parentnode` int (11), - the parent node `Node` int (11), - node `Isparent` int (11) - whether the parent node drop PROCEDURE if EXIST

  • MySQL level partition table summarizes the actual operation 2011-05-24

    This article summarizes a study of this period the level of partition table summarizes MySQL, partition table lists the relevant operations. Read many articles on the Internet, are too idea, focus attention on the advantages of introducing the partit

  • The design of efficient and reasonable query on MySQL 2009-07-30

    The design of efficient and reasonable query on MySQL Release Time: 2006.11.23 04:36 Source: Microsoft technical communities Author: yuanye Database system is the core of management information systems, database-based online transaction processing (O

  • Open view slow query log mysql 2011-05-23

    # Slowlog config slow_query_log = 1 long_query_time = 2 log_output = FILE slow_query_log_file = / data1/mysqldata/mysql5.1.57/logs/slowlog.log # Error logs log-error = / data1/mysqldata/mysql5.1.57/logs/error.log In mysql5.1 previous versions, only l

  • mysql query day, queries a week, one month's data query 2011-06-21

    mysql query day, queries a week, one month's data query queries a day: select * from table where to_days (column_time) = to_days (now ()); select * from table where date (column_time) = curdate (); Query week: select * from table where DATE_SUB (CURD

  • mysql operation tree - get a node to obtain all the child nodes and all parent nodes of a node 2010-04-16

    Read the title will know what I have to say, this is the problems encountered at work. A lot of online search, only to find the node to obtain all the child nodes of the statements below, the following is the code DELIMITER $$ DROP FUNCTION IF EXISTS

  • linux downloading java, resin, tomcat, mysql, at startup tomcat, mysql 2010-04-26

    linux environment 1.java jdk version 1.5.0_09 (also set environment variables under) 2.resin container version of the resin-pro-3.0.18 3.mysql database version of the mysql-5.0 or above can 1. Java Installation 1) # Mkdir-p / usr / local / soft / # C

  • [Change] Log mysql appear / var / lib / mysql / mysql.sock does not exist, and mysql dead but subsys locked problem 2010-06-01

    Problem description: 1.mysql installation is complete, use service mysqld restart Always there stop mysqld service fails. 2. Using mysql-uroot-p log appears not find / var / lib / mysql / mysql.sock problem. 3. To use service mysqld status appear mys

  • MySQL: Grant syntax Xiangjie (MySQL 5.X) 2010-08-31

    MySQL: grant grammar Xiangjie (MySQL 5.X) This instance, running on MySQL 5.0 and above. MySQL user permissions to give the simple order form can be summarized as: grant Permissions on database objects to Users 1, grant general data users, query, ins

  • MySQL: Grant Detailed syntax (MySQL 5.X) 2010-08-31

    MySQL: grant grammar Detailed (MySQL 5.X) This instance, running on MySQL 5.0 and above. MySQL user permissions to give a simple command format can be summarized as: grant Permissions on database objects to User A, grant general data users, query, in

  • mysql replication basic principles, mysql master-slave configuration principle 2010-10-28

    1, the replication process Mysql replication (Replication) is an asynchronous replication, from a Mysql instace (called Master) to another Mysql instance (call it Slave). Entire copy operation completed mainly by three processes, two processes in the

  • [Transfer] mysql replication basic principles, mysql master-slave configuration principle 2010-11-13

    mysql replication basic principles, mysql master-slave configuration principle 1, the replication process Mysql replication (Replication) is an asynchronous replication, from a Mysql instace (called Master) to another Mysql instance (call it Slave).

  • MySQL: MySQL date data sample. MySQL example, when the use of summary 2011-01-13

    MySQL Date Example: Date pattern, accounting for storage space, the date scale than force. Date of sample storage space the size of the date of the date pattern ------------ --------- --------------------- -------- --------------------------------- d

  • MySQL WorkBench管理操作MySQL教程 2014-10-29

    MySQL Workbench提供DBAs和developers一个集成工具环境,方便管理mysql数据库,这里简单介绍下MySQL Workbench使用方法,需要的朋友可以参考下 一 MySQL Workbench MySQL Workbench提供DBAs和developers一个集成工具环境: 1)数据库设计和建模 2)SQL开发(取代原来的MySQL Query Browser) 3)数据库管理(取代原来的MySQL Administrator) 二 MySQL Workbench 下

  • MYSQL的binary解决mysql数据大小写敏感问题的方法 2015-01-21

    BINARY不是函数,是类型转换运算符,它用来强制它后面的字符串为一个二进制字符串,可以理解为在字符串比较的时候区分大小写 mysql> select binary 'ABCD'='abcd' COM1, 'ABCD'='abcd' COM2; +--------+-----------+ | COM1 | COM2 | +--------+-----------+ | 0 | 1 | +---------+-----------+ 1 row in set (0.00 sec) (仅仅有些而已

  • (MySQL notes) set up MySQL server 2010-06-07

    (MySQL notes) set up MySQL server On a: (MySQL Notes) MySQL What is this? To use MySQL, the MySQL server must be built up. Mysql is a database system, which includes the database server, and there is a database management system, database server mana