今天开发的同事提交过来一个sql变更，在部署的时候发现了一个问题。 语句是一个简单的create语句 CREATE TABLE `test_user` ( `openid` varchar(64) NOT NULL, `amount` varchar(11) DEFAULT 0, `create_time` datetime DEFAULT CURRENT_TIMESTAMP, `update_time` datetime DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`openid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 报错内容为： ERROR 1067 (42000) at line 1: Invalid default value for 'create_time' 首先这个看起来不是一个语法问题，部署使用的环境是5.5 这个时候手头有一套虚拟机测试环境，立马实验了一下，发现在5.6中竟然没有任何问题。 得到的信息如下 Query OK, 0 rows affected (0.13 sec) 所以这个问题引起了我的注意。 我做了下面几个测试，首先current_timestamp肯定是可用的。看看时间的情况。 select current_timestamp, current_timestamp(); +---------------------+---------------------+ | current_timestamp | current_timestamp() | +---------------------+---------------------+ | 2015-11-23 18:31:25 | 2015-11-23 18:31:25 | +---------------------+---------------------+ 1 row in set (0.00 sec) 我们创建一个测试表来简单测试一下看看问题到底在哪里。 create table test(col1 datetime DEFAULT CURRENT_TIMESTAMP); ERROR 1067 (42000): Invalid default value for 'col1' 看起来似乎是添加default值的时候出了问题。 > create table test(col1 datetime DEFAULT ''); ERROR 1067 (42000): Invalid default value for 'col1' 添加空值，也是不可以。 > create table test(col1 datetime DEFAULT '2015-11-23 18:31:25'); Query OK, 0 rows affected (0.02 sec) 添加一个固定的静态默认值，这样就可以了。 同时查看了一些文章，有的说不能添加默认值，这个说法应该是不成立的，只能说是不能添加动态的默认值。还有一种说法是default的动态默认值是在5.6.5才开始支持。 这部分内容在 MySQL 官方文档中也有说明。http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html 那么这个问题还是需要解决，怎么解决呢。首先不可能为了这个操作先把 数据库 升级到5.6 那么解决方法就有两个。 一个是就是取消默认值，一个就是字段类型改为timestamp 那么问题来了，datetime和timestamp有啥区别和联系，如果没有记错还有一个类型时date,这三种数据类型有啥区别和关系 我们创建一个表，含有三个字段，datetime,timestamp,date create table test(date1 datetime,date2 timestamp,date3 date); 然后插入三个值，来看看有什么差别。 mysql> insert into test values(current_date,current_timestamp,current_date); Query OK, 1 row affected (0.00 sec) mysql> select *from test; +---------------------+---------------------+------------+ | date1 | date2 | date3 | +---------------------+---------------------+------------+ | 2015-11-23 00:00:00 | 2015-11-23 23:21:35 | 2015-11-23 | +---------------------+---------------------+------------+ 可以看出datetime和timestamp其实是精确到秒的，date是精确到日。 那么datetime和timestamp有啥区别。 datetime和timestamp的存储占用空间不同，datetime占用8个字节，timestamp占用4个字节，所以说timestamp支 持的时间范围要窄一些。范围为：1970-01-01 08:00:01到2038-01-19 11:14:07 而datetime支持的时间范围则要大很多。1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 简单来说，解放前的数据用timestamp就不合适了,而datetime则要更宽泛一些。 来简单验证一下。如果对timestamp列添加超过时间范围的值，则会报错。 mysql> insert into test values('2038-01-19 11:14:08','2038-01-19 11:14:08',current_date); ERROR 1292 (22007): Incorrect datetime value: '2038-01-19 11:14:08' for column 'date2' at row 1 再次对timestamp插入最大值。 mysql> insert into test values('2038-01-19 11:14:08','2038-01-19 11:14:07',current_date); mysql> select *from test; +---------------------+---------------------+------------+ | date1 | date2 | date3 | +---------------------+---------------------+------------+ | 2015-11-23 00:00:00 | 2015-11-23 23:21:35 | 2015-11-23 | | 2038-01-19 11:14:08 | 2038-01-19 11:14:07 | 2015-11-23 | +---------------------+---------------------+------------+ mysql> insert into test values('9999-12-31 23:59:59','2038-01-19 11:14:07',current_date); Query OK, 1 row affected (0.00 sec) 对datetime插入最大值 mysql> insert into test values('9999-12-31 23:59:59','2038-01-19 11:14:07',current_date); mysql> select *from test; +---------------------+---------------------+------------+ | date1 | date2 | date3 | +---------------------+---------------------+------------+ | 2015-11-23 00:00:00 | 2015-11-23 23:21:35 | 2015-11-23 | | 2038-01-19 11:14:08 | 2038-01-19 11:14:07 | 2015-11-23 | | 9999-12-31 23:59:59 | 2038-01-19 11:14:07 | 2015-11-23 | +---------------------+---------------------+------------+ 除此之外，datetime和timestamp还有一个区别，就是timestamp对于Insert，update操作会默认设置为current_timestamp 比如对datetime插入值，timestamp,date保留为空，结果如下： mysql> insert into test(date1) values('2038-01-19 11:14:06'); mysql> select *from test; +---------------------+---------------------+------------+ | date1 | date2 | date3 | +---------------------+---------------------+------------+ | 2015-11-23 00:00:00 | 2015-11-23 23:21:35 | 2015-11-23 | | 2038-01-19 11:14:08 | 2038-01-19 11:14:07 | 2015-11-23 | | 9999-12-31 23:59:59 | 2038-01-19 11:14:07 | 2015-11-23 | | 2038-01-19 11:14:06 | 2015-11-23 23:30:22 | NULL | +---------------------+---------------------+------------+ 4 rows in set (0.00 sec) 如果只对timestamp输入值，datetime和date为空，效果就大大不同 insert into test(date2) values('2038-01-19 11:14:06'); mysql> select *from test; +---------------------+---------------------+------------+ | date1 | date2 | date3 | +---------------------+---------------------+------------+ | 2015-11-23 00:00:00 | 2015-11-23 23:21:35 | 2015-11-23 | | 2038-01-19 11:14:08 | 2038-01-19 11:14:07 | 2015-11-23 | | 9999-12-31 23:59:59 | 2038-01-19 11:14:07 | 2015-11-23 | | 2038-01-19 11:14:06 | 2015-11-23 23:30:22 | NULL | | NULL | 2038-01-19 11:14:06 | NULL | +---------------------+---------------------+------------+ 这种类型的问题在oracle中就会是另外一种情况，oracle中对于timestamp的类型，精度要比date要高。 来简单做一个测试 SQL> create table test(date1 date,date2 timestamp); Table created. SQL> col name format a20 其实这个时候来查看timestamp，可以看到一个timestamp(6)字样的类型。 SQL> desc test Name Null? Type ----------------------------------------- -------- ---------------------------- DATE1 DATE DATE2 TIMESTAMP(6) SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss'; SQL> insert into test values(sysdate,sysdate); SQL> col date2 format a30 DATE1 DATE2 ------------------- ------------------------------ 2015-11-23 23:03:35 23-NOV-15 11.03.35.000000 PM 如果插入systimestamp，结果会有一些差别。 SQL> insert into test values(systimestamp,systimestamp); 1 row created. SQL> select *from test; DATE1 DATE2 ------------------- ------------------------------ 2015-11-23 23:03:35 23-NOV-15 11.03.35.000000 PM 2015-11-23 23:05:08 23-NOV-15 11.05.08.378586 PM 其实在oracle中时间的类型还有很多，比如timestamp with timezone等等，支持的幅度也更大。 如果上面的问题在oracle中，是否可以支持动态的默认值呢，肯定可以，因为我们似乎已经习惯这么用了。