CREATE TABLE `timestamp_test` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `name` VARCHAR(255) NOT NULL, `created` TIMESTAMP NOT NULL, `updated` TIMESTAMP NOT NULL, PRIMARY KEY (`id`) ) COLLATE='utf8_general_ci' ENGINE=InnoDB; 这样一条建表语句,注意红色部分,没有给两个timestamp字段设默认值,那么产生的表是什么样的呢? 经过试验,执行后的表结构自动变成了:
CREATE TABLE `timestamp_test` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `name` VARCHAR(255) NOT NULL, `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `updated` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`id`) ) COLLATE='utf8_general_ci' ENGINE=InnoDB; 坑爹,第一个timestamp字段竟然变成了“自动更新”字段,这明显不是我想要的,所以如果仅仅是希望用 timestamp来代替 int存储时间戳,记得一定要设默认值 如下蓝色部分,这样才不会出现不可预料的结果:
CREATE TABLE `timestamp_test` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `name` VARCHAR(255) NOT NULL, `created` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00', `updated` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`id`) ) COLLATE='utf8_general_ci' ENGINE=InnoDB;