mysql的datetime与timestamp设置null对比

最新推荐文章于 2024-04-15 15:32:40 发布

原创最新推荐文章于 2024-04-15 15:32:40 发布 · 1.2k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

本文介绍了在MySQL 5.7中，针对timestamp和datetime类型的NOTNULL DEFAULT current_timestamp()设置，timestamp会自动填充当前时间，而datetime在null时设置会报错。通过实例对比了两种行为并引用官方文档进行说明。

版本:5.7

结论：对于NOT NULL DEFAULT current_timestamp()，若是timestamp类型设置为null会被设置成当前时间，而对于datetime会报错

1.timestamp类型

CREATE TABLE `table_1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `create_time` timestamp(6) NOT NULL DEFAULT current_timestamp(6) COMMENT '创建时间',
   PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ;

insert into table_1(name, create_time) values('1', null);

select * from table_1;

结果：

2.datetime类型

CREATE TABLE `table_2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `create_time` datetime NOT NULL DEFAULT current_timestamp() COMMENT '创建时间',
   PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ;


insert into table_2(name, create_time) values('1', null)

结果：

参考文档

MySQL :: MySQL 5.7 Reference Manual :: 11.2.6 Automatic Initialization and Updating for TIMESTAMP and DATETIMEhttps://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html

In addition, if the explicit_defaults_for_timestamp system variable is disabled, you can initialize or update any TIMESTAMP (but not DATETIME) column to the current date and time by assigning it a NULL value, unless it has been defined with the NULL attribute to permit NULL values.