MySQL 大小写敏感问题

MySQL在Linux下数据库名、表名、列名、别名大小写的默认规则是这样的：

          1.数据库名与表名是严格区分大小写的.

          2.表的别名是严格区分大小写的.

          3.列名与列的别名在所有的情况下均是忽略大小写的.

          4.字段内容(即数据)默认情况下是大小写不敏感的.

          5.变量名(函数和存储过程)也是严格区分大小写的.

由于上述的第四点导致自己服务的数据产生问题，那么MySQL控制这些究竟是为什么呢?

正是字符集和字符集的校对规则限制的.

MySQL在shell工具下使用客户端进行查询操作时，你会发现其对字段值是不区分大小写的。此时若你的需求是区分大小写，那么可以使用如下解决方案。

设置COLLATE即设置校对，具体规则如下：

• *_bin: 表示的是binary case sensitive collation，即区分大小写的

• *_cs: case sensitive collation，即区分大小写

• *_ci: case insensitive collation，即不区分大小写

解决方法有如下两种：

1. 从查询层面解决，将查询条件用binary()括起来

select * from tableName where binary columnName ='aaa';

2. 从存储层面解决，修改该字段的collation 为 binary

ALTER TABLE tableName MODIFY COLUMN columName VARCHAR(50) BINARY CHARACTER SET utf8 
COLLATE utf8_bin DEFAULT NULL;

补充：针对第二种方法要注意的是，此修改只对后续创建的记录有效，已经存在的数据记录是不受影响的。想要更改现有表的定义，那就必须更改表结构或者重建数据库。

 

原理：

对于CHAR、VARCHAR和TEXT类型，BINARY属性可以为列分配该列字符集的校对规则。BINARY属性是指定列字符集的二元校对规则的简写。排序和比较基于数值字符值，因此也就自然区分了大小写。

我所遇到的问题：

MySQL创建表初期没有考虑到char类型在MySQL中默认其对大小写不敏感，而在统计服务对数据信息聚合时，服务代码本身针对bizName这个char类型字段对其是区分大小写的，但由于自己的MySQL表中存在bizName字段大小写不敏感的问题，导致在存在仅大小写不一致的bizName在同一统计时间粒度会出现部分数据插入失败。

因此统计服务的数据与对方的数据相比偶尔会存在偏少的情况，最终联系DBA同事对服务相关表修改字符集校对规则。

注意⚠️：

MySQL在创建表初期其实我们就需要明确字段值是否区分大小写，否则类似统计服务这种就会出现不正确的行为。

例如：在统计bizName为aaa和AAA的数据量时，MySQL的Select语句会将aaa与AAA的量加在一起作为aaa或AAA的统计结果，最终写入MySQL的针对同一个主键下只有一个结果，要么bizName为aaa，要么为AAA。

而当你查询aaa或者AAA时，MySQL的SELECT语句默认不区分大小写，此时拿到的数据一样。其实此时拿到的数据都是不真实的。

如下是不区分大小写的domain字段数据，它所引发的问题：

插入一条domain值为“CHINA”的数据成功，然后在插入domain值为“china”时，系统提示已经存在该记录。原因就在于domain字段值没有区分大小写！！！

// 给小时表插入一条domain字段为“CHINA”的记录
mysql> insert into hour_process values(1554357600000,2882303761517470000,"CHINA",2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from hour_process where domain = "china";
+---------------+---------------------+--------+--------+
| timestamp     | appId               | domain | status |
+---------------+---------------------+--------+--------+
| 1554357600000 | 2882303761517470000 | CHINA  |      2 |
+---------------+---------------------+--------+--------+
1 row in set (0.00 sec)

// 给小时表插入一条domain字段为“china”的记录时，提示已经该记录已经存在导致插入失败
mysql> insert into hour_process values(1554357600000,2882303761517470000,"china",2);
ERROR 1062 (23000): Duplicate entry '1554357600000-2882303761517470000-china' for key 'PRIMARY'

mysql> select * from hour_process where domain = "china";
+---------------+---------------------+--------+--------+
| timestamp     | appId               | domain | status |
+---------------+---------------------+--------+--------+
| 1554357600000 | 2882303761517470000 | CHINA  |      2 |
+---------------+---------------------+--------+--------+
1 row in set (0.00 sec)

 

希望本文所总结的信息对你们有所帮助，觉得自己写的不是很好。感谢阅读～

 

每天进步一点点～