MySQL의 재귀 쿼리로 계층 적 데이터를 효율적으로 탐색하는 방법은 무엇입니까?

MySQL 계층적 데이터 탐색 및 재귀 쿼리

MySQL 데이터베이스에서 계층적으로 구조화된 데이터를 처리할 때 다양한 수준의 데이터를 효율적으로 탐색하고 검색하는 것이 어려울 수 있습니다. 이 기사에서는 MySQL에서 재귀 쿼리를 생성하는 포괄적인 접근 방식을 소개하고 최신 버전과 레거시 버전 모두에 대한 솔루션을 제공합니다.

MySQL 8.0의 재귀 쿼리

MySQL 8.0에는 강력한 WITH RECURSIVE 구문이 도입되어 간결하고 효율적인 재귀 쿼리를 작성할 수 있습니다. 예:

<code class="language-sql">WITH RECURSIVE cte (id, name, parent_id) AS (
  SELECT id, name, parent_id
  FROM products
  WHERE parent_id = 19
  UNION ALL
  SELECT p.id, p.name, p.parent_id
  FROM products p
  INNER JOIN cte ON cte.id = p.parent_id
)
SELECT * FROM cte;</code>

이 쿼리에서 cte이라는 공통 테이블 표현식(CTE)은 지정된 products(여기서는 19)에서 시작하여 parent_id 테이블을 재귀적으로 순회하고 해당 하위 노드를 모두 검색합니다.

MySQL 5.x에 대한 재귀 쿼리

MySQL 8.0 이전에는 직접 재귀 쿼리가 지원되지 않았습니다. 하지만 취할 수 있는 대안은 두 가지가 있습니다.

변수 할당:

<code class="language-sql">SELECT id, name, parent_id 
FROM (SELECT * FROM products
      ORDER BY parent_id, id) products_sorted,
      (SELECT @pv := '19') initialisation
WHERE FIND_IN_SET(parent_id, @pv)
AND LENGTH(@pv := CONCAT(@pv, ',', id));</code>

이 복잡한 쿼리는 쿼리 자체 내에서 변수 할당을 사용합니다. 제공된 parent_id(이 경우 19)을 기반으로 하위 노드를 반복적으로 식별합니다. 그러나 이 접근 방식은 대규모 데이터 세트에서 성능 문제가 발생할 수 있습니다.

경로 스타일 식별자:

경로 스타일 식별자와 함께 트리형 ID 구조를 사용하면 재귀 쿼리가 단순화됩니다. 예:

<code class="language-sql">CREATE TABLE products (id VARCHAR(255) PRIMARY KEY, name VARCHAR(255));
INSERT INTO products (id, name) VALUES ('19', 'category1');
INSERT INTO products (id, name) VALUES ('19/1', 'category2');
INSERT INTO products (id, name) VALUES ('19/1/1', 'category3');
INSERT INTO products (id, name) VALUES ('19/1/1/1', 'category4');</code>

이 접근 방식을 사용하면 와일드카드를 사용하여 효율적인 계층적 순회가 가능합니다.

<code class="language-sql">SELECT * FROM products WHERE id LIKE '19%';</code>

요약

MySQL에서 재귀 쿼리를 위해 선택하는 방법은 사용되는 특정 버전과 데이터 세트의 크기에 따라 다릅니다. 최신 MySQL 버전의 경우 WITH RECURSIVE 구문은 우아하고 효율적인 솔루션을 제공합니다. 레거시 버전의 경우 위에 설명된 대안을 다양한 성능으로 사용할 수 있습니다.

위 내용은 MySQL의 재귀 쿼리로 계층 적 데이터를 효율적으로 탐색하는 방법은 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!