MyException - 我的异常网
当前位置:我的异常网» MySQL » (转)MySQL 5.7默认SQL模式带来的有关问题总结

(转)MySQL 5.7默认SQL模式带来的有关问题总结

www.MyException.Cn  网友分享于:2013-10-22  浏览:0次
(转)MySQL 5.7默认SQL模式带来的问题总结
http://www.ywnds.com/?p=8865

在使用MySQL 5.7版本时,已经碰到了两起因为SQL_MODE的改变而导致的问题了,究其原因都是因为MySQL 5.7控制的更加严格了,所以在MySQL 5.6或MySQL 5.5有些SQL语句就无法在MySQL 5.7执行了(会给开发带来一个很诡异的问题,就是代码一样、环境一样、SQL一样、测试跟正式怎么就报错呢?)。

说一下我碰到的两起事件:

1、某些GROUP BY的SQL语句无法执行了。

2、创建表时使用日期数据类型指定的默认值为0000-00-00时报错。

先看一下,MySQL 5.6&MySQL 5.7默认的SQL模式。


# MySQL 5.6 Default SQL_MODE;
mysql> select @@sql_mode;
+--------------------------------------------+
| @@sql_mode                                 |
+--------------------------------------------+
| STRICT_TRANS_TABLES,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)

# MySQL 5.6 Default SQL_MODE;
mysql> select @@sql_mode;
+--------------------------------------------+
| @@sql_mode                                 |
+--------------------------------------------+
| STRICT_TRANS_TABLES,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)
先简单说一下每个参数的含义:

ONLY_FULL_GROUP_BY
在严格模式下,不要让GROUP BY部分中的查询指向未选择的列,否则报错。

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

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

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

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

NO_ENGINE_SUBSTITUTION
如果需要的存储引擎被禁用或未编译,可以防止自动替换存储引擎。

STRICT_TRANS_TABLES
为事务存储引擎启用严格模式,也可能为非事务存储引擎启用严格模式,非法数据值被拒绝,下面有详细说明。

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

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

对于非事务表,如果插入或更新的第1行出现坏值,两种模式的行为相同。语句被放弃,表保持不变。如果语句插入或修改多行,并且坏值出现在第2或后面的行,结果取决于启用了哪个严格选项:

对于STRICT_ALL_TABLES,MySQL返回错误并忽视剩余的行。但是,在这种情况下,前面的行已经被插入或更新。这说明你可以部分更新,这可能不是你想要的。要避免这点,最好使用单行语句,因为这样可以不更改表即可以放弃。

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

严格模式不允许非法日期,例如’2004-04-31’。它不允许禁止日期使用“零”部分,例如’2004-04-00’或”零”日期。要想禁止,应在严格模式基础上,启用NO_ZERO_IN_DATE和NO_ZERO_DATE SQL模式。

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

回答刚开始提出的两个问题?

1、某些GROUP BY的SQL语句无法执行了。

这是因为MySQL 5.7默认加入了ONLY_FULL_GROUP_BY参数。在MySQL的sql_mode是非ONLY_FULL_GROUP_BY语义时。一条select语句,MySQL允许target list中输出的表达式是除聚集函数或group by column以外的表达式,这个表达式的值可能在经过group by操作后变成undefined,例如:


select * from tt group by id;
1
select * from tt group by id;
而对于语义限制都比较严谨的多家数据库,如SQLServer、Oracle、PostgreSql都不支持select target list中出现语义不明确的列,这样的语句在这些数据库中是会被报错的,所以从MySQL 5.7版本开始修正了这个语义,就是我们所说的ONLY_FULL_GROUP_BY语义。而正确的写法如下:


select id from tt group by id;
select id,max(age) from tt group by id;
1
2
select id from tt group by id;
select id,max(age) from tt group by id;
所以ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义,简单的说来,在ONLY_FULL_GROUP_BY模式下,target list中的值要么是来自于聚集函数的结果,要么是来自于group by list中的表达式的值。更详细的请看:MySQL 5.7默认ONLY_FULL_GROUP_BY语义介绍

2、创建表时使用日期数据类型指定的默认值为0000-00-00时报错。

这是因为MySQL 5.7默认加入了NO_ZERO_DATE和NO_ZERO_IN_DATE参数。通过上面的介绍,这两个参数还是有些区别的,NO_ZERO_DATE是完全匹配默认值为0000-00-00时才限制,而NO_ZERO_IN_DATE是在匹配到月或日任意为00时限制(对年不限制)。

当数据类型为date或datetime时,并且使用了NO_ZERO_IN_DATE限制,那么会有如下几种情况:


# 不合法默认值;
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;

# 不合法默认值;
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;
如果使用数据类型为timestamp就有点不太一样了。

timestamp类型取值范围:1970-01-01 00:00:00到2037-12-31 23:59:59。也就是说默认值最少需要调整为1970-01-02 00:00:00。


CREATE TABLE `test` (
  `mid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `start_time` timestamp NOT NULL DEFAULT '1970-01-02 00:00:00',
  `end_time` timestamp NOT NULL DEFAULT '2037-12-31 23:59:59',
  PRIMARY KEY (`mid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `test` (
  `mid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `start_time` timestamp NOT NULL DEFAULT '1970-01-02 00:00:00',
  `end_time` timestamp NOT NULL DEFAULT '2037-12-31 23:59:59',
  PRIMARY KEY (`mid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
最大值或最小值在timestamp合法范围内才行。网上大概看了看,部分人碰到这个问题都是数据库升级的时候,老的表结构无法在MySQL 5.7应用了。如果想调整SQL模式,直接在配置文件写入sql_mode参数跟上对应的模式参数即可。

3、修改字段类型小于小于最长字段时报错。

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


mysql> select * from dd;
+--------------------------------+
| log                            |
+--------------------------------+
| 一二三四五六七八九十              |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select * from dd;
+--------------------------------+
| log                            |
+--------------------------------+
| 一二三四五六七八九十              |
+--------------------------------+
1 row in set (0.00 sec)

mysql> set sql_mode='STRICT_TRANS_TABLES';
Query OK, 0 rows affected (0.00 sec)

mysql> alter table dd change column log log varchar(9);        
ERROR 1265 (01000): Data truncated for column 'log' at row 1
                                
mysql> set sql_mode='';                               
Query OK, 0 rows affected (0.00 sec)

mysql> alter table dd change column log log varchar(9);
Query OK, 1 row affected, 1 warning (0.02 sec)    
Records: 1  Duplicates: 0  Warnings: 1

mysql> set sql_mode='STRICT_TRANS_TABLES';
Query OK, 0 rows affected (0.00 sec)

mysql> alter table dd change column log log varchar(9);        
ERROR 1265 (01000): Data truncated for column 'log' at row 1
                                
mysql> set sql_mode='';                               
Query OK, 0 rows affected (0.00 sec)

mysql> alter table dd change column log log varchar(9);
Query OK, 1 row affected, 1 warning (0.02 sec)    
Records: 1  Duplicates: 0  Warnings: 1

MariaDB [dkey]> select * from dd;                              
+-----------------------------+
| log                         |
+-----------------------------+
| 一二三四五六七八九             |
+-----------------------------+
1 row in set (0.00 sec)

MariaDB [dkey]> select * from dd;                              
+-----------------------------+
| log                         |
+-----------------------------+
| 一二三四五六七八九             |
+-----------------------------+
1 row in set (0.00 sec)
可以看到非严格模式,默认把多余数据给截断了。

待续。。。如果再碰到什么问题接着补充。

文章评论

Web开发人员为什么越来越懒了?
Web开发人员为什么越来越懒了?
看13位CEO、创始人和高管如何提高工作效率
看13位CEO、创始人和高管如何提高工作效率
什么才是优秀的用户界面设计
什么才是优秀的用户界面设计
科技史上最臭名昭著的13大罪犯
科技史上最臭名昭著的13大罪犯
老程序员的下场
老程序员的下场
程序员的一天:一寸光阴一寸金
程序员的一天:一寸光阴一寸金
那些性感的让人尖叫的程序员
那些性感的让人尖叫的程序员
2013年美国开发者薪资调查报告
2013年美国开发者薪资调查报告
写给自己也写给你 自己到底该何去何从
写给自己也写给你 自己到底该何去何从
程序员的鄙视链
程序员的鄙视链
60个开发者不容错过的免费资源库
60个开发者不容错过的免费资源库
程序员眼里IE浏览器是什么样的
程序员眼里IE浏览器是什么样的
初级 vs 高级开发者 哪个性价比更高?
初级 vs 高级开发者 哪个性价比更高?
10个帮程序员减压放松的网站
10个帮程序员减压放松的网站
鲜为人知的编程真相
鲜为人知的编程真相
编程语言是女人
编程语言是女人
Java程序员必看电影
Java程序员必看电影
亲爱的项目经理,我恨你
亲爱的项目经理,我恨你
团队中“技术大拿”并非越多越好
团队中“技术大拿”并非越多越好
代码女神横空出世
代码女神横空出世
程序员应该关注的一些事儿
程序员应该关注的一些事儿
程序员都该阅读的书
程序员都该阅读的书
要嫁就嫁程序猿—钱多话少死的早
要嫁就嫁程序猿—钱多话少死的早
10个调试和排错的小建议
10个调试和排错的小建议
老美怎么看待阿里赴美上市
老美怎么看待阿里赴美上市
为什么程序员都是夜猫子
为什么程序员都是夜猫子
我是如何打败拖延症的
我是如何打败拖延症的
程序员必看的十大电影
程序员必看的十大电影
旅行,写作,编程
旅行,写作,编程
十大编程算法助程序员走上高手之路
十大编程算法助程序员走上高手之路
每天工作4小时的程序员
每天工作4小时的程序员
程序员周末都喜欢做什么?
程序员周末都喜欢做什么?
我跳槽是因为他们的显示器更大
我跳槽是因为他们的显示器更大
做程序猿的老婆应该注意的一些事情
做程序猿的老婆应该注意的一些事情
聊聊HTTPS和SSL/TLS协议
聊聊HTTPS和SSL/TLS协议
程序猿的崛起——Growth Hacker
程序猿的崛起——Growth Hacker
Web开发者需具备的8个好习惯
Web开发者需具备的8个好习惯
程序员最害怕的5件事 你中招了吗?
程序员最害怕的5件事 你中招了吗?
Google伦敦新总部 犹如星级庄园
Google伦敦新总部 犹如星级庄园
2013年中国软件开发者薪资调查报告
2013年中国软件开发者薪资调查报告
“肮脏的”IT工作排行榜
“肮脏的”IT工作排行榜
当下全球最炙手可热的八位少年创业者
当下全球最炙手可热的八位少年创业者
漫画:程序员的工作
漫画:程序员的工作
程序员和编码员之间的区别
程序员和编码员之间的区别
总结2014中国互联网十大段子
总结2014中国互联网十大段子
不懂技术不要对懂技术的人说这很容易实现
不懂技术不要对懂技术的人说这很容易实现
“懒”出效率是程序员的美德
“懒”出效率是程序员的美德
那些争议最大的编程观点
那些争议最大的编程观点
 程序员的样子
程序员的样子
软件开发程序错误异常ExceptionCopyright © 2009-2015 MyException 版权所有