网络科技

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

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

[复制链接]
morpheus 投递于 2016-10-19 13:55:31
86 4

Upgrading to MySQL 5.7? Beware of the new STRICT mode

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):
  1. mysql> set global SQL_MODE="NO_ENGINE_SUBSTITUTION";
复制代码
   MySQL 5.7, dates and default values
   The default   SQL_MODE   in MySQL 5.7 is:
  1. 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
复制代码
    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:
  
  1. mysql> CREATE TABLE `events_t` (
  2. -> `id` int(11) NOT NULL AUTO_INCREMENT,
  3. -> `event_date` datetime NOT NULL,
  4. -> `profile_id` int(11) DEFAULT NULL,
  5. -> PRIMARY KEY (`id`),
  6. -> KEY `event_date` (`event_date`),
  7. -> KEY `profile_id` (`profile_id`)
  8. -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
  9. -> ;
  10. Query OK, 0 rows affected (0.02 sec)
  11. mysql> insert into events_t (profile_id) values (1);
  12. ERROR 1364 (HY000): Field 'event_date' doesn't have a default value
复制代码
   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:
  
  1. mysql> alter table events_t change event_date event_date datetime NOT NULL default '0000-00-00 00:00:00';
  2. ERROR 1067 (42000): Invalid default value for 'event_date'
  3. mysql> alter table events_t change event_date event_date datetime NOT NULL default '2000-00-00 00:00:00';
  4. ERROR 1067 (42000): Invalid default value for 'event_date'
复制代码
  We have to use a real date:
  
  1. mysql> alter table events_t change event_date event_date datetime NOT NULL default '2000-01-01 00:00:00';
  2. Query OK, 0 rows affected (0.00 sec)
  3. Records: 0 Duplicates: 0 Warnings: 0
  4. mysql> insert into events_t (profile_id) values (1);
  5. Query OK, 1 row affected (0.00 sec)
复制代码
  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
小时候的梦想并不是要当什么科学家,幻想自己是地主家的少爷,家有良田千顷,终日不学无术,没事领着一群狗奴才上街去调戏一下良家少女……
回复 支持 反对

使用道具 举报

我要投稿

推荐阅读


回页顶回复上一篇下一篇回列表
手机版/CoLaBug.com ( 粤ICP备05003221号 | 文网文[2010]257号 | 粤公网安备 44010402000842号 )

© 2001-2017 Comsenz Inc.

返回顶部 返回列表