MySQL中相似/重复食材词汇识别的SQL优化需求

我正在开发一款类似MyFitness Pal的食品软件，需要在7000条配料记录中识别高度相似/重复的词汇（无法用LIKE逐一匹配）。比如“brown sugar”和“brown sugar from Jamaica”属于同类，需要被识别出来。

现有SQL语句如下：

SELECT 
group_concat(it.id_ingredient) 'id duplicati ' -- 相似词汇的ID列表
, group_concat(CASE 
WHEN i.hide_search = 0 THEN it.id_ingredient
ELSE NULL
END  ) 'Id sostitutivo'
, group_concat(it.name) 'Ingrediente' -- 相似词汇列表
, IFNULL( it.alias, '-') 'Alias'
, COUNT(it.name)  as CNT -- 相似词汇数量
FROM ingredients i 
LEFT JOIN ingredients_translation it ON i.id = it.id_ingredient AND it.language_code = 'IT'
WHERE i.id_customer = 1
AND i.isComplex = 0
GROUP BY SUBSTRING(SOUNDEX(it.name), 1,6);

当前语句能运行，但存在明显问题：错误地将“Acqua”“Acacia”“Acciughe”这类实际不同的词汇归为相似项——这是因为SOUNDEX是为英语设计的语音匹配算法，对意大利语的元音、辅音组合处理精度不足，再加上截取前6位的规则过于宽泛，导致误匹配。

优化思路与具体方案 #

1. 替换SOUNDEX为适配意大利语的语音算法 #

SOUNDEX对罗曼语族的支持很差，建议改用Double Metaphone或意大利语版本的Metaphone算法。如果使用MySQL，可以自定义实现意大利语Metaphone函数，或者使用部分版本内置的PHONETIC()函数（需确认数据库版本支持），这类算法能更准确地捕捉意大利语词汇的语音特征，减少误判。

2. 提取核心词分组，处理修饰性后缀 #

配料名称的相似性大多源于核心词一致、附加修饰语不同（比如“brown sugar”和“brown sugar from Jamaica”）。可以先通过正则去掉常见修饰语，提取核心词后再分组：

• 示例正则处理（针对意大利语和英语修饰语）：

  REGEXP_REPLACE(it.name, ' (from|organic|raw|bio|di|da|con)[^ ]*', '', 1, 0, 'i') AS core_name
  

• 也可以结合词干提取工具（比如自定义词干处理函数），将词汇还原为词根，再基于词根分组，进一步提升匹配准确性。

3. 结合编辑距离过滤误匹配 #

分组后可以用Levenshtein编辑距离对组内词汇做二次筛选，确保只有真正相似的词汇被保留：

• 在MySQL中自定义Levenshtein函数，计算两个字符串的编辑距离，设置合理阈值（比如≤3）来判断是否为相似项；
• 可以在分组后通过子查询对每组内的词汇进行两两比较，过滤掉编辑距离过大的条目。

4. 优化分组规则 #

不要直接截取语音编码的前6位，建议结合核心词+短语音编码的组合方式分组，平衡精度和召回率：

GROUP BY core_name, SUBSTRING(DOUBLE_METAPHONE(it.name), 1, 4)

调整后的示例SQL #

SELECT 
  GROUP_CONCAT(it.id_ingredient) AS 'id_duplicati',
  GROUP_CONCAT(CASE WHEN i.hide_search = 0 THEN it.id_ingredient ELSE NULL END) AS 'id_sostitutivo',
  GROUP_CONCAT(it.name) AS 'ingrediente',
  IFNULL(it.alias, '-') AS 'alias',
  COUNT(it.name) AS cnt
FROM ingredients i
LEFT JOIN ingredients_translation it 
  ON i.id = it.id_ingredient AND it.language_code = 'IT'
WHERE i.id_customer = 1
  AND i.isComplex = 0
GROUP BY 
  -- 提取核心词，移除常见修饰语
  REGEXP_REPLACE(it.name, ' (from|organic|raw|bio|di|da|con)[^ ]*', '', 1, 0, 'i'),
  -- 结合Double Metaphone前4位，降低误匹配概率
  SUBSTRING(DOUBLE_METAPHONE(it.name), 1, 4);

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