Mysql

通過第 n 個孩子 ID 獲得最上面的父母？

  • May 25, 2018

現在有一個問題，我們通常使用這種技術來維護父子關係，即我們將所有實體儲存在一個帶有parent_id列的表中，並且所有最頂層的父母在 parent_id 列中都有0這是我同意的一種很好的標準化技術，但是也有一個缺點，它速度慢，效率低。這主要是由遞歸引起的，例如對於每個父級，我們必須一次又一次地執行查詢以生成樹

SELECT id FROM `table` WHERE parent_id=something

我已經查看了一些解決方案，有些人可能會嘗試通過一次又一次地執行查詢來使用任何程式語言來做到這一點，這會在伺服器上產生負載，有些人提供了儲存過程，但也涉及遞歸。

所以我的問題是，如果我們知道深度或者如果我們不知道是否有可能，我們可以對樹（連接或子查詢）進行一個數據庫查詢，那麼我們如何獲得最高父級（即 parent_id=0 ) 如果不可能，那麼為什麼這種技術如此有名，而它有缺陷，或者我們對此有另一種解決方案？. 我已經添加了 sql fiddle，但它只有架構

小提琴

在編寫此答案時，MySQL 根本沒有為您所尋找的內容提供便利。多個數據庫支持公用表表達式 (CTE)。@a_horse_with_no_name發布了一個命名這些 RDBMS的答案。

我已經發布了遞歸儲存過程：查找分層欄位的最高級別：使用 vs 不使用 CTE。但是，您強調不要使用任何儲存過程。要執行您所要求的操作，需要 MySQL 中的儲存過程。

妥協解決方案…

您仍然需要一個儲存過程，但不使用遞歸。使用一個簡單的循環，您可以建構一個自連接查詢並動態執行它。鑑於您正在尋找的樹的深度，這是一個這樣的儲存過程：

DELIMITER $$

DROP PROCEDURE IF EXISTS `dianuj`.`GetTopParentGivenDepth` $$
CREATE PROCEDURE `dianuj`.`GetTopParentGivenDepth` (GivenDepth INT)
BEGIN

   DECLARE x1,x2 INT;

   SET x = 0;
   SET y = 1;
   SET @SQ = 'SELECT DISTINCT A0.id FROM prarent A0';
   WHILE y < GivenDepth DO
       SET @SQ = CONCAT(@SQ,' INNER JOIN prarent A',y,' ON A',x,'.id = A',y,'.parent_id');
       SET x = y;
       SET y = x + 1;
   END WHILE;
   SET @SQ = CONCAT(@SQ,' WHERE A0.parent_id = 0');
   SELECT @SQ;
   PREPARE stmt FROM @SQ;
   EXECUTE stmt;
   DEALLOCATE PREPARE stmt;

END $$

DELIMITER ;

將您的數據載入到我桌面上的 MySQL 後，我執行儲存過程…

深度 1

mysql> call GetTopParentGivenDepth(1);
+--------------------------------------------------------------+
| @SQLSTMT                                                     |
+--------------------------------------------------------------+
| SELECT DISTINCT A0.id FROM prarent A0 WHERE A0.parent_id = 0 |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

深度 2

mysql> call GetTopParentGivenDepth(2);
+------------------------------------------------------------------------------------------------------------+
| @SQLSTMT                                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| SELECT DISTINCT A0.id FROM prarent A0 INNER JOIN prarent A1 ON A0.id = A1.parent_id WHERE A0.parent_id = 0 |
+------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

+----+
| id |
+----+
|  3 |
|  2 |
+----+
2 rows in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql>

深度 3 .. 6

mysql> call GetTopParentGivenDepth(3);
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| @SQLSTMT                                                                                                                                                 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT DISTINCT A0.id FROM prarent A0 INNER JOIN prarent A1 ON A0.id = A1.parent_id INNER JOIN prarent A2 ON A1.id = A2.parent_id WHERE A0.parent_id = 0 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

+----+
| id |
+----+
|  3 |
+----+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call GetTopParentGivenDepth(4);
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @SQLSTMT                                                                                                                                                                                               |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT DISTINCT A0.id FROM prarent A0 INNER JOIN prarent A1 ON A0.id = A1.parent_id INNER JOIN prarent A2 ON A1.id = A2.parent_id INNER JOIN prarent A3 ON A2.id = A3.parent_id WHERE A0.parent_id = 0 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

+----+
| id |
+----+
|  3 |
+----+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call GetTopParentGivenDepth(5);
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @SQLSTMT                                                                                                                                                                                                                                             |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT DISTINCT A0.id FROM prarent A0 INNER JOIN prarent A1 ON A0.id = A1.parent_id INNER JOIN prarent A2 ON A1.id = A2.parent_id INNER JOIN prarent A3 ON A2.id = A3.parent_id INNER JOIN prarent A4 ON A3.id = A4.parent_id WHERE A0.parent_id = 0 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

+----+
| id |
+----+
|  3 |
+----+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> call GetTopParentGivenDepth(6);
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @SQLSTMT                                                                                                                                                                                                                                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT DISTINCT A0.id FROM prarent A0 INNER JOIN prarent A1 ON A0.id = A1.parent_id INNER JOIN prarent A2 ON A1.id = A2.parent_id INNER JOIN prarent A3 ON A2.id = A3.parent_id INNER JOIN prarent A4 ON A3.id = A4.parent_id INNER JOIN prarent A5 ON A4.id = A5.parent_id WHERE A0.parent_id = 0 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Empty set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql>

注意Depth 6沒有返回值。要找到最大深度，您必須對其進行迭代，直到沒有行返回。

警告

  • 最壞的情況是使用表中的行數作為深度。這樣的樹只不過是一個退化的鍊錶。
  • 我不能給你任何關於查詢性能的保證，比如 SQL 構造和後續執行。任何類型的遞歸都是查詢解析器的唯一責任。

試一試 ！！！

引用自：https://dba.stackexchange.com/questions/46393

相關問答

Mysql

將電子商務商店中的產品圖像作為單獨的表與作為產品表的列實現的性能？

  • November 12, 2021
檢視問答
Mysql

將不同類型的數據儲存在單個列中的最佳方法，應該是查詢友好的 - MySql

  • February 15, 2021
檢視問答
Mysql

MySQL 8.021 = 100% CPU 當許多 UPDATE 查詢到 1 個表時

  • October 28, 2020
檢視問答
Mysql

從多個平台保存社交媒體文章的最佳方式

  • October 18, 2018
檢視問答
Mysql

如何對大張郵政編碼表進行快速簡單的查詢

  • August 31, 2018
檢視問答
Mysql

VARCHAR 或 DATETIME 使用哪個列更好？

  • June 6, 2018
檢視問答