MySQL搜索hashtag标签返回前缀匹配的多余结果如何解决

问题原因 #

你当前的搜索逻辑用了LIKE "%#xxx%"语法，其中%是MySQL的通配符，可以匹配任意长度的任意字符，所以只要内容里出现#xxx前缀的片段就会命中，自然会返回带后缀的其他标签。

解决方案 #

核心是在SQL层加标签边界匹配规则：只匹配前后都不是标签合法字符（字母、数字、下划线）的完整标签，这里推荐用MySQL的正则匹配实现，代码改法如下：

1. 原有输入过滤逻辑可以保留，作用是把用户传入的标签里的特殊字符剔除，拿到干净的标签文本：

$_GET["tag"] = '#nba'; // 实际来自查询参数
$fulltag = preg_replace("/[^a-zA-Z0-9_]/", '', $_GET["tag"]);

2. 把原来的LIKE查询替换为正则匹配查询，规则是：标签要么出现在文本开头，要么前面不是标签合法字符；标签后面要么是文本结尾，要么不是标签合法字符：

// 注意：这里仅做逻辑演示，实际生产环境必须用预处理语句防SQL注入，不要直接拼接用户输入到SQL中
$sql = "SELECT * FROM status WHERE data REGEXP '(^|[^a-zA-Z0-9_])#{$fulltag}([^a-zA-Z0-9_]|$)' LIMIT 12";

额外说明 #

• 如果需要严格区分大小写，可以把REGEXP换成REGEXP BINARY即可。
• 千万不要跳过SQL预处理步骤：即使用户输入做了正则过滤，直接拼接SQL仍然存在注入风险，推荐用PDO或MySQLi的预处理功能传参。

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