歡迎訪問個人博客 http://blog.colinspace.com

根據(jù)報錯猜測可能是因為MySQL版本的問題，導(dǎo)出的SQL文件是從MySQL 5.6導(dǎo)出的， 目前Mac上面的MySQL版本是5.7

查詢官網(wǎng)發(fā)現(xiàn)：

參考地址https://dev.mysql.com/doc/refman/5.7/en/datetime.html

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.

故相關(guān)的默認值需要設(shè)置成'1000-01-01 00:00:00' 到 '9999-12-31 23:59:59' 之間即可

MySQL設(shè)置默認為當前時間日期

一般給創(chuàng)建時間和更新時間給定當前時間和日期在某些場景下是很有效果的。

新建測試表如下：

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

插入測試數(shù)據(jù)并驗證

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)

字段也可以設(shè)置成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

插入測試數(shù)據(jù)并驗證

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)

sql-mode 官網(wǎng)介紹https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html