下面是一个典型的多层数据结构示意图:相关创建数据语句:
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 * FROM category ORDER BY category_id;在这种数据结构中,各层之间通过字段 parent 来形成邻接表,我们查询某些层级的关系的时候一般都是通过递归的方式,遍历某个层级关系的SQL的查询次数会顺着层级的增加,想想在层级有20的时候,根据某个底层节点取它到顶层节点的查询次数吧。为了解决这个问题,人们想出了嵌套集模型(The Nested Set Model),请看下图:上图依然是表现的与图一相同的层级关系,但是却更换了一种表现形式 下面是新的关系表和数据(关系和数据与之前相同,但是表结构不一样):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);
SELECT * FROM nested_category ORDER BY category_id;这里将 left,right 修改为 lft,rgt因为这两个词在MYSQL中属于关键字 下面我们将插入的数据标识在图上: 同样,我们将数据标识在原来的结构上:
怎么样,是不是很明确了下面使我自己标定一种形式,方便理解[1
[2
[3 4]
[5 6]
[7 8]
9]
[10
[11
[12 13]
14]
[15 16]
[17 18]
19]
20]===========================================================
以上是MySQL 所推荐的左右值法(毗邻目录法、预排序历遍法)我的想法是根据category的内容,用一个存储过程来计算,把结果算出来放到nested_category中。请大家指点一下,看有没有好一点的处理方法。
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 * FROM category ORDER BY category_id;在这种数据结构中,各层之间通过字段 parent 来形成邻接表,我们查询某些层级的关系的时候一般都是通过递归的方式,遍历某个层级关系的SQL的查询次数会顺着层级的增加,想想在层级有20的时候,根据某个底层节点取它到顶层节点的查询次数吧。为了解决这个问题,人们想出了嵌套集模型(The Nested Set Model),请看下图:上图依然是表现的与图一相同的层级关系,但是却更换了一种表现形式 下面是新的关系表和数据(关系和数据与之前相同,但是表结构不一样):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);
SELECT * FROM nested_category ORDER BY category_id;这里将 left,right 修改为 lft,rgt因为这两个词在MYSQL中属于关键字 下面我们将插入的数据标识在图上: 同样,我们将数据标识在原来的结构上:
怎么样,是不是很明确了下面使我自己标定一种形式,方便理解[1
[2
[3 4]
[5 6]
[7 8]
9]
[10
[11
[12 13]
14]
[15 16]
[17 18]
19]
20]===========================================================
以上是MySQL 所推荐的左右值法(毗邻目录法、预排序历遍法)我的想法是根据category的内容,用一个存储过程来计算,把结果算出来放到nested_category中。请大家指点一下,看有没有好一点的处理方法。
解决方案 »
- 大型网站用户如何分表,例如新浪微薄?
- 把mysql生成.sql导入到sql server
- 如何获得点击率前十的文章,效率要高
- 如何查询一周用户消费数
- mysql数据库导入问题
- 查询如何显示在MSFlexGrid网格控件中的问题。救急!
- 在mysql中实现匹配查询出现的问题(高手进,在线等)
- 如何配置mysql才能支持中文按拼音排序?(mysql4.0)
- MySQL Connector/J 3.0 支持 MySQL4.0.12吗?
- 经过一段时间本人修改了sendmail、POP3、IMAP4的源代码,把用户认证该为了数据库,数据库采用的MYSQL、并虚拟了整个系统用户来存放邮件内容,有意者请与我联系[email protected]
- 请教mysql 外键 实现 A表删除一条数据,联动的B表更新记录
- 如何更改mysql数据库表中的enum值?
MySQL中进行树状所有子节点的查询
我多建了一个和category 一样结构的category_temp表。CREATE PROCEDURE ADJ_NEST AS
DECLARE @COUNTER INT
DECLARE @MAX_COUNTER INT
DECLARE @CURRENT_TOP INT
DELETE FROM CATEGORY_TEMP
INSERT INTO CATEGORY_TEMP
SELECT * FROM CATEGORY
DELETE FROM NESTED_CATEGORY INSERT INTO NESTED_CATEGORY(NAME,CATEGORY_ID,LFT,RGT)
SELECT 1, CATEGORY_ID, 1, NULL
FROM CATEGORY_TEMP
WHERE PARENT IS NULL
DELETE FROM CATEGORY_TEMP
WHERE PARENT IS NULL
SET @COUNTER = 2
SET @MAX_COUNTER = 2 * (SELECT COUNT(*) FROM CATEGORY_TEMP)+2
SET @CURRENT_TOP = 1
WHILE @COUNTER <= @MAX_COUNTER
BEGIN
IF EXISTS (SELECT *
FROM NESTED_CATEGORY AS S1, CATEGORY_TEMP AS T1
WHERE S1.CATEGORY_ID = T1.PARENT
AND S1.NAME = @CURRENT_TOP)
BEGIN
INSERT INTO NESTED_CATEGORY(NAME,CATEGORY_ID,LFT,RGT)
SELECT (@CURRENT_TOP + 1), MIN(T1.CATEGORY_ID), @COUNTER, NULL
FROM NESTED_CATEGORY AS S1, CATEGORY_TEMP AS T1
WHERE S1.CATEGORY_ID = T1.PARENT
AND S1.NAME = @CURRENT_TOP
DELETE FROM CATEGORY_TEMP
WHERE CATEGORY_ID = (SELECT CATEGORY_ID
FROM NESTED_CATEGORY
WHERE NAME = @CURRENT_TOP + 1)
SET @COUNTER = @COUNTER + 1
SET @CURRENT_TOP = @CURRENT_TOP + 1
END
ELSE
BEGIN
UPDATE NESTED_CATEGORY
SET RGT = @COUNTER,
NAME = -NAME
WHERE NAME = @CURRENT_TOP
SET @COUNTER = @COUNTER + 1
SET @CURRENT_TOP = @CURRENT_TOP - 1
END
END
GO