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)
希望本文所总结的信息对你们有所帮助,觉得自己写的不是很好。感谢阅读~
每天进步一点点~