mysql timestamp 值不合法问题

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

Create Table: CREATE TABLE `RecruitmentDesc` (
`sn` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号（自增字段）',
`areaSn` int(11) NOT NULL COMMENT '地区编号',
`title` varchar(50) NOT NULL COMMENT '职位标题',
`content` text NOT NULL COMMENT '职位描述',
`status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '状态 1可用 2不可用',
`personNum` int(11) NOT NULL DEFAULT '0' COMMENT '招聘人数 0-若干',
`actionTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '启用时间',
`dueTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '到期时间',
`createTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '创建时间',
`updateTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '更新时间',
PRIMARY KEY (`sn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='诚聘英才表'
1 row in set (0.00 sec)mysql> ALTER TABLE `RecruitmentDesc` MODIFY COLUMN `status` TINYINT NOT NULL DEFAULT 2 COMMENT '状态 1可用 2不可用';
ERROR 1067 (42000): Invalid default value for 'dueTime'Create Table: CREATE TABLE `RecruitmentDesc111` (
`sn` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号（自增字段）',
`areaSn` int(11) NOT NULL COMMENT '地区编号',
`title` varchar(50) NOT NULL COMMENT '职位标题',
`content` text NOT NULL COMMENT '职位描述',
`status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '状态 1可用 2不可用',
`personNum` int(11) NOT NULL DEFAULT '0' COMMENT '招聘人数 0-若干',
`actionTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '启用时间',
`dueTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '到期时间',
`createTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '创建时间',
`updateTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '更新时间',
PRIMARY KEY (`sn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='诚聘英才表tinyint
从 0 到 255 的整型数据。存储大小为 1 字节。ALTER TABLE `RecruitmentDesc` MODIFY COLUMN `dueTime` timestamp NOT NULL DEFAULT '2016-03-23 00:00:00' , MODIFY COLUMN `createTime` timestamp NOT NULL DEFAULT '2016-03-23 00:00:00' , MODIFY COLUMN `updateTime` timestamp NOT NULL DEFAULT '2016-03-23 00:00:00' ;ALTER TABLE `RecruitmentDesc` MODIFY COLUMN `dueTime` timestamp NOT NULL DEFAULT now() , MODIFY COLUMN `createTime` timestamp NOT NULL DEFAULT now() , MODIFY COLUMN `updateTime` timestamp NOT NULL DEFAULT now() ;create table test100(id TINYINT,dueTime NOT NULL DEFAULT now());mysql> desc test100
-> ;
+---------+------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------+------+-----+-------------------+-------+
| id | tinyint(4) | YES | | NULL | |
| dueTime | timestamp | NO | | CURRENT_TIMESTAMP | |
+---------+------------+------+-----+-------------------+-------+
2 rows in set (0.00 sec)mysql> select * from test100;
Empty set (0.00 sec)mysql> insert into test100 values(1, default);
Query OK, 1 row affected (0.03 sec)mysql> select * from test100;
+------+---------------------+
| id | dueTime |
+------+---------------------+
| 1 | 2016-03-23 17:37:19 |
+------+---------------------+
1 row in set (0.00 sec)mysql> insert into test100(id) values(20);
Query OK, 1 row affected (0.01 sec)mysql> select * from test00;
ERROR 1146 (42S02): Table 'zjzc.test00' doesn't exist
mysql> select * from test100;
+------+---------------------+
| id | dueTime |
+------+---------------------+
| 1 | 2016-03-23 17:37:19 |
| 20 | 2016-03-23 17:38:24 |
+------+---------------------+
2 rows in set (0.00 sec)