MySQL搜索时忽略字符串键值，仅匹配值部分的方案咨询

针对你现有存储格式（custName:John|custSurname:Doe|custTel:123456789|custEmail:john@doe.com）的客户信息，要实现仅匹配值部分的实时搜索，这里提供两种直接基于现有列的可行方案：

方案一：正则匹配查询（无需表结构修改） #

利用数据库正则表达式功能，构造仅匹配冒号后、分隔符|前的值区域的规则，避免命中键名。

MySQL 示例 #

当用户输入关键词（如sur）时，执行以下查询：

SELECT * FROM your_table
WHERE cust_info REGEXP CONCAT('(:|\\|)[^|]*', QUOTE('sur'), '[^|]*(\\||$)');

• 正则逻辑：(:|\\|)匹配值区域的起始（键名后的冒号，或分隔符|）；[^|]*匹配值区域内任意非分隔符字符；最终确保关键词只出现在值部分，不会匹配custSurname这类键名里的子串。

PostgreSQL 示例 #

使用POSIX正则匹配：

SELECT * FROM your_table
WHERE cust_info ~* CONCAT('(:|\|)[^|]*', quote_literal('sur'), '[^|]*(\||$)');

• ~*表示不区分大小写匹配，若需区分大小写则改用~。

适用场景：数据量较小（万级以内），实时搜索响应要求不极致的场景，无需额外索引或表结构改动。

方案二：虚拟生成列+全文索引（大数据量优化） #

如果数据量较大，正则查询性能不足，可以使用数据库的虚拟生成列（不占用物理存储空间，基于原列实时计算）提取所有值部分，再配合全文索引实现高效搜索，完全规避同步问题。

MySQL 实现步骤 #

1. 添加虚拟生成列，自动提取所有值部分：

ALTER TABLE your_table 
ADD COLUMN search_values TEXT GENERATED ALWAYS AS (REGEXP_REPLACE(cust_info, '[^|]+:', '')) VIRTUAL;

• 该列会自动将原串转换为John|Doe|123456789|john@doe.com格式，仅保留值部分。

2. 为生成列创建全文索引：

ALTER TABLE your_table ADD FULLTEXT INDEX ft_search_values (search_values);

3. 搜索查询：

SELECT * FROM your_table
WHERE MATCH(search_values) AGAINST('sur' IN BOOLEAN MODE);

PostgreSQL 实现步骤 #

1. 添加虚拟生成列：

ALTER TABLE your_table
ADD COLUMN search_values TEXT GENERATED ALWAYS AS (regexp_replace(cust_info, '[^|]+:', '', 'g')) STORED;

• PostgreSQL的STORED生成列会存储计算结果，但仍会随原列自动更新，无同步问题。

2. 创建全文索引：

CREATE INDEX ft_search_values ON your_table USING gin(to_tsvector('english', search_values));

3. 搜索查询：

SELECT * FROM your_table
WHERE to_tsvector('english', search_values) @@ to_tsquery('english', 'sur');

适用场景：数据量较大（十万级以上），对搜索响应速度要求高的场景，虚拟列自动同步原数据，无需手动维护。

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