MySQL中如何从层级列提取自引用父键？

当然可以直接在MySQL里搞定，不用折腾外部脚本！我给你分享几种实用的方法，直接在SELECT语句里就能动态提取父键：

方法一：利用反转字符串定位最后一个分隔点 #

这种方法的核心是找到层级字符串中最后一个点的位置，然后截取该点之前的内容作为父节点：

SELECT
  hierarchy_column AS node,
  CASE
    -- 顶级节点（没有点）的父节点为NULL
    WHEN LOCATE('.', hierarchy_column) = 0 THEN NULL
    -- 反转字符串后找到第一个点的位置，以此确定原字符串最后一个点的位置，截取前面的内容
    ELSE LEFT(hierarchy_column, LENGTH(hierarchy_column) - LOCATE('.', REVERSE(hierarchy_column)))
  END AS parent_node
FROM your_table;

举个实际例子：

• 对于1.A.1.a，反转后是a.1.A.1，第一个点的位置是2，原字符串长度是6，6-2=4，截取前4位就是1.A.1，正好是父节点。
• 对于1这种顶级节点，因为没有点，父节点直接返回NULL。

方法二：用SUBSTRING_INDEX结合点数量计算 #

另一种思路是先统计层级字符串里的点的总数，再用SUBSTRING_INDEX截取到倒数第二个点的位置：

SELECT
  hierarchy_column AS node,
  CASE
    -- 顶级节点（点数量为0）的父节点为NULL
    WHEN LENGTH(hierarchy_column) - LENGTH(REPLACE(hierarchy_column, '.', '')) = 0 THEN NULL
    -- 点的数量就是分隔次数，截取到对应次数的内容
    ELSE SUBSTRING_INDEX(hierarchy_column, '.', LENGTH(hierarchy_column) - LENGTH(REPLACE(hierarchy_column, '.', '')))
  END AS parent_node
FROM your_table;

比如：

• 1.A.1.a有3个点，SUBSTRING_INDEX截取前3段，得到1.A.1。
• 1.A有1个点，截取前1段，得到1。

额外：如果要将父键持久化到表中 #

如果后续需要把父键作为自引用外键存到表中，可以用类似逻辑写UPDATE语句：

-- 先添加父键列（类型和你的层级列保持一致）
ALTER TABLE your_table ADD COLUMN parent_id VARCHAR(255);

-- 更新父键值
UPDATE your_table
SET parent_id = CASE
  WHEN LOCATE('.', hierarchy_column) = 0 THEN NULL
  ELSE LEFT(hierarchy_column, LENGTH(hierarchy_column) - LOCATE('.', REVERSE(hierarchy_column)))
END;

-- 可选：建立自引用外键约束
ALTER TABLE your_table ADD CONSTRAINT fk_parent_node FOREIGN KEY (parent_id) REFERENCES your_table(hierarchy_column);

这些方法都是纯MySQL内置函数实现的，完全不需要外部脚本，直接就能在查询或更新中生成你需要的自引用父键~

备注：内容来源于stack exchange，提问作者Steve