mysql如何设计多级分销上下级查询的数据结构？

你说的分销上下级查询其实就是类似于一个栏目类目树结构。

我们以一个栏目树形结构来为例分析一下如果对无限栏目进行添加查询修改删除。

CREATE TABLE category (
 id int(10) unsigned NOT NULL AUTO_INCREMENT,
 title varchar(255) NOT NULL,
 parent_id int(10) unsigned DEFAULT NULL,
 PRIMARY KEY (id),
 FOREIGN KEY (parent_id) REFERENCES category (id)
 ON DELETE CASCADE ON UPDATE CASCADE
);

插入数据

INSERT INTO category(title,parent_id)
VALUES('电器',NULL);

INSERT INTO category(title,parent_id)
VALUES('电灯',1);

INSERT INTO category(title,parent_id)
VALUES('电脑',2);
INSERT INTO category(title,parent_id)
VALUES('手机',2);

INSERT INTO category(title,parent_id)
VALUES('电视',1);

INSERT INTO category(title,parent_id)
VALUES('智能手机',4);

INSERT INTO category(title,parent_id)
VALUES('andriod手机',4);
INSERT INTO category(title,parent_id)
VALUES('苹果手机',4);
INSERT INTO category(title,parent_id)
VALUES('鸿蒙手机',4);

寻找根节点

SELECT id, title FROM category WHERE parent_id IS NULL;

查找节点的直接子节点

SELECT id, title FROM category WHERE parent_id = 1;

查找叶节点

SELECT
 c1.id, c1.title
FROM
 category c1
LEFT JOIN
 category c2 ON c2.parent_id = c1.id
WHERE
c2.id IS NULL;

查询整棵树

WITH RECURSIVE category_path (id, title, path) AS
(
SELECT id, title, title as path
FROM category
WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.title, CONCAT(cp.path, ' > ', c.title)
FROM category_path AS cp JOIN category AS c
ON cp.id = c.parent_id
)
SELECT * FROM category_path
ORDER BY path;

查询子树

WITH RECURSIVE category_path (id, title, path) AS
(
SELECT id, title, title as path
FROM category
WHERE parent_id = 4
UNION ALL
SELECT c.id, c.title, CONCAT(cp.path, ' > ', c.title)
FROM category_path AS cp JOIN category AS c
ON cp.id = c.parent_id
)
SELECT * FROM category_path
ORDER BY path;

查询单个路径

WITH RECURSIVE category_path (id, title, parent_id) AS
(
SELECT id, title, parent_id
FROM category
WHERE id = 10 -- child node
UNION ALL
SELECT c.id, c.title, c.parent_id
FROM category_path AS cp JOIN category AS c
ON cp.parent_id = c.id
)
SELECT * FROM category_path;

计算每个节点的级别

WITH RECURSIVE category_path (id, title, lvl) AS
(
SELECT id, title, 0 lvl
FROM category
WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.title,cp.lvl + 1
FROM category_path AS cp JOIN category AS c
ON cp.id = c.parent_id
)
SELECT * FROM category_path
ORDER BY lvl;

删除节点及其后代

例如，要删除手机节点及其子节点 ，请使用以下语句：

DELETE FROM category
WHERE
id =4;

删除节点并提升其后代

首先，parent_id将节点的直接子节点的更新为id新父节点的 。

然后，删除节点。

UPDATE category
SET
parent_id =2 
WHERE
parent_id = 4; 

DELETE FROM category
WHERE
id =4;

BEGIN;
UPDATE category
SET
parent_id =2
WHERE
parent_id = 4;

DELETE FROM category
WHERE
id = 4;

COMMIT;

移动子树

UPDATE category
SET
parent_id = 2
WHERE
id = 6;

可以使用xml数据存入mysql中，取出来后进行增删改查操作后再存入数据库