文档站点

主题

2025-02-03
664
2.5m

MySQL的where条件字符串不区分大小写 ​

背景 ​

在MySQL的默认情况下字符集采用的是utf8mb4，排序规则是utf8mb4_general_ci，这种情况下where条件遇到字符串是不区分大小写的。

比如以下两条SQL语句查询出来的结果都是一样的，即对大小写不敏感：

sql
SELECT * FROM TABLE NAME WHERE name='lingyejun';
SELECT * FROM TABLE NAME WHERE name='LingYeJun';

原因

通过查询资料发现是所选用的collate（校对）导致的问题 ，也即选用的排序规则问题。 collate规则：

*_bin: 表示的是binary case sensitive collation，也就是说是区分大小写的 *_cs: case sensitive collation，区分大小写 *_ci: case insensitive collation，不区分大小写

在MySQL中排序规则（collation）决定了如何对字符串进行比较和排序

解决方案： ​

方法一：使用BINARY关键字 ​

在MySQL中，BINARY关键字，用于声明一个列为二进制字符串类型。它可以用于创建表时指定列的数据类型，或者查询时限制列的比较方式。它可以确保在比较时将列视为二进制字符串，而不是根据字符集进行比较。

因此可以在建表时将列设置为BINARY

sql
CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) BINARY DEFAULT NULL,
)

或更新该字段为BINARY：

sql
ALTER TABLE sys_user MODIFY username VARCHAR(50) BINARY;

也可以不改变原有表结构，而是改SQL，在查询字段前加上 binary 关键字

sql
select * from sys_user where BINARY username = 'Admin'

方法二：修改字段的的排序规则 ​

utf8mb4字符集的默认排序规则是utf8mb4_general_ci，该排序规则是不区分大小写的，因此可以将字段的排序规则更换为区分大小写的排序规则utf8mb4_bin，如：

sql
ALTER TABLE sys_user MODIFY username VARCHAR(50) COLLATE utf8mb4_bin;

MySQL中utf8mb4字符集的常见排序规则介绍 ​

  • utf8mb4_general_ci：这是MySQL默认的排序规则。它是不区分大小写的（例如，"a"和"A"被认为是相等的）， 没有实现Unicode排序规则，在遇到某些特殊语言或者字符集，排序结果可能不一致。
  • utf8mb4_bin：这是一种二进制排序规则，它会按照字符的二进制编码进行排序。它是区分大小写的。例如，"a"和"A"被认为是不相等的。
  • utf8mb4_unicode_ci：这是一种基于Unicode字符集的排序规则。它是不区分大小写的。它支持多种语言和字符集，可以正确地处理各种语言的排序需求。
  • utf8mb4_unicode_520_ci：这是基于Unicode 5.2.0版本的排序规则。它是不区分大小写的。它是utf8mb4_unicode_ci的一个子集，提供了更快的排序性能。
最近更新