mysql locate 的用法和它跟 like 的差别

做商品搜索时写了 WHERE LOCATE('手机', name)，能出数据，但翻到第 20 页明显变慢。mysql locate 本身不复杂，坑主要在索引上。

语法是 LOCATE(substr, str, pos)，返回子串第一次出现的位置，从 1 开始，找不到返回 0。pos 可选，表示从第几个字符开始找，省略就从头找。

SELECT LOCATE('mysql', 'learn mysql fast'); 结果 7 SELECT LOCATE('a', 'banana', 3); 结果 4

大小写看排序规则

表用 utf8mb4_general_ci 时，LOCATE('A', 'abc') 返回 1；换成 _bin 结尾的排序规则就返回 0。要强制区分大小写，可以在参数外面套 BINARY。

还有一个容易混的 INSTR(str, substr)，参数顺序和 LOCATE 相反，功能一样，看团队习惯选一个就行。

其实两者在前缀匹配时差别不在函数本身。LIKE 'abc%' 能走索引，LIKE '%abc%' 和 LOCATE 都要逐行扫。数据量几十万行以上，全表扫描的代价就出来了。

真要模糊搜，优先把条件收窄，比如先按分类和状态过滤，再对剩下的行做 LOCATE。或者上全文索引，InnoDB 从 5.6 起支持，中文分词效果一般，需要自己权衡。

参数为 NULL 时结果也是 NULL，不是 0，判断时要写 IS NULL。多字节字符按字符算位置，不是字节，和 CHAR_LENGTH 的口径一致。

想验证差别，用 EXPLAIN 看 type 列：const、ref 正常，ALL 就是全表扫。把 LIKE 和 LOCATE 各跑一次，对比 rows 和实际耗时，比凭感觉靠谱。

LOCATE 的结果还能直接参与排序，比如按关键词出现的位置排，但这样没法走索引，只适合数据量小的后台列表。中文列上用时注意字符集一致，连接字符集和列字符集不同，位置可能对不上。

结论：小表、后台查询用 mysql locate 挺顺手；前台搜索和大表，别把它当索引救星。