我试图创建如下表,但出现错误:
"updated_at"的默认值无效
我不知道该怎么办…查询中出了什么问题。
CREATE TABLE `posts` (
`id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`user_id` int(11) DEFAULT NULL,
`title` varchar(255) NOT NULL,
`slug` varchar(255) NOT NULL UNIQUE,
`views` int(11) NOT NULL DEFAULT '0',
`image` varchar(255) NOT NULL,
`body` text NOT NULL,
`published` tinyint(1) NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
根据SQL标准"0000-00-00 00:00:00"不是时间戳字段的有效值。您应该使用NULL
作为updated_at
列的默认值。
CREATE TABLE `posts` (
`id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`user_id` int(11) DEFAULT NULL,
`title` varchar(255) NOT NULL,
`slug` varchar(255) NOT NULL UNIQUE,
`views` int(11) NOT NULL DEFAULT '0',
`image` varchar(255) NOT NULL,
`body` text NOT NULL,
`published` tinyint(1) NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NULL ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
此外,ON UPDATE CURRENT_TIMESTAMP
部分与created_at
字段无关,但与updated_at
相关