如何用SQL精准查询text字段中pageTitle含指定字符串的记录？

问题背景 #

需要从page_contents表的text类型fields字段中，筛选出pageTitle包含特定关键词的记录。原SQL语句：

SELECT * FROM page_contents WHERE fields ILIKE '%"pageTitle":"%keyword%",%';

存在误匹配问题——比如关键词为meta时，会错误命中metaDescription字段的内容，而非仅匹配pageTitle内部的关键词。

可行解决方案：用正则SQL精准匹配 #

完全可以通过正则表达式的SQL语句实现精准匹配，无需先全量导出再用代码过滤。根据使用的数据库类型，写法略有不同：

1. PostgreSQL 写法 #

使用~*操作符（大小写不敏感匹配），正则锁定pageTitle的键值对范围：

SELECT * FROM page_contents 
WHERE fields ~* '"pageTitle":"[^"]*keyword[^"]*"';

• [^"]* 表示匹配任意非双引号的字符，确保只在pageTitle的双引号包裹范围内查找关键词，不会串到其他字段。

2. MySQL 写法 #

使用REGEXP或RLIKE操作符（MySQL 8.0+支持REGEXP_LIKE，可指定大小写不敏感）：

-- 大小写不敏感匹配
SELECT * FROM page_contents 
WHERE fields REGEXP '"pageTitle":"[^"]*keyword[^"]*"';

-- 或者用REGEXP_LIKE明确指定大小写不敏感
SELECT * FROM page_contents 
WHERE REGEXP_LIKE(fields, '"pageTitle":"[^"]*keyword[^"]*"', 'i');

更优方案：将fields转为JSON类型 #

如果你的数据库支持JSON字段（PostgreSQL 9.3+、MySQL 5.7+都支持），建议直接把fields字段改为JSON类型，查询会更高效且逻辑更清晰：

-- PostgreSQL
SELECT * FROM page_contents 
WHERE fields->>'pageTitle' ILIKE '%keyword%';

-- MySQL
SELECT * FROM page_contents 
WHERE JSON_UNQUOTE(JSON_EXTRACT(fields, '$.pageTitle')) LIKE '%keyword%';

这种方式直接定位pageTitle字段，完全避免了正则匹配的边界问题，性能也更优（可针对JSON字段的键建立索引）。

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