MySQL LIKE语句默认大小写不敏感未生效 实现不区分大小写模糊查询

MySQL的LIKE操作不存在全局默认的大小写不敏感特性，匹配时是否区分大小写完全由查询字段的排序规则（Collation）决定：

• 排序规则后缀为_ci（Case Insensitive）时，匹配自动忽略大小写
• 排序规则后缀为_cs（Case Sensitive）、_bin（二进制逐字节匹配）时，LIKE会严格区分大小写
  你当前查询出现大小写敏感的表现，是因为name、surname字段使用了大小写敏感的排序规则，CONCAT函数拼接生成的字符串会继承原字段的排序规则，因此小写关键词无法匹配大写开头的内容。

按对业务的侵入性从低到高排序：

• 方案1：查询时临时指定大小写不敏感排序规则
  不需要修改现有表结构，仅在需要忽略大小写匹配的语句中指定_ci后缀的排序规则即可，写法示例：

  SELECT * FROM users 
  WHERE CONCAT(name, ' ', surname) LIKE '%rob%' COLLATE utf8mb4_general_ci;
  

  注意选择和你当前表字符集匹配的_ci规则即可，MySQL 8.0+版本也可以替换为utf8mb4_0900_ai_ci。

• 方案2：统一转换大小写后匹配
  将字段值和匹配关键词统一转为大写或小写后再比对，完全绕开排序规则的影响，所有版本、所有排序规则下都能生效，写法示例：

  SELECT * FROM users 
  WHERE LOWER(CONCAT(name, ' ', surname)) LIKE LOWER('%rob%');
  

  注意该写法会导致字段上的普通索引无法生效，数据量较大时查询性能会明显下降，不适合大表场景使用。

• 方案3：修改字段排序规则（一劳永逸）
  如果你的业务本身就不需要对姓名字段做大小写敏感的匹配，可以直接把两个字段的排序规则修改为_ci后缀的类型，之后所有查询默认都是大小写不敏感的，不需要每次写SQL加特殊逻辑，修改示例：

  ALTER TABLE users 
    MODIFY name VARCHAR(字段实际定义的长度) COLLATE utf8mb4_general_ci,
    MODIFY surname VARCHAR(字段实际定义的长度) COLLATE utf8mb4_general_ci;
  

  执行前务必把语句中的字段长度替换为你建表时设置的实际长度，避免字段属性被错误修改。

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