如何基于insertAfter列值重新排序输出SQL表数据？

问题背景 #

现有SQL表结构如下：

CREATE TABLE `table` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `categoryName` text DEFAULT NULL,
  `freeText` tinyint(1) DEFAULT 0,
  `multiple` tinyint(1) DEFAULT 0,
  `activecat` tinyint(1) DEFAULT 1,
  `insertAfter` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`)
);

需要编写SQL查询，将insertAfter≠0的行插入到其指定的insertAfter对应行的后面，完整输出所有数据，预期输出格式示例：

(1,'gartenname',1,0,1,0),
(2,'plz',1,0,1,0),
...（省略部分数据）

此前尝试递归CTE时出现错误：#1146 - Tabelle 'wg_multi_db.cte' existiert nicht（表不存在），其他方案仅输出insertAfter≠0的行，用PHP代码也未解决问题，寻求可行实现方案。

可行实现方案 #

递归CTE方案（MySQL 8.0+） #

递归CTE是处理这类层级排序需求的可靠方式，之前报错大概率是语法错误导致。以下是正确的实现代码：

WITH RECURSIVE sorted_categories AS (
    -- 锚点：筛选所有排序起点行（insertAfter为0或NULL）
    SELECT 
        ID, categoryName, freeText, multiple, activecat, insertAfter,
        CAST(ID AS CHAR(255)) AS path
    FROM `table`
    WHERE COALESCE(insertAfter, 0) = 0
    UNION ALL
    -- 递归：关联后续插入的行，拼接路径用于排序
    SELECT 
        t.ID, t.categoryName, t.freeText, t.multiple, t.activecat, t.insertAfter,
        CONCAT(sc.path, ',', t.ID) AS path
    FROM `table` t
    JOIN sorted_categories sc ON t.insertAfter = sc.ID
)
-- 按路径排序并拼接成预期格式输出
SELECT 
    CONCAT('(', ID, ',''', categoryName, ''',', freeText, ',', multiple, ',', activecat, ',', COALESCE(insertAfter, 0), ')') AS output_row
FROM sorted_categories
ORDER BY path;

逻辑说明

• 锚点成员先取出所有作为排序起点的行，同时生成初始的路径字符串（用ID表示）。
• 递归成员通过insertAfter关联已排序的行，把当前行的ID拼接到路径后，路径字符串会完整反映行的顺序层级。
• 最后按路径字符串排序，就能得到符合insertAfter指定顺序的结果，再用CONCAT拼接成你需要的输出格式。

兼容MySQL 5.x的替代方案 #

如果你的MySQL版本低于8.0，不支持递归CTE，可以用自定义变量模拟排序：

SELECT 
    CONCAT('(', ID, ',''', categoryName, ''',', freeText, ',', multiple, ',', activecat, ',', COALESCE(insertAfter, 0), ')') AS output_row
FROM (
    SELECT 
        t.*,
        @sort_path := CONCAT(@sort_path, ',', t.ID) AS sort_path
    FROM (
        -- 先按初始顺序排序
        SELECT * FROM `table` ORDER BY COALESCE(insertAfter, 0), ID
    ) t
    -- 初始化变量
    JOIN (SELECT @sort_path := '') AS init
) AS sorted
-- 按ID在路径中的位置排序
ORDER BY FIND_IN_SET(ID, sort_path);

注意：这个方案适合层级简单的场景，若存在复杂的嵌套插入逻辑，递归CTE方案更稳定。

内容的提问来源于stack exchange，提问作者drumkid