背景:在之前的升级过程中,为了对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过程中,如果被零除(或MOD(X,0)),则产生错误(否则为警告)。如果未给出该模式,被零除时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 BY
mysql> select id,count(word) from words group by word;
ERROR 1055 (42000): 'tom.words.id' isn't in GROUP BY
mysql> select id,word,count(word) from words group by word;
ERROR 1055 (42000): 'tom.words.id' isn't in GROUP BY
mysql> 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 tom
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> 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 1
mysql> insert into words(word) values(10/0);
ERROR 1365 (22012): Division by 0
mysql> 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 1
mysql> alter table words change `word` `word` varchar(3) DEFAULT NULL;
ERROR 1265 (01000): Data truncated for column 'word' at row 1
mysql> 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: 0
mysql> insert into words(id) values(-1);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql>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)
官方连接:点我查看