SQL全文搜索整数值处理方案及多列加权搜索技术问询

嘿，针对你遇到的整数值纳入SQL全文搜索的需求，我整理了几个实用方案，你可以根据自己用的数据库和场景来选：

方案1：将整数字段转换为字符串后加入全文索引 #

这是最通用的方法，适合大多数数据库（比如MySQL、SQL Server）。核心思路是把整数类型的字段（比如course_level）转换成字符串，然后将这个字符串字段加入全文索引中，这样就能和其他文本字段一起参与全文搜索了。

示例（MySQL）： #

首先添加一个存储计算列，把整数转成字符串：

ALTER TABLE courses ADD COLUMN course_level_str VARCHAR(10) AS (CAST(course_level AS CHAR)) STORED;

然后创建包含这个新字段的全文索引：

ALTER TABLE courses ADD FULLTEXT INDEX ft_courses (course_name, course_level_str, duration);

搜索时可以结合权重计算相关性：

SELECT *,
  -- 给不同字段设置权重，course_name权重最高，course_level次之
  MATCH(course_name) AGAINST('math 6' IN BOOLEAN MODE) * 3 +
  MATCH(course_level_str) AGAINST('6' IN BOOLEAN MODE) * 2 +
  MATCH(duration) AGAINST('3 months' IN BOOLEAN MODE) * 1 AS relevance
FROM courses
WHERE MATCH(course_name, course_level_str, duration) AGAINST('math 6' IN BOOLEAN MODE)
ORDER BY relevance DESC;

方案2：利用数据库原生的全文搜索扩展处理数值（以PostgreSQL为例） #

PostgreSQL的tsvector支持直接将数值转换为文本后纳入向量，还能通过setweight轻松设置字段权重，不需要额外添加计算列。

示例： #

首先创建包含所有需要搜索字段的生成向量列：

ALTER TABLE courses ADD COLUMN search_vector tsvector GENERATED ALWAYS AS (
  -- 给course_name设最高权重A，course_level设B，duration设C
  setweight(to_tsvector('english', course_name), 'A') ||
  setweight(to_tsvector('english', CAST(course_level AS TEXT)), 'B') ||
  setweight(to_tsvector('english', duration), 'C')
) STORED;

然后创建GIN索引提升搜索性能：

CREATE INDEX ft_idx_courses ON courses USING GIN(search_vector);

搜索时使用ts_rank计算相关性：

SELECT *,
  ts_rank(search_vector, to_tsquery('english', 'math & 6')) AS relevance
FROM courses
WHERE search_vector @@ to_tsquery('english', 'math & 6')
ORDER BY relevance DESC;

方案3：动态结合全文搜索与数值过滤（无需修改索引） #

如果不想调整表结构或索引，可以在查询时直接把数值匹配作为相关性计算的一部分，同时结合全文搜索条件。这种方式适合临时需求，但数据量大时性能可能不如前两种方案。

示例： #

SELECT *,
  MATCH(course_name) AGAINST('math' IN BOOLEAN MODE) * 3 +
  -- 如果course_level匹配目标值，就加上对应的权重分
  CASE WHEN course_level = 6 THEN 2 ELSE 0 END +
  MATCH(duration) AGAINST('3 months' IN BOOLEAN MODE) * 1 AS relevance
FROM courses
-- 同时匹配全文搜索条件和数值条件
WHERE MATCH(course_name, duration) AGAINST('math' IN BOOLEAN MODE) OR course_level = 6
ORDER BY relevance DESC;

额外提示： #

• 对于duration这种带单位的字符串字段，里面的数字本身就是文本，直接加入全文索引就能被搜索到，不需要额外处理；如果是纯数字的duration字段，参考前面的整数转字符串方法即可。
• 不同数据库的全文搜索语法有差异，比如SQL Server的全文搜索需要使用CONTAINS或FREETEXT，调整时注意对应数据库的文档。

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