Tag Archives: MYSQL timestamp Default

[MySQL] mysql 5.5 and 5.6 timestamp default default value CURRENT_TIMESTAMP problem

Leave a reply

The behavior of TIMESTAMP in MySQL 5.5:

1. The implicit default value of the first TIMESTAMP NOT NULL field without a default value: CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

2. The implicit default value of the TIMESTAMP NOT NULL field with no default value set later: 0000-00-00 00:00:00

3. The table creation statement that does not support multiple CURRENT_TIMESTAMP default values ​​of 5.5 is similar to this:

CREATE TABLE `audit_log` (
  `id` int ( 10 ) unsigned NOT NULL AUTO_INCREMENT,
  `ent_id` int ( 10 ) unsigned NOT NULL DEFAULT ' 0 ' ,
  `rule_id` int ( 10 ) unsigned NOT NULL DEFAULT ' 0 ' ,
  rules_detail` VARCHAR `( 2048 ) the NOT NULL the DEFAULT '' the COMMENT ' rule details ' ,
  sender_email` VARCHAR `( 512 ) the NOT NULL the DEFAULT '' the COMMENT ' Sender mail review ' ,
  `receiver_email` varchar( 512 ) NOT NULL DEFAULT '' COMMENT'recipient 's mailbox ' ,
  subject` VARCHAR `( 512 ) the NOT NULL the DEFAULT '' the COMMENT ' theme ' ,
  `createtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `start_time` timestamp NOT NULL DEFAULT ' 0000-00-00 00:00:00 ' ,
  `end_time` timestamp NOT NULL DEFAULT ' 0000-00-00 00:00:00 ' ,
  status` tinyint `( 3 ) the NOT NULL the DEFAULT unsigned ' 1 ' the COMMENT ' current state (by 1, 2 rejected rejected timeout 3, 4 by timeout) ' ,
  reviewer_leader` VARCHAR `( 512 ) the NOT NULL the DEFAULT '' the COMMENT ' moderator ' ,
  PRIMARY KEY (`id`),
  KEY `idx_ent_id` (`ent_id`)
) ENGINE =InnoDB DEFAULT CHARSET=utf8 COMMENT = ' Audit log table '

The behavior of TIMESTAMP in MySQL 5.6:

Supports multiple CURRENT_TIMESTAMP default values, but does not support setting the default value to 0000-00-00 00:00:00

5.6 can be like this:

  `createtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `end_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,