Oracle SQL中LIKE模糊匹配表连接查询返回空结果排查

你写的旧式隐式连接语法本身逻辑没有错误，测试环境能跑通说明匹配规则成立，真实数据返回空集基本是以下几个原因导致，按出现概率从高到低排查：

• 大小写敏感匹配失败
  Oracle默认LIKE操作是大小写敏感的。测试环境的虚拟表数据一般会刻意保持query字段和PFERDENAME字段的大小写完全一致，但真实的AOL搜索query是用户自发输入的，大小写组合非常随机；如果Pferde表中马名是统一大写/首字母大写存储，和query里的小写写法对不上，就会完全匹配不到。

• 字段存在不可见冗余字符
  如果创建Pferde表时PFERDENAME字段用了定长CHAR类型而非变长VARCHAR2类型，数据库会自动在存储的马名尾部补全空格到字段定义长度，拼接后生成的LIKE匹配串会携带大量尾部空格，只有query内容中马名后刚好带对应数量空格才会命中。除此之外，导入马匹数据时如果源文件带首尾制表符、换行符、全角空格等不可见字符，也会导致匹配失败。

• 大表查询未执行完成误判为空
  AOL 2006泄露的querydata表存储了超过2000万条用户搜索记录，如果存储的赛马信息有数千到数万条，无关联条件的隐式连接会生成笛卡尔积，实际需要计算的匹配行数可达千亿级别，执行时间极长；如果客户端设置了查询超时，或者执行中途被中断，看到的空白结果集不是真的没有匹配数据，是语句没跑完。

• 马名携带LIKE通配符导致匹配逻辑失效
  如果马匹名称本身包含_（匹配任意单个字符）、%（匹配任意长度字符）这两个LIKE通配符，未做转义的情况下匹配逻辑会不符合预期，也可能导致结果为空，这个问题出现概率较低。

修复后的可用SQL #

以下写法统一了匹配大小写、自动去除字段首尾冗余空白，可选开启通配符转义适配特殊马名：

select q.query,
       p.PFERDENAME
from AOLDATA.querydata q,
     Pferde p
where lower(q.query) like '%' || lower(trim(p.PFERDENAME)) || '%'
-- 若确认马名包含_、%等特殊字符，启用下面的转义写法替换上面的where条件
-- where lower(q.query) like '%' || replace(replace(replace(lower(trim(p.PFERDENAME)), '\', '\\'), '%', '\%'), '_', '\_') || '%' escape '\'
ORDER BY p.PFERDENAME;

如果数据量较大执行时间过长，可以先限制Pferde表的查询范围做小批量验证，确认匹配逻辑正常后再全量执行。

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