网络科技

    今日:654| 主题:246247
收藏本版
互联网、科技极客的综合动态。

[其他] Upgrading to MySQL 5.7? Beware of the new STRICT mode

[复制链接]
morpheus 发表于 2016-10-19 13:55:31
62 4

立即注册CoLaBug.com会员,免费获得投稿人的专业资料,享用更多功能,玩转个人品牌!

您需要 登录 才可以下载或查看,没有帐号?立即注册

x

Upgrading to MySQL 5.7? Beware of the new STRICT mode-1 (statements,discusses,default,controls,example)
This blog post discusses the ramifications of STRICT mode in MySQL 5.7.
    In short
   By default, MySQL 5.7 is much “stricter” than older versions of MySQL. That can make your application fail. To temporarily fix this, change the   SQL_MODE   to   NO_ENGINE_SUBSTITUTION   (same as in MySQL 5.6):
  [code]mysql> set global SQL_MODE="NO_ENGINE_SUBSTITUTION";
[/code]    MySQL 5.7, dates and default values
   The default   SQL_MODE   in MySQL 5.7 is:
  [code]ONLY_FULL_GROUP_BY,STRI*ANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
[/code]     That makes MySQL operate in “strict” mode for transactional tables.
        “Strict mode controls how MySQL handles invalid or missing values in data-change statements such as  INSERT  or  UPDATE  . A value can be invalid for several reasons. For example, it might have the wrong data type for the column, or it might be out of range. A value is missing when a new row to be inserted does not contain a value for a non- NULL column that has no explicit DEFAULT clause in its definition. (For a NULL column, NULL is inserted if the value is missing.) Strict mode also affects DDL statements such as CREATE TABLE.”
    http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-strict      That also brings up an interesting problem with the default value for the date/datetime column. Let’s say we have the following table in MySQL 5.7, and want to insert a row into it:
   [code]mysql> CREATE TABLE `events_t` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `event_date` datetime NOT NULL,
-> `profile_id` int(11) DEFAULT NULL,
-> PRIMARY KEY (`id`),
-> KEY `event_date` (`event_date`),
-> KEY `profile_id` (`profile_id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
-> ;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into events_t (profile_id) values (1);
ERROR 1364 (HY000): Field 'event_date' doesn't have a default value
[/code]    The   event_date   does not have a default value, and we are inserting a row without a value for   event_date   . That causes an error in MySQL 5.7. If we can’t use NULL, we will have to create a default value. In strict mod,e we can’t use “0000-00-00” either:
   [code]mysql> alter table events_t change event_date event_date datetime NOT NULL default '0000-00-00 00:00:00';
ERROR 1067 (42000): Invalid default value for 'event_date'
mysql> alter table events_t change event_date event_date datetime NOT NULL default '2000-00-00 00:00:00';
ERROR 1067 (42000): Invalid default value for 'event_date'
[/code]   We have to use a real date:
   [code]mysql> alter table events_t change event_date event_date datetime NOT NULL default '2000-01-01 00:00:00';
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> insert into events_t (profile_id) values (1);
Query OK, 1 row affected (0.00 sec)
[/code]   Or, a most likely much better approach is to change the application logic to:
   
       
  • allow NULLs, or   
  • always insert the real dates (i.e. use NOW() function), or   
  • change the table field to timestamp and update it automatically if no value has been assigned   
    Further reading
    Read the Morgan Tocker’s article on how to transition to MySQL 5.7 , and check the full sql_mode documentation
     PREVIOUS POST
   Related
友荐云推荐




上一篇:摩拜推出轻骑版单车Lite,共享单车进入融合之战了么?
下一篇:Why an ex-Apple design chief got Square to abandon the audio jack
酷辣虫提示酷辣虫禁止发表任何与中华人民共和国法律有抵触的内容!所有内容由用户发布,并不代表酷辣虫的观点,酷辣虫无法对用户发布内容真实性提供任何的保证,请自行验证并承担风险与后果。如您有版权、违规等问题,请通过"联系我们"或"违规举报"告知我们处理。

范冬梅 发表于 2016-10-21 02:44:27
要戒烟,早睡,好好的死。
回复 支持 反对

使用道具 举报

御龙在天 发表于 2016-10-21 03:00:38
你身材很好,好的连孙悟空看见你,都会给你三棍子了。
回复 支持 反对

使用道具 举报

chunglyc 发表于 2016-10-24 13:01:52
垃圾内容,路过为证。
回复 支持 反对

使用道具 举报

HgdrbJ2 发表于 2016-10-28 13:40:53
小时候的梦想并不是要当什么科学家,幻想自己是地主家的少爷,家有良田千顷,终日不学无术,没事领着一群狗奴才上街去调戏一下良家少女……
回复 支持 反对

使用道具 举报

*滑动验证:
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

我要投稿

推荐阅读

扫码访问 @iTTTTT瑞翔 的微博
回页顶回复上一篇下一篇回列表手机版
手机版/CoLaBug.com ( 粤ICP备05003221号 | 文网文[2010]257号 )|网站地图 酷辣虫

© 2001-2016 Comsenz Inc. Design: Dean. DiscuzFans.

返回顶部 返回列表