如何在MariaDB中编写SQL查询获取层级键对应的祖先键？

核心逻辑梳理 #

根据你的层级编码规则，祖先Key需同时满足以下条件：

• 与目标Key首字符一致
• 祖先的有效前缀（移除右侧所有零的子串）是目标有效前缀的前缀，且有效前缀长度更短（对应零填充部分更长）

实现方案 #

1. 预处理目标Key

先提取目标Key的有效前缀（去掉右侧所有零），并记录其长度，这是判断祖先的基础依据。

2. 编写查询语句

基于预处理结果，筛选出符合条件的祖先Key。

具体SQL代码 #

假设你的表名为items，存储Key的字段为key，目标Key为@target_key（可替换为具体字符串或参数）：

-- 第一步：计算目标Key的有效前缀及长度
SET @target_key = 'ABC000'; -- 替换为你的目标Key
SET @effective_prefix = TRIM(TRAILING '0' FROM @target_key);
SET @prefix_length = CHAR_LENGTH(@effective_prefix);

-- 第二步：查询所有祖先Key
SELECT `key`
FROM items
WHERE
    -- 首字符匹配，排除无关联条目
    LEFT(`key`, 1) = LEFT(@effective_prefix, 1)
    -- 祖先的有效前缀是目标有效前缀的前缀
    AND LEFT(@effective_prefix, CHAR_LENGTH(TRIM(TRAILING '0' FROM `key`))) = TRIM(TRAILING '0' FROM `key`)
    -- 祖先有效前缀长度更短（零填充更长）
    AND CHAR_LENGTH(TRIM(TRAILING '0' FROM `key`)) < @prefix_length;

简化单语句版本

如果不想拆分变量设置，可以把逻辑整合为单条查询：

SELECT `key`
FROM items
WHERE
    LEFT(`key`, 1) = LEFT(TRIM(TRAILING '0' FROM 'ABC000'), 1)
    AND LEFT(TRIM(TRAILING '0' FROM 'ABC000'), CHAR_LENGTH(TRIM(TRAILING '0' FROM `key`))) = TRIM(TRAILING '0' FROM `key`)
    AND CHAR_LENGTH(TRIM(TRAILING '0' FROM `key`)) < CHAR_LENGTH(TRIM(TRAILING '0' FROM 'ABC000'));

示例验证 #

假设目标Key为ABC000（有效前缀ABC，长度3），符合条件的祖先Key会是：

• A00000（有效前缀A，长度1）
• AB0000（有效前缀AB，长度2）
  查询会返回这两个结果，完全匹配规则。

注意事项 #

• 若目标Key是根节点（如A00000，有效前缀长度为1），查询会返回空结果，符合根节点无祖先的逻辑
• 确保所有Key都是等长字符串，否则TRIM和长度计算可能出现偏差

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