• Post author:
  • Post category:mysql
  • Post comments:0评论
Like
Like Love Haha Wow Sad Angry

  以下简略介绍,详细请查看官方文档: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 |
+--------------------------------------------+
Like
Like Love Haha Wow Sad Angry

发表评论