SQL实现数据表全列多关键词模糊搜索的高效方案咨询

你当前的自定义函数存在两个问题：一是遗漏了Vehicle_ID字段的检索逻辑，二是仅支持单个关键词入参。以下是适配SQL Server环境的可落地方案：

方案1：改造自定义函数，支持多关键词入参（推荐复用场景） #

如果需要频繁调用检索逻辑，可改造原表值函数，支持传入分隔符拼接的多关键词字符串，内部自动拆分后完成全列匹配。
SQL Server 2016及以上版本可直接使用内置STRING_SPLIT函数拆分字符串，代码如下：

CREATE FUNCTION [dbo].[Keyword_Search_Multi]
(
    @KeywordList VARCHAR(MAX), -- 传入分隔符拼接的多关键词，例：'11111,88888'
    @SplitChar VARCHAR(10) = ',' -- 关键词分隔符，默认使用逗号
)
RETURNS TABLE
AS
RETURN
(
    WITH KeywordCTE AS (
        -- 拆分关键词，自动拼接前后模糊匹配符
        SELECT '%' + TRIM(value) + '%' AS MatchPattern
        FROM STRING_SPLIT(@KeywordList, @SplitChar)
        WHERE TRIM(value) <> '' -- 过滤空值关键词
    )
    SELECT DISTINCT vp.*
    FROM Vehicle_parts vp
    INNER JOIN KeywordCTE k
        -- 所有待检索列在此处添加匹配条件，数值列需转字符串后匹配
        ON CAST(vp.Vehicle_ID AS VARCHAR(50)) LIKE k.MatchPattern
        OR vp.Vehicle_Major_Part_Number LIKE k.MatchPattern
        OR vp.Vehicle_Sub_Part_Number LIKE k.MatchPattern
);

调用方式 #

-- 检索匹配11111、88888的记录，返回结果和预期完全一致
SELECT * FROM Keyword_Search_Multi('11111,88888', DEFAULT);

如果使用SQL Server 2016以下版本，没有内置STRING_SPLIT函数，自行实现一个字符串拆分表值函数替换CTE内的拆分逻辑即可。

方案2：直接写查询逻辑（适合临时检索场景） #

如果不需要复用函数，可直接通过VALUES构造关键词列表写查询，无需额外创建函数：

SELECT DISTINCT vp.*
FROM Vehicle_parts vp
WHERE EXISTS (
    SELECT 1
    FROM (VALUES ('11111'),('88888')) AS kw(Keyword)
    WHERE CAST(vp.Vehicle_ID AS VARCHAR(50)) LIKE '%' + kw.Keyword + '%'
       OR vp.Vehicle_Major_Part_Number LIKE '%' + kw.Keyword + '%'
       OR vp.Vehicle_Sub_Part_Number LIKE '%' + kw.Keyword + '%'
);

大数据量性能优化建议 #

上述LIKE写法因为匹配规则是前后模糊匹配（%关键词%），无法命中普通B树索引，单表数据量超过百万级时检索效率会明显下降。这类场景建议给三个检索字段创建全文索引，使用CONTAINS语法检索，性能可提升10倍以上：

-- 需提前给表配置全文目录、创建全文索引后使用
SELECT * FROM Vehicle_parts
WHERE CONTAINS(
    (Vehicle_ID, Vehicle_Major_Part_Number, Vehicle_Sub_Part_Number),
    '"11111*" OR "88888*"'
);

注意事项 #

• 全列检索时不要遗漏字段，数值类型字段必须显式转为字符串类型再做LIKE匹配，否则会触发隐式转换报错、或匹配结果不符合预期。
• 用关联关键词表+DISTINCT去重的写法，比在WHERE子句中嵌套多层OR的可读性更强，后续新增检索列、调整匹配规则时维护成本更低。

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