MySQL 5.7 SQL MODE严格模式带来的影响

阅读量：5733 次

发布时间：2019-06-18

本文共 9696 字，大约阅读时间需要 32 分钟。

背景：在之前的升级过程中，为了对RD更加友好的支持，我们都是把MySQL的SQL MODE修改成低版本的，但是这样往往也会带来一些其他问题，今天我们就来梳理一下，SQL MODE在MySQL 5.6和5.7两个版本之间的差异，让DBA在后续的升级过程中，更加从容。

一、如何查看现在MySQL的SQL MODE

1.1 查看mysql的sql mode：

# MySQL 5.6 Default SQL_MODE;mysql> select @@sql_mode;+--------------------------------------------+| @@sql_mode                                 |+--------------------------------------------+| NO_ENGINE_SUBSTITUTION |+--------------------------------------------+1 row in set (0.00 sec)# MySQL 5.7 Default SQL_MODE;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 |+-------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

1.2 简单解释下5.7的默认SQL MODE

ONLY_FULL_GROUP_BY

在select、having、group by列表里引用的列必须在group by列表中，否则报错。

STRICT_TRANS_TABLES

严格模式控制MySQL如何处理非法或丢失的输入值的SQL。有几种原因可以使一个值为非法。例如，数据类型错误或超出范围。当新插入的行不包含某列的没有显示定义DEFAULT子句的值，则该值被丢失。

对于事务表，当启用STRICT_ALL_TABLES或STRICT_TRANS_TABLES模式时，如果语句中有非法或丢失值，则会出现错误。SQL语句被回滚。

对于非事务表，STRICT_TRANS_TABLES，MySQL将非法值转换为最接近该列的合法值并插入调整后的值。如果值丢失，MySQL在列中插入隐式默认值。在任何情况下，MySQL都会生成警告而不是给出错误并继续执行语句。

如果你不使用严格模式(即不启用STRICT_TRANS_TABLES或STRICT_ALL_TABLES模式)，对于非法或丢失的值，MySQL将插入调整后的值并给出警告。在严格模式，你可以通过INSERT IGNORE或UPDATE IGNORE来实现。

NO_ZERO_IN_DATE

在严格模式，不接受月或日部分为0的日期，对年不限制。如果使用IGNORE选项，我们为类似的日期插入’0000-00-00’。在非严格模式，可以接受该日期，但会生成警告。

NO_ZERO_DATE

在严格模式，不要将’0000-00-00’做为合法日期。你仍然可以用IGNORE选项插入零日期。在非严格模式，可以接受该日期，但会生成警告。

ERROR_FOR_DIVISION_BY_ZERO

在严格模式，在INSERT或UPDATE过程中，如果被零除(或)，则产生错误(否则为警告)。如果未给出该模式，被零除时MySQL返回NULL。如果用到INSERT IGNORE或UPDATE IGNORE中，MySQL生成被零除警告，但操作结果为NULL。

NO_AUTO_CREATE_USER

在严格模式下，防止GRANT自动创建新用户，除非还指定了密码。

二、验证SQL MODE对SQL的影响

2.1 group by语句的影响

mysql> select @@sql_mode;+-------------------------------------------+| @@sql_mode                                |+-------------------------------------------+| ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION |+-------------------------------------------+1 row in set (0.00 sec)mysql> select * from words group by id;ERROR 1055 (42000): 'tom.words.word' isn't in GROUP BYmysql> select id,count(word) from words group by word;ERROR 1055 (42000): 'tom.words.id' isn't in GROUP BYmysql> select id,word,count(word) from words group by word;ERROR 1055 (42000): 'tom.words.id' isn't in GROUP BYmysql> select word,count(word) from words group by word having count(word)>1;+------+-------------+| word | count(word) |+------+-------------+| bbbb |           3 |+------+-------------+1 row in set (0.00 sec)mysql> select * from words;+----+------+| id | word |+----+------+|  1 | aaaa ||  2 | bbbb ||  3 | cccc ||  4 | bbbb ||  5 | bbbb |+----+------+5 rows in set (0.00 sec)

2.2 日期零值的影响

mysql> CREATE TABLE `test` (`time` datetime NOT NULL DEFAULT '1111-00-01 00:00:00'  ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;Query OK, 0 rows affected (0.00 sec)mysql> select @@sql_mode;+-------------------------------------------+| @@sql_mode                                |+-------------------------------------------+| ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION |+-------------------------------------------+1 row in set (0.00 sec)mysql> set sql_mode='ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION,NO_ZERO_IN_DATE,NO_ZERO_DATE';Query OK, 0 rows affected, 2 warnings (0.00 sec)mysql> drop table test;Query OK, 0 rows affected (0.00 sec)mysql> CREATE TABLE `test` (`time` datetime NOT NULL DEFAULT '1111-00-01 00:00:00'  ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;Query OK, 0 rows affected, 1 warning (0.01 sec)mysql> select @@sql_mode;+------------------------------------------------------------------------+| @@sql_mode                                                             |+------------------------------------------------------------------------+| ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION |+------------------------------------------------------------------------+1 row in set (0.00 sec)mysql> select @@version;+------------+| @@version  |+------------+| 5.6.35-log |+------------+1 row in set (0.00 sec)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';Query OK, 0 rows affected, 3 warnings (0.00 sec)mysql> drop table test;Query OK, 0 rows affected (0.00 sec)mysql> CREATE TABLE `test` (`time` datetime NOT NULL DEFAULT '1111-00-01 00:00:00'  ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;ERROR 1067 (42000): Invalid default value for 'time'

下面的例子：

# 不合法默认值;CREATE TABLE `test` (`time` datetime NOT NULL DEFAULT '1111-00-01 00:00:00') ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;# 合法默认值;CREATE TABLE `test` (`time` datetime NOT NULL DEFAULT '1111-01-01 00:00:00') ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;# 合法默认值;CREATE TABLE `test` (`time` datetime NOT NULL DEFAULT '0000-01-01 00:00:00') ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

2.3 字段非法值的影响

mysql> select @@sql_mode;+------------------------+| @@sql_mode             |+------------------------+| NO_ENGINE_SUBSTITUTION |+------------------------+1 row in set (0.00 sec)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';Query OK, 0 rows affected, 3 warnings (0.00 sec)mysql> use tomReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show create table words;+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                                                |+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| words | CREATE TABLE `words` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `word` varchar(4) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 |+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql> insert into words(word) values("abcde");ERROR 1406 (22001): Data too long for column 'word' at row 1mysql> insert into words(word) values(10/0);ERROR 1365 (22012): Division by 0mysql> insert into words(word) values(13);Query OK, 1 row affected (0.00 sec)mysql> insert into words(id) values(abc);ERROR 1054 (42S22): Unknown column 'abc' in 'field list'mysql> insert into words(id) values('abc');ERROR 1366 (HY000): Incorrect integer value: 'abc' for column 'id' at row 1mysql> alter table words change `word` `word` varchar(3) DEFAULT NULL;ERROR 1265 (01000): Data truncated for column 'word' at row 1mysql> alter table words change `id` `id` int(10) unsigned NOT NULL AUTO_INCREMENT;Query OK, 7 rows affected (0.01 sec)Records: 7  Duplicates: 0  Warnings: 0mysql> insert into words(id) values(-1);ERROR 1264 (22003): Out of range value for column 'id' at row 1mysql>show create table words1;+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------+| Table  | Create Table                                                                                                                                            |+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------+| words1 | CREATE TABLE `words1` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `word` varchar(5) NOT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql> insert into words1(id) values(1);ERROR 1364 (HY000): Field 'word' doesn't have a default value

2.4 非严格模式导致的sql执行结果不一致

##表数据如下mysql> select * from words;+----+------+| id | word |+----+------+|  7 | aaaa ||  8 | bbbb ||  9 | cccc || 10 | bbbb || 11 | cccc |+----+------+5 rows in set (0.00 sec)##5.6环境执行mysql> select * from (select * from words order by id desc) a group by word  ;+----+------+| id | word |+----+------+|  7 | aaaa || 10 | bbbb || 11 | cccc |+----+------+3 rows in set (0.00 sec)##5.7环境执行mysql> select * from (select * from words order by id desc) a group by word  ;+----+------+| id | word |+----+------+|  7 | aaaa ||  8 | bbbb ||  9 | cccc |+----+------+3 rows in set (0.00 sec)##正确的姿势mysql> select max(id),word from (select * from words order by id desc) a group by word  ;+---------+------+| max(id) | word |+---------+------+|       7 | aaaa ||      10 | bbbb ||      11 | cccc |+---------+------+3 rows in set (0.00 sec)

官方连接：

转载于:https://my.oschina.net/u/3023401/blog/1794987

你可能感兴趣的文章