简化递归mysql查询
与给定的MySQL表
CREATE TABLE taxons (
id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
parent_id int,
name varchar(255)
);该表有以下条目
INSERT INTO taxons (parent_id, name) VALUES (NULL, "Baby & Kleinkind");
INSERT INTO taxons (parent_id, name) VALUES (1, "Babysicherheit");
INSERT INTO taxons (parent_id, name) VALUES (2, "Babysicherungen & Schutzvorrichtungen"); 水平的数量是无限的。
我想通过向下遍历树来选择一个只有一个查询的实体(一个分类单元)。
目前我的查询是手工制作的
SELECT *
FROM taxons
WHERE name = "Babysicherungen & Schutzvorrichtungen"
AND parent_id = (
SELECT id
FROM taxons
WHERE name = "Babysicherheit"
AND parent_id = (
SELECT id
FROM taxons
WHERE name = "Baby & Kleinkind"
)
);对于这个查询有什么更好的解决方案吗？
请参阅数据库小提琴以获得更好的理解：https://www.db-fiddle.com/f/mvMCGdNgjCa9PeNKbbzmRL/0
MySQL v5.7
回答 2
Stack Overflow用户
发布于 2019-07-22 15:40:53
如果我正确地读取了您的查询，您希望查询顶级记录，但只查询所有子行都存在的记录。例如，如果“婴儿与克莱因金德”不存在某些ID，那么我们就不需要这个ID。
如果是这样的话，您可以使用动态SQL构建一个查询，该查询会自动检查每个父-子关系。如果你有很多分类单元，这可以节省你的时间。
这样做的目的是构建一个如下所示的查询(假设有五个分类单元)：
SELECT t1.*
FROM taxons t1
INNER JOIN taxons t2
ON t1.id = t2.parent_id
INNER JOIN taxons t3
ON t2.id = t3.parent_id
INNER JOIN taxons t4
ON t3.id = t4.parent_id
INNER JOIN taxons t5
ON t4.id = t5.parent_id
WHERE NAME = "Babysicherungen & Schutzvorrichtungen" 要构建它，您可以使用一个循环：
DECLARE totalLevels INT;
DECLARE num INT;
DECLARE queryString VARCHAR(255);
SET @totalLevels = 5
SET @num = 2;
SET @str = 'SELECT t1.*
FROM taxons t1';
build_query: LOOP
IF @num > @totalLevels THEN
LEAVE build_query;
END IF;
SET @queryString = CONCAT(@queryString,
' INNER JOIN taxons t', num, ' ON t', num-1, '.id = t', num, '.parent_id'
);
SET @num = @num + 1;
ITERATE build_query;
END LOOP;
SET @queryString = CONCAT(@queryString, ' WHERE NAME = "Babysicherungen & Schutzvorrichtungen"')您可以使用totalLevels来设置要遍历的分类单元的总数。
我还没有在那个循环中专门测试代码，但是我过去也做过类似的查询，它应该可以工作，或者只需要做一些小的修改就可以了。
编辑:我忘了添加，一旦创建了查询字符串，就需要执行它。要了解这一点，请参见此处：可以在MySQL中执行字符串吗？
Stack Overflow用户
发布于 2019-07-22 15:35:16
表达这一点的一个更简洁的方法是使用联接：
select t1.* from taxons t1
join taxons t2 on t1.name = "Babysicherungen & Schutzvorrichtungen" and t1.parent_id = t2.id and t2.name = "Babysicherheit"
join taxons t3 on t2.parent_id = t3.id and t3.name="Baby & Kleinkind"
;考虑到您定义的键(或缺少这些键)，它也会更高效。请参阅对SQL和上面的两个解释分析：
模式(MySQL v5.7)
CREATE TABLE taxons (
id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
parent_id int,
name varchar(255)
);
INSERT INTO taxons (parent_id, name) VALUES (NULL, "Baby & Kleinkind");
INSERT INTO taxons (parent_id, name) VALUES (1, "Babysicherheit");
INSERT INTO taxons (parent_id, name) VALUES (2, "Babysicherungen & Schutzvorrichtungen"); 查询#1
EXPLAIN SELECT *
FROM taxons
WHERE name = "Babysicherungen & Schutzvorrichtungen"
AND parent_id = (
SELECT id
FROM taxons
WHERE name = "Babysicherheit"
AND parent_id = (
SELECT id
FROM taxons
WHERE name = "Baby & Kleinkind"
)
);( select_type )\x{e76f}\x{e76f}
---- | -------- | ----------- |
| 1 | PRIMARY | taxons | | ALL | | | | | 3 | 33.33 | Using where |
| 2 | SUBQUERY | taxons | | ALL | | | | | 3 | 33.33 | Using where |
| 3 | SUBQUERY | taxons | | ALL | | | | | 3 | 33.33 | Using where |
---模式(MySQL v5.7)
CREATE TABLE taxons (
id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
parent_id int,
name varchar(255)
);
INSERT INTO taxons (parent_id, name) VALUES (NULL, "Baby & Kleinkind");
INSERT INTO taxons (parent_id, name) VALUES (1, "Babysicherheit");
INSERT INTO taxons (parent_id, name) VALUES (2, "Babysicherungen & Schutzvorrichtungen"); 查询#1
EXPLAIN select t1.* from taxons t1
join taxons t2 on t1.name = "Babysicherungen & Schutzvorrichtungen" and t1.parent_id = t2.id and t2.name = "Babysicherheit"
join taxons t3 on t2.parent_id = t3.id and t3.name="Baby & Kleinkind"
;
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
| --- | ----------- | ----- | ---------- | ------ | ------------- | ------- | ------- | ----------------- | ---- | -------- | ----------- |
| 1 | SIMPLE | t1 | | ALL | | | | | 3 | 33.33 | Using where |
| 1 | SIMPLE | t2 | | eq_ref | PRIMARY | PRIMARY | 4 | test.t1.parent_id | 1 | 33.33 | Using where |
| 1 | SIMPLE | t3 | | eq_ref | PRIMARY | PRIMARY | 4 | test.t2.parent_id | 1 | 33.33 | Using where |https://stackoverflow.com/questions/57143756
复制相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号