SQL多列模糊匹配后按字符串匹配度从高到低排序实现方法

我们需要实现多列模糊搜索功能，采用LIKE %InputParam%的匹配规则，对表中OrderID、Ref no两个字符串类型字段匹配搜索参数，匹配结果按照字符串匹配度由高到低排序。

示例表结构与数据 #

OrderId  | Name | Ref No  |
12345    | XYZ  | 120545  | 
1205     | ABC  | 451003  |
00120505 | CDE  | 000174  |

排序规则 #

• 完全匹配优先：字段值与搜索参数完全一致的结果排在最前
• 其次为字段包含目标子串的结果，匹配度越低排序越靠后

当输入搜索参数为1205时，预期输出如下：

OrderId  | Name | Ref no  |
1205     | ABC  | 451003  |
12345    | XYZ  | 120545  |
00120505 | CDE  | 000174  |

实现SQL #

假设表名为order_table，可直接使用如下查询语句，如需动态传参将1205替换为对应预编译占位符即可：

SELECT OrderId, Name, `Ref No`
FROM order_table
WHERE OrderId LIKE '%1205%' OR `Ref No` LIKE '%1205%'
ORDER BY 
  -- 完全匹配优先级最高
  CASE WHEN OrderId = '1205' THEN 1
       WHEN `Ref No` = '1205' THEN 2
       ELSE 3 END ASC,
  -- 按匹配子串数量降序，匹配越多优先级越高
  CHAR_LENGTH(OrderId) - CHAR_LENGTH(REPLACE(OrderId, '1205', '')) DESC,
  -- 按子串出现位置升序，越靠前匹配度越高
  LOCATE('1205', OrderId) ASC,
  CHAR_LENGTH(`Ref No`) - CHAR_LENGTH(REPLACE(`Ref No`, '1205', '')) DESC,
  LOCATE('1205', `Ref No`) ASC;

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