MySQL如何高效自动校验带通配符的黑名单用户名

你不需要手动枚举所有LIKE条件，直接利用LIKE语法的特性，将待检测用户名放在LIKE左侧、表内存储的规则放在右侧做匹配即可，单条SQL就能完成自动校验。

基础查询写法 #

-- 查询有返回结果 = 用户名命中黑名单，禁止注册
-- 查询无返回结果 = 用户名不在黑名单，允许注册
SELECT 1
FROM blacklisted_usernames
WHERE '待检测的用户名' LIKE name
LIMIT 1;

以你给出的测试数据为例，检测用户名AdminJohn时，SQL会自动匹配表内所有规则，命中%admin%后返回1，完全不需要手动拼接多个OR条件。

不区分大小写适配 #

• 如果你的blacklisted_usernames.name字段使用的是大小写不敏感的排序规则（MySQL默认的utf8mb4_general_ci、utf8mb4_0900_ai_ci等后缀带_ci的规则都属于此类），上述查询天然满足不区分大小写的匹配要求，无需额外修改。
• 如果字段使用了大小写敏感的排序规则，只需要显式指定大小写不敏感的排序规则即可：

SELECT 1
FROM blacklisted_usernames
WHERE '待检测的用户名' LIKE name COLLATE utf8mb4_general_ci
LIMIT 1;

性能说明 #

• 绝大多数站点的黑名单规则量级都在千条以内，上述查询的全表扫描开销极低，性能完全满足业务要求，比循环逐行校验的效率高很多。
• 如果后续规则量级涨到十万级以上，可以拆分规则类型做索引优化：将规则分为精确匹配、前缀匹配、后缀匹配、包含匹配四类存储，前三类可以走字符串索引快速过滤，最后仅需要扫描少量包含类规则做匹配，就能进一步提升查询性能。

注意：常规业务查询是「字段值匹配固定规则」，所以会把字段写在LIKE左侧；这个场景是「固定输入值匹配表内存储的动态规则」，把输入值写在LIKE左侧、规则字段写在右侧才是正确写法。

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