계층적 데이터에 대한 재귀 MySQL 쿼리 생성

Question

아래와 같은 MySQL 테이블이 있습니다.

id	이름	parent_id
19	카테고리 1	0
20	카테고리 2	19
21	카테고리 3	20
22	카테고리 4	21
...	...	...

이제, ID [예: id=19]만 제공하고 모든 하위 ID를 가져와야 하는 MySQL 쿼리가 필요합니다. [즉, 결과에는 ID '20,21,22'가 있어야 합니다....

어린이의 계층 구조는 알 수 없습니다. 다를 수 있습니다...

저는 for 루프를 사용하여 이 작업을 수행하는 방법을 알고 있습니다... 그런데 단일 MySQL 쿼리를 사용하여 동일한 결과를 어떻게 얻을 수 있습니까?

Answer 1

MySQL 8+의 경우: 재귀적 使用 구문을 사용하세요.
MySQL 5.x의 경우: 인라인 변수, 경로 ID 또는 자체 조인을 사용합니다.

MySQL 8+

으아아아

parent_id = 19 中指定的值应设置为您要选择其所有后代的父级的 id.

MySQL 5.x

공통 테이블 표현식을 지원하지 않는 MySQL 버전(최대 버전 5.7)의 경우 다음 쿼리를 사용하여 이를 달성할 수 있습니다.

으아아아

이것은 바이올린입니다.

여기, @pv := '19' 中指定的值应设置为您要选择其所有后代的父级的 id.

이 방법은 부모에게 자녀가 여러 명 있는 경우에도 적용됩니다. 그러나 각 레코드는 parent_id < id 조건을 충족해야 하며, 그렇지 않으면 결과가 불완전합니다.

쿼리 내 변수 할당

이 쿼리는 특정 MySQL 구문을 사용합니다. 즉, 실행 중에 변수가 할당되고 수정됩니다. 실행 순서에 대해 몇 가지 가정을 했습니다.

• from 子句。这就是 @pv가 초기화되는 위치를 먼저 평가합니다.
• from 别名检索的顺序对每条记录评估 where 子句。因此，这里设置的条件仅包括父级已被识别为位于后代树中的记录（主要父级的所有后代都将逐步添加到 @pv에서 팔로우하세요).
• 이 where 子句中的条件按顺序求值，一旦总结果确定，求值就会中断。因此，第二个条件必须位于第二位，因为它将 id 添加到父列表中，并且只有在 id 通过第一个条件时才会发生这种情况。调用 length 函数只是为了确保此条件始终为真，即使 pv 문자열은 어떤 이유로든 잘못된 값을 생성합니다.

대체로 이러한 가정은 너무 위험해서 의존할 수 없습니다. 문서경고:

따라서 위의 쿼리와 일치하더라도 평가 순서는 여전히 변경될 수 있습니다. 예를 들어 조건을 추가하거나 이 쿼리를 더 큰 쿼리 내의 뷰 또는 하위 쿼리로 사용하는 경우입니다. 이는 향후 MySQL 버전 에서 제거될 "기능" 입니다:

위에서 언급했듯이 MySQL 8.0부터는 재귀적 with 구문을 사용해야 합니다.

효율성

매우 큰 데이터 세트의 경우 find_in_set 작업이 목록에서 숫자를 찾는 가장 이상적인 방법이 아니며 반환된 레코드 수를 일치시키는 경우에도 확실히 아니기 때문에 이 솔루션은 느릴 수 있습니다.

대안 1:使用递归，连接

점점 더 많은 데이터베이스가 SQL:1999 ISO 표준 WITH [RECURSIVE]递归查询的 구문 을 구현합니다(예: Postgres 8.4+ , SQL Server 2005+, DB2, Oracle 11gR2+, SQLite 3.8.4+ , 파이어버드 2.1 +, H2, HyperSQL 2.1.0+, Teradata, MariaDB 10.2.2+). 버전 8.0부터 MySQL도 이를 지원합니다. 사용할 구문은 이 답변의 상단을 참조하세요.

일부 데이터베이스에는 Oracle, DB2, Informix과 같은 계층적 조회를 위한 대체 비표준 구문이 있습니다. CUBRID 및 기타 데이터베이스

.

MySQL 버전 5.7은 이러한 기능을 제공하지 않습니다. 데이터베이스 엔진이 이 구문을 제공하거나 이 구문을 제공하는 데이터베이스 엔진으로 마이그레이션할 수 있는 경우 이는 의심할 여지 없이 최선의 선택입니다. 그렇지 않은 경우 다음 대안을 고려하십시오.

대안 2: 경로 스타일 식별자

계층적 정보(경로)가 포함된 id 값을 지정하면 작업이 훨씬 쉬워집니다. 예를 들어, 귀하의 경우 다음과 같을 수 있습니다:

ID	이름
19	카테고리 1
19/1	카테고리 2
1/19	카테고리 3
19/1/1/1	카테고리 4

그러면 选择가 다음과 같이 보일 것입니다:

으아아아

대안 3: 자체 조인 반복

계층 트리 깊이의 상한을 알고 있는 경우 다음과 같은 표준 sql 쿼리를 사용할 수 있습니다.

으아아아

이것을 보세요 바이올린

where 조건은 검색하려는 부모의 자손을 지정합니다. 필요에 따라 이 쿼리를 더 많은 수준으로 확장할 수 있습니다.