問(wèn)題描述

我有一個(gè) MySQL 表，如下所示:

現(xiàn)在，我想要一個(gè)單一的 MySQL 查詢(xún)，我只需提供 ID [例如說(shuō) id=19] 然后我應(yīng)該得到它的所有子 ID [即結(jié)果應(yīng)該有 ID '20,21,22']....

孩子的層次結(jié)構(gòu)未知；它可能會(huì)有所不同....

我知道如何使用 for 循環(huán)來(lái)實(shí)現(xiàn)...但如何使用單個(gè) MySQL 查詢(xún)實(shí)現(xiàn)相同的效果?

對(duì)于 MySQL 8+: 使用遞歸 with 語(yǔ)法.
對(duì)于 MySQL 5.x:使用內(nèi)聯(lián)變量、路徑 ID 或自聯(lián)接.

MySQL 8+

 with recursive cte (id, name, parent_id) as (選擇身份證，姓名，parent_id從產(chǎn)品其中 parent_id = 19聯(lián)合所有選擇 p.id，p.name,p.parent_id從產(chǎn)品 p內(nèi)連接p.parent_id = cte.id)從 cte 中選擇 *；

parent_id = 19 中指定的值應(yīng)設(shè)置為要選擇其所有后代的父級(jí)的 id.

MySQL 5.x

對(duì)于不支持 Common Table Expressions 的 MySQL 版本(最高版本 5.7)，您可以使用以下查詢(xún)來(lái)實(shí)現(xiàn):

選擇id，姓名，parent_id來(lái)自(從產(chǎn)品中選擇*按 parent_id 排序，id) products_sorted，(選擇@pv := '19')初始化其中 find_in_set(parent_id, @pv)和長(zhǎng)度(@pv := concat(@pv, ',', id))

這是一個(gè)小提琴.

此處，@pv := '19' 中指定的值應(yīng)設(shè)置為要選擇其所有后代的父級(jí)的 id.>

如果父母有多個(gè)孩子，這也適用.但是，要求每條記錄都滿(mǎn)足條件parent_id <;id，否則結(jié)果不完整.

查詢(xún)中的變量賦值

此查詢(xún)使用特定的 MySQL 語(yǔ)法:在執(zhí)行期間分配和修改變量.對(duì)執(zhí)行順序做了一些假設(shè):

• 首先評(píng)估 from 子句.所以這就是 @pv 被初始化的地方.
• where 子句按照從 from 別名中檢索的順序?yàn)槊織l記錄求值.所以這是一個(gè)條件，只包括父代已被識(shí)別為在后代樹(shù)中的記錄(主要父代的所有后代都逐漸添加到 @pv).
• 此where 子句中的條件按順序進(jìn)行評(píng)估，一旦總結(jié)果確定，評(píng)估就會(huì)中斷.因此，第二個(gè)條件必須排在第二位，因?yàn)樗鼘?id 添加到父列表中，并且只有在 id 通過(guò)第一個(gè)條件時(shí)才會(huì)發(fā)生這種情況.length 函數(shù)僅被調(diào)用以確保此條件始終為真，即使 pv 字符串由于某種原因會(huì)產(chǎn)生假值.

總而言之，人們可能會(huì)發(fā)現(xiàn)這些假設(shè)風(fēng)險(xiǎn)太大而無(wú)法依賴(lài).文檔警告:

您可能會(huì)得到您期望的結(jié)果，但這并不能保證[...] 涉及用戶(hù)變量的表達(dá)式的求值順序未定義.

因此，即使它與上述查詢(xún)一致，但評(píng)估順序仍可能會(huì)發(fā)生變化，例如，當(dāng)您添加條件或?qū)⒋瞬樵?xún)用作更大查詢(xún)中的視圖或子查詢(xún)時(shí).這是一個(gè)功能"將在未來(lái)的 MySQL 版本中刪除:

以前的 MySQL 版本可以在 SET 以外的語(yǔ)句中為用戶(hù)變量賦值.MySQL 8.0 支持此功能以實(shí)現(xiàn)向后兼容性，但在未來(lái)的 MySQL 版本中可能會(huì)被刪除.

如上所述，從 MySQL 8.0 開(kāi)始，您應(yīng)該使用遞歸 with 語(yǔ)法.

效率

對(duì)于非常大的數(shù)據(jù)集，此解決方案可能會(huì)變慢，因?yàn)?find_in_set 操作不是在列表中查找數(shù)字的最理想方法，當(dāng)然也不是在大小達(dá)到相同數(shù)量級(jí)的列表中返回的記錄數(shù).

方案一:with recursive，connect by

越來(lái)越多的數(shù)據(jù)庫(kù)實(shí)現(xiàn)了SQL:1999 ISO 標(biāo)準(zhǔn)WITH [RECURSIVE] 語(yǔ)法 用于遞歸查詢(xún)(例如 Postgres 8.4+, SQL Server 2005+、DB2、Oracle 11gR2+，SQLite 3.8.4+, Firebird 2.1+, H2、HyperSQL 2.1.0+、Teradata、MariaDB 10.2.2+).從 8.0 版開(kāi)始，MySQL 也支持它.有關(guān)要使用的語(yǔ)法，請(qǐng)參閱此答案的頂部.

某些數(shù)據(jù)庫(kù)具有用于分層查找的替代性非標(biāo)準(zhǔn)語(yǔ)法，例如 CONNECT BY 子句/B19306_01/server.102/b14200/queries003.htm" rel="noreferrer">Oracle, DB2，Informix, CUBRID 和其他數(shù)據(jù)庫(kù).

MySQL 5.7 版沒(méi)有提供這樣的功能.如果您的數(shù)據(jù)庫(kù)引擎提供了這種語(yǔ)法，或者您可以遷移到提供這種語(yǔ)法的引擎，那么這無(wú)疑是最好的選擇.如果不是，那么還可以考慮以下替代方案.

備選方案 2:路徑樣式標(biāo)識(shí)符

如果您分配包含分層信息的 id 值，事情會(huì)變得容易得多:路徑.例如，在您的情況下，這可能如下所示:

然后您的 select 將如下所示:

選擇id，姓名從產(chǎn)品其中 id 像 '19/%'

備選方案 3:重復(fù)自聯(lián)接

如果您知道層次結(jié)構(gòu)樹(shù)的深度上限，您可以使用標(biāo)準(zhǔn)的 sql 查詢(xún)，如下所示:

選擇 p6.parent_id 作為 parent6_id，p5.parent_id 作為 parent5_id，p4.parent_id 作為 parent4_id，p3.parent_id 作為 parent3_id，p2.parent_id 作為 parent2_id，p1.parent_id 作為 parent_id，p1.id 作為 product_id，p1.name來(lái)自產(chǎn)品 p1在 p2.id = p1.parent_id 上左連接產(chǎn)品 p2在 p3.id = p2.parent_id 上左連接產(chǎn)品 p3在 p4.id = p3.parent_id 上左連接產(chǎn)品 p4在 p5.id = p4.parent_id 上左連接產(chǎn)品 p5在 p6.id = p5.parent_id 上左連接產(chǎn)品 p6其中 19 in (p1.parent_id,p2.parent_id,p3.parent_id,p4.parent_id，p5.parent_id，p6.parent_id)按 1、2、3、4、5、6、7 排序；

見(jiàn)這個(gè)fiddle

where 條件指定要檢索其后代的父級(jí).您可以根據(jù)需要使用更多級(jí)別擴(kuò)展此查詢(xún).

I have a MySQL table which is as follows:

Now, I want to have a single MySQL query to which I simply supply the id [for instance say id=19] then I should get all its child ids [i.e. result should have ids '20,21,22']....

The hierarchy of the children is not known; it can vary....

I know how to do it using a for loop... but how to achieve the same using a single MySQL query?

For MySQL 8+: use the recursive with syntax.
For MySQL 5.x: use inline variables, path IDs, or self-joins.

MySQL 8+

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 p.parent_id = cte.id
)
select * from cte;

The value specified in parent_id = 19 should be set to the id of the parent you want to select all the descendants of.

MySQL 5.x

For MySQL versions that do not support Common Table Expressions (up to version 5.7), you would achieve this with the following query:

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))

Here is a fiddle.

Here, the value specified in @pv := '19' should be set to the id of the parent you want to select all the descendants of.

This will work also if a parent has multiple children. However, it is required that each record fulfills the condition parent_id < id, otherwise the results will not be complete.

Variable assignments inside a query

This query uses specific MySQL syntax: variables are assigned and modified during its execution. Some assumptions are made about the order of execution:

• The from clause is evaluated first. So that is where @pv gets initialised.
• The where clause is evaluated for each record in the order of retrieval from the from aliases. So this is where a condition is put to only include records for which the parent was already identified as being in the descendant tree (all descendants of the primary parent are progressively added to @pv).
• The conditions in this where clause are evaluated in order, and the evaluation is interrupted once the total outcome is certain. Therefore the second condition must be in second place, as it adds the id to the parent list, and this should only happen if the id passes the first condition. The length function is only called to make sure this condition is always true, even if the pv string would for some reason yield a falsy value.

All in all, one may find these assumptions too risky to rely on. The documentation warns:

you might get the results you expect, but this is not guaranteed [...] the order of evaluation for expressions involving user variables is undefined.

So even though it works consistently with the above query, the evaluation order may still change, for instance when you add conditions or use this query as a view or sub-query in a larger query. It is a "feature" that will be removed in a future MySQL release:

Previous releases of MySQL made it possible to assign a value to a user variable in statements other than SET. This functionality is supported in MySQL 8.0 for backward compatibility but is subject to removal in a future release of MySQL.

As stated above, from MySQL 8.0 onward you should use the recursive with syntax.

Efficiency

For very large data sets this solution might get slow, as the find_in_set operation is not the most ideal way to find a number in a list, certainly not in a list that reaches a size in the same order of magnitude as the number of records returned.

Alternative 1: with recursive, connect by

More and more databases implement the SQL:1999 ISO standard WITH [RECURSIVE] syntax for recursive queries (e.g. Postgres 8.4+, SQL Server 2005+, DB2, Oracle 11gR2+, SQLite 3.8.4+, Firebird 2.1+, H2, HyperSQL 2.1.0+, Teradata, MariaDB 10.2.2+). And as of version 8.0, also MySQL supports it. See the top of this answer for the syntax to use.

Some databases have an alternative, non-standard syntax for hierarchical look-ups, such as the CONNECT BY clause available on Oracle, DB2, Informix, CUBRID and other databases.

MySQL version 5.7 does not offer such a feature. When your database engine provides this syntax or you can migrate to one that does, then that is certainly the best option to go for. If not, then also consider the following alternatives.

Alternative 2: Path-style Identifiers

Things become a lot easier if you would assign id values that contain the hierarchical information: a path. For example, in your case this could look like this:

Then your select would look like this:

select  id,
        name 
from    products
where   id like '19/%'

Alternative 3: Repeated Self-joins

If you know an upper limit for how deep your hierarchy tree can become, you can use a standard sql query like this:

select      p6.parent_id as parent6_id,
            p5.parent_id as parent5_id,
            p4.parent_id as parent4_id,
            p3.parent_id as parent3_id,
            p2.parent_id as parent2_id,
            p1.parent_id as parent_id,
            p1.id as product_id,
            p1.name
from        products p1
left join   products p2 on p2.id = p1.parent_id 
left join   products p3 on p3.id = p2.parent_id 
left join   products p4 on p4.id = p3.parent_id  
left join   products p5 on p5.id = p4.parent_id  
left join   products p6 on p6.id = p5.parent_id
where       19 in (p1.parent_id, 
                   p2.parent_id, 
                   p3.parent_id, 
                   p4.parent_id, 
                   p5.parent_id, 
                   p6.parent_id) 
order       by 1, 2, 3, 4, 5, 6, 7;

See this fiddle

The where condition specifies which parent you want to retrieve the descendants of. You can extend this query with more levels as needed.

這篇關(guān)于如何創(chuàng)建 MySQL 分層遞歸查詢(xún)?的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！