MySQL17.分层结构

邻接列表

演示

创建语句

CREATE TABLE category ( category_id INT AUTO_INCREMENT PRIMARY KEY, NAME VARCHAR ( 20 ) NOT NULL, parent INT DEFAULT NULL );

INSERT INTO category
VALUES
	( 1, 'ELECTRONICS', NULL ),
	( 2, 'TELEVISIONS', 1 ),
	( 3, 'TUBE', 2 ),
	( 4, 'LCD', 2 ),
	( 5, 'PLASMA', 2 ),
	( 6, 'PORTABLE ELECTRONICS', 1 ),
	( 7, 'MP3 PLAYERS', 6 ),
	( 8, 'FLASH', 7 ),
	( 9, 'CD PLAYERS', 6 ),
	( 10, '2 WAY RADIOS', 6 );

查询所有并标识叶节点

SELECT
	t1.*,
	IF(t2.total IS NULL, '1', '0') AS 'leaf'
FROM
	category t1
	LEFT JOIN ( SELECT COUNT(1) AS 'total', parent FROM category GROUP BY parent) t2 ON t1.category_id = t2.parent

嵌套集模型

演示

如何来确定左右值？

创建语句

CREATE TABLE nested_category ( category_id INT AUTO_INCREMENT PRIMARY KEY, NAME VARCHAR ( 20 ) NOT NULL, lft INT NOT NULL, rgt INT NOT NULL );
INSERT INTO nested_category
VALUES
	( 1, 'ELECTRONICS', 1, 20 ),
	( 2, 'TELEVISIONS', 2, 9 ),
	( 3, 'TUBE', 3, 4 ),
	( 4, 'LCD', 5, 6 ),
	( 5, 'PLASMA', 7, 8 ),
	( 6, 'PORTABLE ELECTRONICS', 10, 19 ),
	( 7, 'MP3 PLAYERS', 11, 14 ),
	( 8, 'FLASH', 12, 13 ),
	( 9, 'CD PLAYERS', 15, 16 ),
	( 10, '2 WAY RADIOS', 17, 18 );

查询所有并标识叶节点

判断是否叶节点的依据是：lef == rgt - 1

SELECT
	node.*,
IF
	( node.lft + 1 = node.rgt, 1, 0 ) AS 'leaf' 
FROM
	nested_category AS node,
	nested_category AS parent 
WHERE
	node.lft BETWEEN parent.lft 
	AND parent.rgt 
	AND parent.NAME = 'ELECTRONICS' 
ORDER BY
	node.lft;

检索父级路径

SELECT
	*
FROM
	nested_category node
WHERE
	lft <= 12 AND rgt >= 12

检索深度

SELECT
	COUNT(1)
FROM
	nested_category node
WHERE
	lft <= 12 AND rgt >= 12

添加节点

思路：确定元素的左右大小，大于该元素的左右节点 均＋2

LOCK TABLE nested_category WRITE;

SELECT
	@myRight := rgt 
FROM
	nested_category 
WHERE
	NAME = 'TELEVISIONS';

UPDATE nested_category 
SET rgt = rgt + 2 
WHERE
	rgt >
@myRight;

UPDATE nested_category 
SET lft = lft + 2 
WHERE
	lft >
@myRight;

INSERT INTO nested_category ( NAME, lft, rgt )
VALUES
	( 'GAME CONSOLES', @myRight + 1, @myRight + 2 );

UNLOCK TABLES