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语句都是转换为小写对表和数据库进行查找。
• 2：创建的表和数据库依据语句上格式存放，凡是查找都是转换为小写进行。

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服务重启了，我们的配置仍然生效。

模式	说明
ONLY_FULL_GROUP_BY	对于group by聚合操作，如果在select中的列没有在group by中出现，那么这个SQL是不合法的，因为列不在group by从句中
NO_AUTO_VALUE_ON_ZERO	该值影响自增长列的插入。默认设置下插入 0 或者null 代表生成下一个自增长值。如果用户希望插入的值为 0 ，而该列又是自增长的，那么这个选项就有用了。
STRICT_TRANS_TABLES	在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做限制
NO_ZERO_IN_DATE	在严格模式下，不允许日期和月份为零
NO_ZERO_DATE	设置该值，MySQL数据库不允许插入 零日期，插入零日期会抛出错误而不是警告
ERROR_FOR_DIVISION_BY_ZERO	在insert或update过程中，如果数据被零除，则产生错误而非警告。如果未给出该模式，那么数据被零除时MySQL返回null。
NO_AUTO_CREATE_USER	禁止grant创建密码为空的用户
NO_ENGINE_SUBSTITUTION	如果需要的存储引擎被引用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代并抛出一个异常。
PIPES_AS_CONCAT	将丨丨 视为 字符串的`连接操作符而非或运算符，这和oracle数据库是一样的，也和字符串的拼接函数concat相类似。
ANSI_QUOTES	启用ANSI_QUOTES后，不能用双引号来引用字符串，因为它被解释为识别符