MySQL的SQL_Mode修改小计

文章汇总：https://www.cnblogs.com/dotnetcrazy/p/9160514.html

目录：

• 问题复现
• 临时解决
• 重启失效验证
• 配置文件
• 知识点

问题复现

今天突然发现MySQL服务器升级之后sql_mode变成宽松摸索了，危害如下：

临时解决

set global sql_mode='strict_trans_tables'（阿里服务器默认是：strict_trans_tables）

重启失效验证

上面这种方式在MySQL重启后就会失效，想要永久生效还得联系运维工程师，eg：

配置文件

PS：为了防止再出现升级后SQL_Mode改变，以后在配置文件中设置SQL_Mode

知识点

• SQL Model：定义MySQL对约束的响应行为：
  • 会话修改：
    • mysql> set [session] sql_model='xx_mode'
    • mysql> set @@session.sql_mode='xx_mode'
    • PS：只在当前会话生效
  • 全局修改：需要有权限，并且不会立即生效，对以后新建的会话生效（从全局继承的）
    • mysql> set global sql_mode='xx_mode'
    • mysql> set @@global.sql_mode='xx_mode'
    • PS：MySQL重启后失效
  • 配置修改：永远生效：
    • eg：vi /etc/my.cnf，在[mysqld]下添加sql_mode='xx'，然后重启数据库
    • PS：从MySQL8开始,可通过set persist命令将全局变量的修改持久化到配置文件中
      • 持久化到/var/lib/mysql/mysqld-auto.cnf配置文件中
      • eg：set persist log_timestamps='SYSTEM';（需要root权限）
  • 常用mode：（阿里服务器默认是：strict_trans_tables）
    • traditional：使用传统模型，不允许对非法值做插入操作
    • strict_all_tables：对所有表做严格约束
    • strict_trans_tables：对所有支持事物类型的表做严格约束：
      • PS：最常见，主要对事物型的存储引擎生效，其他的没效果（如果插入数据不符合规范，则中断当前操作）
    • no_engine_substitution：建表的时候指定不可用存储引擎会报错
    • only_full_group_by：检验group by语句的合法性
      • 要求在在分组查询语句中，把所有没有使用聚合函数的列，列出来
      • eg：select count(url),name from file_records group by url;
        • 使用了name字段，name不是聚合函数，那必须在group by中写一下
    • PS：生存环境下最好不要修改，容易报错对业务产生影响（严格变宽松没事）
    • 查询当前设置：select @@sql_mode