php - MySQL查询按父子位置排序

Question

我的数据库中有一个页面表，每个页面都可以有一个父页面，如下所示:

id            parent_id            title
1             0                    Home
2             0                    Sitemap
3             0                    Products
4             3                    Product 1
5             3                    Product 2
6             4                    Product 1 Review Page

什么是最好的 MySQL 查询来选择所有页面按父然后子然后子再排序如果有不止一个级别，最多有三个级别。上面的示例将产生所需的顺序:

Home
Sitemap
Products
    Product 1
        Product 1 Review Page
    Product 2

Answer 1

如果你必须坚持你的模型，我建议这个查询:

SELECT p.id, p.title, 
       (
        SELECT LPAD(parent.id, 5, '0') 
        FROM page parent 
        WHERE parent.id = p.id AND parent.parent_id = 0 

        UNION

        SELECT CONCAT(LPAD(parent.id, 5, '0'), '.', LPAD(child.id, 5, '0')) 
        FROM page parent 
        INNER JOIN page child ON (parent.id = child.parent_id) 
        WHERE child.id = p.id AND parent.parent_id = 0 

        UNION

        SELECT CONCAT(LPAD(parent.id, 5, '0'), '.', LPAD(child.id, 5, '0'), '.', LPAD(grandchild.id, 5, '0')) 
        FROM page parent 
        INNER JOIN page child ON (parent.id = child.parent_id) 
        INNER JOIN page grandchild ON (child.id = grandchild.parent_id) 
        WHERE grandchild.id = p.id AND parent.parent_id = 0 
       ) AS level  
FROM page p
ORDER BY level;

结果集示例:

+-----+-------------------------+-------------------+
| id  | title                   | level             |
+-----+-------------------------+-------------------+
|   1 | Home                    | 00001             |
|   2 | Sitemap                 | 00002             |
|   3 | Products                | 00003             |
|   4 | Product 1               | 00003.00004       |
|   6 | Product 1 Review Page 1 | 00003.00004.00006 |
| 646 | Product 1 Review Page 2 | 00003.00004.00646 |
|   5 | Product 2               | 00003.00005       |
| 644 | Product 3               | 00003.00644       |
| 645 | Product 4               | 00003.00645       |
+-----+-------------------------+-------------------+
9 rows in set (0.01 sec)

解释的输出:

+------+--------------------+--------------+--------+---------------+---------+---------+--------------------------+------+----------------+
| id   | select_type        | table        | type   | possible_keys | key     | key_len | ref                      | rows | Extra          |
+------+--------------------+--------------+--------+---------------+---------+---------+--------------------------+------+----------------+
|  1   | PRIMARY            | p            | ALL    | NULL          | NULL    | NULL    | NULL                     |  441 | Using filesort |
|  2   | DEPENDENT SUBQUERY | parent       | eq_ref | PRIMARY,idx1  | PRIMARY | 4       | tmp.p.id                 |    1 | Using where    |
|  3   | DEPENDENT UNION    | child        | eq_ref | PRIMARY,idx1  | PRIMARY | 4       | tmp.p.id                 |    1 |                |
|  3   | DEPENDENT UNION    | parent       | eq_ref | PRIMARY,idx1  | PRIMARY | 4       | tmp.child.parent_id      |    1 | Using where    |
|  4   | DEPENDENT UNION    | grandchild   | eq_ref | PRIMARY,idx1  | PRIMARY | 4       | tmp.p.id                 |    1 |                |
|  4   | DEPENDENT UNION    | child        | eq_ref | PRIMARY,idx1  | PRIMARY | 4       | tmp.grandchild.parent_id |    1 |                |
|  4   | DEPENDENT UNION    | parent       | eq_ref | PRIMARY,idx1  | PRIMARY | 4       | tmp.child.parent_id      |    1 | Using where    |
| NULL | UNION RESULT       | <union2,3,4> | ALL    | NULL          | NULL    | NULL    | NULL                     | NULL |                |
+------+--------------------+--------------+--------+---------------+---------+---------+--------------------------+------+----------------+
8 rows in set (0.00 sec)

我使用了这个表格布局:

CREATE TABLE `page` (
  `id` int(11) NOT NULL,
  `parent_id` int(11) NOT NULL,
  `title` varchar(255) default NULL,
  PRIMARY KEY  (`id`),
  KEY `idx1` (`parent_id`)
);

请注意，我在 parent_id 上包含了一个索引以提高性能。