MySQL中的校对集/大小写敏感/sql_mode实践

校对集：数据比较方式。必须在无数据前进行声明，如果有了数据再进行数据修改，那么修改无效！

主要使用的有如下格式：

• _bin：binary，二进制比较，取出二进制位，一位一位进行比较，区分大小写；
• _cs：case sensitive，大小写敏感，区分大小写
• _ci：case insensitive，大小写不敏感，不区分大小写。
• _ai：accent insensitive，不区分重音；
• _as：accent sensitive，区分重音；

【1】查看数据库所支持的校对集

show collation

【2】对比不同校对集的效果

对比：使用utf8的_bin and _ci来验证不同校对集的效果；

① 创建两张使用不同校对集的表

CREATE TABLE `my_collate_bin` (
  `name` char(1) COLLATE utf8_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
CREATE TABLE `my_collate_ci` (
  `name` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

② 分别插入数据

insert into my_collate_bin VALUES('a','A','B','b');
insert into my_collate_ci VALUES('a','A','B','b');

③ 比较

根据某个字段进行排序-order by 字段名[asc||desc] ;

select * from my_collate_bin ORDER BY name;
select * from my_collate_ci ORDER BY name;

可以看到图一是正常排序，图二未区分大小写(一般应用中，默认不区分大小写，使用utf8_general_ci)。

utf8_unicode_ci utf8_general_ci区别

二者对中文、英文来说没有实质区别。utf8_general_ci 校对速度快，但准确度稍差。utf8_unicode_ci 准确度高，但校对速度稍慢。一般情况下，用utf8_general_ci 就够了，但如果应用有德语、法语或者俄语，请一定使用utf8_unicode_ci 。

【3】大小写敏感

我们顺带说一下MySQL大小写敏感问题。主要涉及到环境变量：lower_case_table_names。

show variables like '%lower_case_table_names%'

取值有三个：

• 0：大小写敏感；
• 1：大小写不敏感。创建的表、数据库都是以小写形式存放在磁盘上，对于SQL语句都是转换为小写对表和数据库进行查找。

• 创建的表和数据库依据语句上格式存放，凡是查找都是转换为小写进行。

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

• 数据库名、表名、表的别名、变量名是严格区分大小写的；
• 关键字、函数名称在SQL中不区分大小写；
• 列名与列的别名在所有的情况下都是忽略大小写的；

MySQL在window环境下全部不区分大小写。

如果Linux下想要设置大小写不敏感，那么需要在my.cnf文件中[mysqld]下添加如下配置：

lower_case_table_names=1

然后重启数据库服务，切记在重启数据库实例之前需要将原来的数据库和表转换为小写，否则将找不到数据库名。

这种操作适合于MySQL5.7不适合MySQL8。在MySQL8下禁止在重新启动MySQL服务时将 lower_case_table_names 修改为不同于初始化MySQL服务时设置的 lower_case_table_names 值。如果非要将MySQL8设置为大小写不敏感，具体步骤如下：

• 停止MySQL服务；
• 删除数据目录，通常删除 /var/lib/mysql
• my.cnf文件中添加 lower_case_table_names=1
• 重新启动MySQL服务

【4】sql_mode

sql_mode会影响MySQL支持的SQL语法以及它执行的数据验证检查。通过设置sql_mode 可以完成不同严格程度的数据校验，有效地保障数据准确性。

MySQL服务器可以在不同的SQL模式下运行，并且可以针对不同的客户端以不同的方式应用这些模式，具体取决于sql_mode系统变量的值。

MySQL5.6和MySQL5.7默认的sql_mode模式参数是不一样的：

• 5.6的sql_mode默认为空，即NO_ENGINE_SUBSTITUTION。其实表示的是一个空值，相当于没有什么模式设置，可以理解为宽松模式。在这种设置下是可以允许一些非法操作的，比如允许一些非法数据的插入。
• 5.7的sql_mode是STRICT_TRANS_TABLES，也就是严格模式。用于进行数据的严格校验，错误数据不能插入，将会报error错误并产生回滚。

查看当前会话的sql_mode

select @@session.sql_mode;
select @@global.sql_mode;

临时设置sql_mode

# 当前会话
set session sql_mode='STRICT_TRANS_TABLES';
# 全局
set global sql_mode='STRICT_TRANS_TABLES';

永久设置sql_mode

同样是修改mysql的配置文件，在[mysqld]下增加配置：

sql_mode=STRICT_TRANS_TABLES

针对生产环境禁止重启MySQL服务，所以解决措施通常是临时方式+永久方式来解决线上问题，这样即使某天MySQL服务重启了，我们的配置仍然生效。