基于可变长度编码前缀匹配数据表记录的SQL实现问题

这是ICD编码处理场景中常见的需求：根据查找表内存储的可变长度编码前缀，从主表筛选出所有匹配这些前缀的记录。

示例数据 #

查找表示例：

code_prefixes | char_length
--------------|------------
A12           | 3
B123          | 4

原有方案的问题 #

最初的朴素写法需要针对每个前缀长度单独编写SUBSTRING逻辑：

SELECT *
FROM main_table
WHERE SUBSTRING(code, 1, 3) IN (SELECT code_prefix FROM lookup_table WHERE char_length = 3)
OR SUBSTRING(code, 1, 4) IN (SELECT code_prefix FROM lookup_table WHERE char_length = 4)

这种方法存在两个明显缺陷：

• 扩展性差：新增不同长度的前缀时，必须手动添加新的OR分支
• 可靠性低：完全依赖char_length字段的准确性，一旦该字段值与前缀实际长度不符，就会导致匹配错误

优化方案 #

方案1：使用JOIN动态匹配前缀长度

直接通过前缀的实际长度匹配，无需依赖查找表的char_length字段：

SELECT DISTINCT mt.*
FROM main_table mt
JOIN lookup_table lt 
  ON LEFT(mt.code, CHAR_LENGTH(lt.code_prefixes)) = lt.code_prefixes

• 优势：自动适配所有前缀长度，新增前缀无需修改SQL；逻辑清晰，摆脱对char_length字段的依赖
• 注意：如果查找表存在重复前缀，主表记录会被重复返回，因此需要用DISTINCT去重

方案2：使用LIKE前缀匹配（推荐）

利用SQL的LIKE特性实现前缀匹配，写法更简洁且性能更优：

SELECT *
FROM main_table mt
WHERE EXISTS (
  SELECT 1
  FROM lookup_table lt
  WHERE mt.code LIKE CONCAT(lt.code_prefixes, '%')
)

• 优势：
  1. 完全不依赖char_length字段，直接通过前缀本身匹配，可靠性更高
  2. EXISTS子查询会在找到第一个匹配项后停止扫描，性能优于JOIN
  3. 天然避免重复记录，无需额外去重
• 性能提示：如果主表的code字段创建了索引，xxx%格式的LIKE匹配可以正常利用索引，大幅提升查询效率

注意事项 #

• 确保查找表中的code_prefixes字段无空值，避免出现意外的全表匹配
• 若主表数据量极大，优先选择方案2，利用索引优化查询速度

内容的提问来源于stack exchange，提问作者Simon.S.A.