先创建单独的数据库(eg:ticket),然后创建一个表:
CREATE TABLE Tickets64 (
id bigint(20) unsigned NOT NULL auto_increment,
stub char(1) NOT NULL default '',
PRIMARY KEY (id),
UNIQUE KEY stub (stub)
) ENGINE=MyISAM
创建一个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
使用我们直接利用find_in_set函数配合这个getChildlst来查找
mysql>select getChildLst(1)
+-----------------+
| getChildLst(1) |
+-----------------+
| $,1,2,3,4,5,6,7 |
+-----------------+
1 row in set (0.00 sec)
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)