mysql sql_mode

原文链接：https://www.cpweb.top/915

  以下简略介绍，详细请查看官方文档：https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html。
  SQL mode也可以称严格模式。作用是对SQL语句进行约束检查数据校验，让我们的SQL语句在执行时更加严谨、有意义等。
  在5.5版本中，sql_mode值为空；5.6版本中，sql_mode值为NO_ENGINE_SUBSTITUTION。

mysql> select @@sql_mode;
+------------------------+
| @@sql_mode             |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+

NO_ENGINE_SUBSTITUTION 的作用：
  mysql在创建表时指定engine子句（engine子句用于指定表的存储引擎），让mysql对此语句进行判断。
  如果此时把引擎指定成一个不存在或者不被支持的引擎，会有以下两种情况：
    1、sql_mode中不包涵no_engine_subtitution，mysql会把表的引擎改为innodb。
    2、sql_mode中包涵no_engine_subtitution，mysql会报错。

而5.7版本中，sql_mode值相对于5.6增加了不少。以下为5.7.28版本。

mysql> select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                        | 
+-------------------------------------------------------------------------------------------------------------------+
|ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,
NO_ENGINE_SUBSTITUTION                                                                                              |
+-------------------------------------------------------------------------------------------------------------------+


ANSI:
  宽松模式，对插入数据进行校验，如果不符合定义类型或长度，对数据类型调整或截断保存，并产生警告。

TRADITIONAL:
  当向mysql数据库插入数据时，进行数据的严格校验，错误数据不能插入并报错。用于事物时，会进行事物的回滚。

STRICT_TRANS_TABLES:
  严格模式，进行数据的严格校验，错误数据不能插入并报错。

ONLY_FULL_GROUP_BY:
  对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中。简而言之，就是SELECT后面接的列必须被GROUP BY后面接的列所包含。

NO_ZERO_IN_DATE:
  不允许日期和月份单独一个为零，但允许全零。例如支持：0000-01-01、0000-00-00，不支持：0000-01-00、0000-00-01。

NO_ZERO_DATE:
  不允许年月日同时为零。例如：0000-00-00。

ERROR_FOR_DIVISION_BY_ZERO:
  对于数据更改操作INSERT和UPDATE，如果数据除以0会报错，不设置此模式，则0除会插入NULL。对于SELECI，除以零会返回NULL并产生警告，不设置此模式，则0除返回NULL不出声警告。

NO_AUTO_CREATE_USER:
  禁止GRANT时自动创建密码为空的用户。

NO_ENGINE_SUBSTITUTION:
  创建表时如果指定一个不支持或者不存在的引擎会报错。

  sql_mode的设置。sql_mode有GLOBAL.sql_mode和SESSION.sql_mode。前者作用所有客户端连接（不过已连接客户端需要退出再连接才会生效），后者只影响当前客户端。只对sql_mode设置默认作用当前客户端。

1、删除某个模式
  当前:
  mysql> set sql_mode=(select replace(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

  全局:
  mysql> set global sql_mode=(select replace(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

2、删除所有并恢复
  当前:
  mysql> set sql_mode='';
  mysql> select @@sql_mode;
  +------------+
  | @@sql_mode |
  +------------+
  |            |
  +------------+
  mysql> set sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
  
  全局:
  mysql> set global sql_mode='';
  mysql> select @@GLOBAL.sql_mode;
  +-------------------+
  | @@GLOBAL.sql_mode |
  +-------------------+
  |                   |
  +-------------------+
  mysql> set global sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTTITUTION';

以上设置都是临时生效，数据库重启即失效。需要永久生效，则要修改配置文件。

[root@db ~]# vim /etc/my.cnf    //在mysqld下添加以下配置
sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

[root@db ~]# systemctl restart  mysqld
[root@db ~]# mysql -uroot -p

mysql> select @@sql_mode;
+--------------------------------------------+
| @@sql_mode                                 |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+

mysql> select @@GLOBAL.sql_mode;
+--------------------------------------------+
| @@GLOBAL.sql_mode                          |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+