mysql链表查询语法_mysql查询链表

查询通过迭代t_list表(最后一行)来工作.对于此表中的每一行,SELECT子句中的子查询重新查询表,搜索当前行的子项(WHERE parent = _parent – 但_parent是@r的别名).在每次迭代时,子节点的id被分配给@r变量.

要添加边界,这种变化应该可以解决问题：

SELECT * FROM (

SELECT

@r AS _parent,

@r := (

SELECT id

FROM t_list

WHERE

( @c = 0 AND _parent IS NULL AND parent IS NULL ) -- special case if the first item is the root

OR (parent = _parent)

) AS id,

@c := @c + 1 AS rank

FROM (

SELECT @c := 0, @r := parent FROM t_list WHERE id = @start

) AS ini,

(

SELECT id FROM t_list LIMIT @limit

) AS lim

) AS tmp WHERE id IS NOT NULL;

将@start和@limit替换为第一个项的id和要检索的最大项数.请test it here.

使用RDBMS对这样的数据结构建模可能完全是一个坏主意.为什么不使用“索引”列？获取列表然后变为即时：

SELECT * FROM list ORDER BY index_column ASC;

也许你的列表是经常更改的,但是这样的查询应该相当快,除非列表变得非常大：

-- insert an element at position X

UPDATE list SET index_column = index_column +1 WHERE index_column > X ORDER BY index_column DESC;

INSERT INTO list VALUE (some_value, X);

-- delete an element at position X

DELETE FROM list WHERE index_column = X;

UPDATE list SET index_column = index_column -1 WHERE index_column > X ORDER BY index_column ASC;