Error : Invalid default value for 'datetime'

﹏ヽ暗。殇╰゛Y 2021-11-29 23:14 243阅读 0赞

出现原因由于数据库升到5.7+以后出现了默认值兼容性问题

解决办法：

**永久性解决：**

修改my.cnf文件

在mysqlId下面加上：

sql\_mode=ONLY\_FULL\_GROUP\_BY,STRICT\_TRANS\_TABLES,ERROR\_FOR\_DIVISION\_BY\_ZERO,NO\_AUTO\_CREATE\_USER,NO\_ENGINE\_SUBSTITUTION

[mysqld]
    #
    # * Basic Settings
    #
    user            = mysql
    pid-file        = /var/run/mysqld/mysqld.pid
    socket          = /var/run/mysqld/mysqld.sock
    port            = 3306
    basedir         = /usr
    datadir         = /var/lib/mysql
    tmpdir          = /tmp
    lc-messages-dir = /usr/share/mysql
    skip-external-locking
    #
    # Instead of skip-networking the default is now to listen only on
    # localhost which is more compatible and is not less secure.
    bind-address            = 127.0.0.1
    sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    #
    # * Fine Tuning
    #
    key_buffer_size         = 16M
    max_allowed_packet      = 16M
    thread_stack            = 192K
    thread_cache_size       = 8
    # This replaces the startup script and checks MyISAM tables if needed
    # the first time they are touched
    myisam-recover-options  = BACKUP
    #max_connections        = 100
    #table_cache            = 64
    #thread_concurrency     = 10
    #
    # * Query Cache Configuration
    #
    query_cache_limit       = 1M
    query_cache_size        = 16M
    #
    # * Logging and Replication

**临时性解决：**

mysql> set session sql\_mode='ONLY\_FULL\_GROUP\_BY,STRICT\_TRANS\_TABLES,ERROR\_FOR\_DIVISION\_BY\_ZERO,NO\_AUTO\_CREATE\_USER,NO\_ENGINE\_SUBSTITUTION';

参考文章：[https://blog.csdn.net/chenshun123/article/details/79676567][https_blog.csdn.net_chenshun123_article_details_79676567]

[https_blog.csdn.net_chenshun123_article_details_79676567]: https://blog.csdn.net/chenshun123/article/details/79676567