mysql 5.7 默认值问题_mysql5.7之后版本datetime默认值不能设置为0000-00-00的问题

根据报错猜测可能是因为MySQL版本的问题，导出的SQL文件是从MySQL 5.6导出的， 目前Mac上面的MySQL版本是5.7

查询官网发现：

The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD'

format. The supported range is '1000-01-01' to '9999-12-31'.

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values

in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of

'1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

故相关的默认值需要设置成'1000-01-01 00:00:00' 到 '9999-12-31 23:59:59' 之间即可

MySQL设置默认为当前时间日期

一般给创建时间和更新时间给定当前时间和日期在某些场景下是很有效果的。

新建测试表如下：

CREATE TABLE `temp_lc` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(20) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB

新增datetime 字段

mysql> alter table temp_lc add column regdate datetime default current_timestamp comment 'reg date';

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

插入测试数据并验证

mysql> insert into temp_lc(name)values('James') ;

Query OK, 1 row affected (0.00 sec)

mysql> select * from temp_lc ;

+----+-------+---------------------+

| id | name | regdate |

+----+-------+---------------------+

| 1 | James | 2017-10-18 19:51:25 |

+----+-------+---------------------+

1 row in set (0.00 sec)

字段也可以设置成timestamp 类型

mysql> alter table temp_lc add column regdatet timestamp default current_timestamp comment 'reg date2';

Query OK, 0 rows affected (0.05 sec)

Records: 0 Duplicates: 0 Warnings: 0

插入测试数据并验证

mysql> insert into temp_lc (name) values('Reg-timestamp-col') ;

Query OK, 1 row affected (0.00 sec)

mysql> select * from temp_lc ;

+----+-------------------+---------------------+---------------------+

| id | name | regdate | regdatet |

+----+-------------------+---------------------+---------------------+

| 1 | James | 2017-10-18 19:51:25 | 2017-10-18 20:06:06 |

| 2 | Reg-timestamp-col | 2017-10-18 20:06:25 | 2017-10-18 20:06:25 |

+----+-------------------+---------------------+---------------------+

2 rows in set (0.00 sec)

附加

查看当前的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)