MySQL/SQLite中完整单词的前缀模糊查询方案

表结构与测试数据 #

create table product (
  nama_product varchar(20)
);

insert into product values
('ayam bakar'),
('daging ayam'),
('bayam hijau'),
('daun bayam');

当前问题 #

现有查询语句：

select nama_product
  from product
 where nama_product regexp '(^| )ayam( |$)';

该语句可精准匹配包含完整单词ayam的记录，但仅支持输入完整单词，无法通过前缀（如aya）匹配以该前缀开头的完整单词。使用%ayam%会匹配单词中间包含目标字符的记录（如bayam），不符合需求。

需求 #

输入前缀（如aya）时，返回所有包含以该前缀开头的完整单词的记录，预期结果：

nama_product
ayam bakar
daging ayam

MySQL解决方案 #

调整正则表达式，匹配开头/空格 + 前缀 + 任意单词字符 + 空格/结尾的模式：

-- 直接使用前缀'aya'的示例
select nama_product
from product
where nama_product regexp '(^| )aya[[:alnum:]]*( |$)';

-- 动态传入前缀的示例（使用变量）
set @prefix = 'aya';
select nama_product
from product
where nama_product regexp concat('(^| )', @prefix, '[[:alnum:]]*( |$)');

说明 #

• (^| )：匹配字符串开头或单词前的空格，确保是完整单词的起始
• aya：指定的前缀
• [[:alnum:]]*：匹配任意数量的字母/数字（单词的剩余部分），支持前缀后有任意字符的完整单词
• ( |$)：匹配单词后的空格或字符串结尾，确保是完整单词的结束

SQLite解决方案 #

SQLite默认未启用REGEXP功能，若已通过自定义函数或扩展启用正则支持，可使用类似写法：

select nama_product
from product
where nama_product regexp '(^| )aya\w*( |$)';

若未启用正则，可使用字符串函数组合实现：

select nama_product
from product
where
  -- 匹配前缀在开头的情况：前缀开头，后续是空格或字符串结束
  (nama_product like 'aya%' and (length(nama_product) = length('aya') or substr(nama_product, length('aya')+1, 1) = ' '))
  or
  -- 匹配前缀在中间的情况：前缀前是空格，后续是空格或字符串结束
  (instr(nama_product, ' aya') > 0 and (
    substr(nama_product, instr(nama_product, ' aya') + length(' aya'), 1) = ' '
    or instr(nama_product, ' aya') + length(' aya') > length(nama_product)
  ));

验证结果 #

上述两种方案均会返回预期结果：

nama_product
ayam bakar
daging ayam

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