Oracle中如何用CONTAINS实现类似LIKE %xxx%的快速全文检索？

针对你的问题，我来一步步拆解说明，帮你搞定600万条数据的高效检索需求：

一、用CONTAINS实现类似LIKE '%hh%'的高效全文检索 #

首先要明确：LIKE '%hh%'会触发全表扫描，600万条数据下性能极差，而全文索引通过倒排索引结构，能大幅提升检索速度。下面以SQL Server为例（因为CONTAINS是SQL Server的全文检索函数），步骤如下：

1. 创建全文目录（如果还没有的话）
   全文索引需要依附于全文目录，执行以下语句创建：

   CREATE FULLTEXT CATALOG ft_user_detail AS DEFAULT;
   

2. 给user_detail表的firstname列创建全文索引
   注意：表必须有唯一主键（比如user_id），全文索引依赖唯一键来关联记录：

   -- 假设你的主键索引名为PK_user_detail，替换成实际的主键索引名
   CREATE FULLTEXT INDEX ON user_detail(firstname) 
   KEY INDEX PK_user_detail;
   

3. 使用CONTAINS实现子串匹配
   要实现类似LIKE '%hh%'的任意位置子串匹配，需要用双引号包裹+星号通配符，语法如下：

   SELECT * FROM user_detail 
   WHERE CONTAINS(firstname, '"*hh*"', 1) > 0;
   

   这里的第三个参数1是指定语言（英文的LCID，中文用2052），确保分词规则符合你的语言场景。这个语句会匹配所有firstname中包含hh子串的记录，和LIKE '%hh%'效果一致，但性能提升非常明显。

二、区分CONTAINS(firstname,'hh',1) > 0与CONTAINS(firstname,'%hh%',1) > 0 #

先明确：CONTAINS的第三个参数是语言LCID，不影响匹配逻辑，主要影响分词规则，下面重点看前两个参数的差异：

• CONTAINS(firstname,'hh',1) > 0
  这个语句是匹配firstname中存在独立的"hh"词的记录。这里的"词"是指符合全文索引分词规则的独立单元（比如被空格、逗号、句号等分隔的字符串）。举个例子，它会匹配"hh Smith"或"John hh"，但不会匹配"hhill"或"Smithhh"——因为这些是包含hh作为子串，而非独立词。

• CONTAINS(firstname,'%hh%',1) > 0
  这里要特别注意：CONTAINS中不支持用%作为通配符（通配符是*），所以%会被当作普通字符处理。这个语句实际是匹配firstname中包含**完整字符串"%hh%"**的记录，比如"abc%hh%def"，完全不是你想要的LIKE '%hh%'的效果。

额外注意事项 #

• 如果你的数据是中文，记得把语言参数改成2052（中文LCID），否则分词会出错，导致检索结果不准确。
• 全文索引默认是后台异步更新的，如果你的数据频繁变更，可能需要手动刷新索引保证数据一致性：

  ALTER FULLTEXT INDEX ON user_detail START FULL POPULATION;
  

• 600万条数据的全文索引创建可能需要一段时间，建议在低峰期操作。

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