mysql timestamp字段规范使用详情

目录
  • 1. 前言
  • 2. mysql中的时间字段
    • 2.1. 数据的存储方式
    • 2.2. DATETIME和TIMESTMAP的区别
  • 3. timestamp字段处理默认值和null值时的行为
    • 3.1. 参数禁用
    • 3.2. 参数启用
  • 4. 总结

1. 前言

这个世界离不开时间,同样,数据库中也是如此,表中的每条记录除了数据模型的时间字段(如生日,生产日期,出发日期等),一般至少还有两个固定的时间字段:记录插入时间,记录更新时间。

然而,看似很简单时间字段,谁能想到会导致应用报错,引发血案:

个中缘由,正是接下来要讲到的。

2. mysql中的时间字段

因时间字段的一些特性与版本有关,且目前我司统一使用的mysql 5.7版本,因此本文内容都基于mysql 5.7。
mysql时间相关的字段主要有DATE、DATETIME、TIMESTAMP。

其中datatime和timestamp字段都可以包含小数,如datetime(6),字节长度的可变部分(0-3)由小数位数决定:

2.1. 数据的存储方式

DATE:

3个字节的整型,按照这种方式进行压缩: YYYY×16×32 + MM×32 + DD

DATETIME:

整数部分5个字节,由以下部分组成

TIMESTAMP:

整数部分4个字节,存储从(‘1970-01-01 00:00:00’ UTC)到指定时间的秒数;

timestamp类型是4个字节,最大值是2的31次方减1,也就是2147483647,转换成北京时间就是2038-01-19 11:14:07

2.2. DATETIME和TIMESTMAP的区别

  • 数据的存储方式决定了timestamp的计算和索引效率比datetime更快;
  • timestamp存储的时间范围比datetime小很多;
  • timestamp数据显示时要根据时区换算,datetime数据显示时不受时区影响;
admin@test 04:42:41>show variables like 'time_zone';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| time_zone   | +08:00 |
+---------------+--------+
admin@test 04:42:42>create table t1(dt datetime,ts timestamp);
admin@test 04:43:07>insert into t1 values(now(),now());
admin@test 04:43:17>select * from t1;
+---------------------+---------------------+
| dt         | ts         |
+---------------------+---------------------+
| 2021-03-27 16:43:17 | 2021-03-27 16:43:17 |
+---------------------+---------------------+
admin@test 04:43:50>set time_zone='+09:00';
admin@test 04:44:00>select * from t1;
+---------------------+---------------------+
| dt         | ts         |
+---------------------+---------------------+
| 2021-03-27 16:43:17 | 2021-03-27 17:43:17 |
+---------------------+---------------------+
admin@test 04:44:07>

timestamp在处理默认值和null值时的行为时受mysql参数explicit_defaults_for_timestamp控制,datatime不受影响。

3. timestamp字段处理默认值和null值时的行为

3.1. 参数禁用

当禁用该值时(explicit_defaults_for_timestamp=0),mysql启用timestamp字段的特有行为(和数字、字符串等类型的表现不同),

具体特性如下:

  • timestamp字段默认设置为not null
  • 表中的第一个timestamp字段插入时默认设置当前时间,更新时,默认更新为当前时间,即默认设置为以下特性:NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  • 表中的第二个timestamp字段默认为'0000-00-00 00:00:00'
  • 显式向timestamp字段插入null值时,不会报错,且都设置为当前时间;
  • 对datetime字段的行为无影响。
admin@test 05:49:00>create table t2(id int auto_increment, name varchar(100), dt1 datetime, ts1 timestamp, ts2 timestamp, primary key(id));
admin@test 05:49:48>show create table t2;
±------±--------------------------------------------------------------------------+
| Table | Create Table |
±------±--------------------------------------------------------------------------+
| t2 | CREATE TABLE t2 (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(100) DEFAULT NULL,
dt1 datetime DEFAULT NULL,
ts1 timestamp NULL DEFAULT NULL,
ts2 timestamp NULL DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
±------±--------------------------------------------------------------------------+
1 row in set (0.00 sec)

admin@test 05:50:20>insert into t2(name) values(‘a1');
Query OK, 1 row affected (0.00 sec)

admin@test 05:51:07>select * from t2;
±—±-----±-----±-----±-----+
| id | name | dt1 | ts1 | ts2 |
±—±-----±-----±-----±-----+
| 1 | a1 | NULL | NULL | NULL |
±—±-----±-----±-----±-----+
1 row in set (0.00 sec)

##注:插入记录时,默认为null
admin@test 05:54:20>update t2 set name=‘aa1' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

admin@test 05:54:31>select * from t2;
±—±-----±-----±-----±-----+
| id | name | dt1 | ts1 | ts2 |
±—±-----±-----±-----±-----+
| 1 | aa1 | NULL | NULL | NULL |
±—±-----±-----±-----±-----+
1 row in set (0.00 sec)

##注:更新记录时,默认为null
admin@test 05:58:10>create table t3(id int auto_increment,name varchar(100),ts1 timestamp not null default current_timestamp,primary key(id));
admin@test 05:58:18>insert into t3(name) values(‘a1');
Query OK, 1 row affected (0.00 sec)

admin@test 05:58:22>select * from t3;
±—±-----±--------------------+
| id | name | ts1 |
±—±-----±--------------------+
| 1 | a1 | 2021-03-23 17:58:22 |
±—±-----±--------------------+
1 row in set (0.00 sec)
##注:创建表手动设置not null default current_timestamp,插入记录不含timestamp字段时,默认为当前时间

admin@test 05:58:25>insert into t3(name,ts1) values(‘a1',null);
ERROR 1048 (23000): Column ‘ts1' cannot be null
##注:timestamp字段显式插入null时,报错Column ‘ts1' cannot be null

admin@test 05:59:11>create table t4(id int auto_increment,name varchar(100),ts1 timestamp not null ,primary key(id));
Query OK, 0 rows affected (0.04 sec)

admin@test 05:59:44>insert into t4(name) values(‘a1');
ERROR 1364 (HY000): Field ‘ts1' doesn't have a default value
admin@test 05:59:49>
##注:创建表手动设置not null,插入记录不含timestamp字段时,报错Field doesn't have a default value
admin@test 05:59:50>insert into t4(name,ts1) values(‘a1',null);
ERROR 1048 (23000): Column ‘ts1' cannot be null
admin@test 05:59:57>
##注:timestamp字段显式插入null时,报错Column ‘ts1' cannot be null

3.2. 参数启用

当启用该值时(explicit_defaults_for_timestamp=1),mysql禁用timestamp字段的特有行为,具体表现和数字、字符串类型一样。

  • timestamp字段默认属性是“NULL DEFAULT NULL”;
  • timestamp字段手动设置了not null和default后,显式插入null值会报错:Column cannot be null;
  • timestamp字段同时设置了not null但未设置default后,显式插入null值会报错:Column cannot be null,插入记录不含timestamp字段时会报错Field doesn’t have a default value;
  • 对datetime字段的行为无影响。
admin@test 05:49:00>create table t2(id int auto_increment, name varchar(100), dt1 datetime, ts1 timestamp, ts2 timestamp, primary key(id));
admin@test 05:49:48>show create table t2;
+-------+---------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------+
| t2 | CREATE TABLE `t2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) DEFAULT NULL,
`dt1` datetime DEFAULT NULL,
`ts1` timestamp NULL DEFAULT NULL,
`ts2` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+---------------------------------------------------------------------------+
1 row in set (0.00 sec)
admin@test 05:50:20>insert into t2(name) values(‘a1');
Query OK, 1 row affected (0.00 sec)

admin@test 05:51:07>select * from t2;
±—±-----±-----±-----±-----+
| id | name | dt1 | ts1 | ts2 |
±—±-----±-----±-----±-----+
| 1 | a1 | NULL | NULL | NULL |
±—±-----±-----±-----±-----+
1 row in set (0.00 sec)

## 注:插入记录时,默认为null
admin@test 05:54:20>update t2 set name=‘aa1' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

admin@test 05:54:31>select * from t2;
±—±-----±-----±-----±-----+
| id | name | dt1 | ts1 | ts2 |
±—±-----±-----±-----±-----+
| 1 | aa1 | NULL | NULL | NULL |
±—±-----±-----±-----±-----+
1 row in set (0.00 sec)

## 注:更新记录时,默认为null
admin@test 05:58:10>create table t3(id int auto_increment,name varchar(100),ts1 timestamp not null default current_timestamp,primary key(id));
admin@test 05:58:18>insert into t3(name) values(‘a1');
Query OK, 1 row affected (0.00 sec)

admin@test 05:58:22>select * from t3;
±—±-----±--------------------+
| id | name | ts1 |
±—±-----±--------------------+
| 1 | a1 | 2021-03-23 17:58:22 |
±—±-----±--------------------+
1 row in set (0.00 sec)

##注:创建表手动设置not null default current_timestamp,插入记录不含timestamp字段时,默认为当前时间
admin@test 05:58:25>insert into t3(name,ts1) values(‘a1',null);
ERROR 1048 (23000): Column ‘ts1' cannot be null
##注:timestamp字段显式插入null时,报错Column ‘ts1' cannot be null

admin@test 05:59:11>create table t4(id int auto_increment,name varchar(100),ts1 timestamp not null ,primary key(id));
Query OK, 0 rows affected (0.04 sec)

admin@test 05:59:44>insert into t4(name) values(‘a1');
ERROR 1364 (HY000): Field ‘ts1' doesn't have a default value
admin@test 05:59:49>
##注:创建表手动设置not null,插入记录不含timestamp字段时,报错Field doesn't have a default value
admin@test 05:59:50>insert into t4(name,ts1) values(‘a1',null);
ERROR 1048 (23000): Column ‘ts1' cannot be null
admin@test 05:59:57>
##注:timestamp字段显式插入null时,报错Column ‘ts1' cannot be null

4. 总结

启用该参数(explicit_defaults_for_timestamp=1)

timestamp字段在null、default属性的表现和其他普通字段表现类似:

  • 如果没有显式设置default值,该值的维护完全需要应用程序显式插入和更新;
  • 如果设置了not null,那么一定不能显式插入null值,否则应用会报错。

禁用该参数(explicit_defaults_for_timestamp=0)

timestamp字段在null、default属性的表现和其他普通字段表现有明显差异:

  • 默认会设置NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
  • 显式插入null值,默认为当前时间,应用不会报错。

案例发生的场景:

公司所有集群已经统一启用该参数;
某集群过去某个时间因为研发的要求,将该参数禁用,但是这次集群切换后的新服务器采用了统一的参数模板,启用了参数;
应用程序显式向timestamp字段插入null值,且该字段已经设置了not null,在禁用该参数的集群不会报错,但是切换到启用了该参数的集群时,就报column cannot be null.

统一规范:

个别集群禁用该参数导致公司所有的mysql集群参数不统一,可能带来应用报错的后果,因此建议:

  • 统一公司所有集群的参数explicit_defaults_for_timestamp=1;
  • 用timestamp字段时设置default和not null属性;
  • 应用程序不要显式插入null值。

到此这篇关于mysql timestamp字段规范使用详情的文章就介绍到这了,更多相关mysql timestamp字段 内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • 详解MySQL中timestamp和datetime时区问题导致做DTS遇到的坑

    目录 MySQL中如何表示当前时间? 结论 验证 坑 MySQL中如何表示当前时间? 其实,表达方式还是蛮多的,汇总如下: Data Type "Zero" Value DATE '0000-00-00' TIME '00:00:00' DATETIME '0000-00-00 00:00:00' TIMESTAMP '0000-00-00 00:00:00' YEAR 0000 datetime和timestamp这两种类型都是用于表示YYYY-MM-DD HH:MM:SS 这种年月

  • MySQL中CURRENT_TIMESTAMP时间戳的使用详解

    目录 前言 解决: 总结 前言 最近在项目中发现一个小问题,数据被更改时,插入记录和更新记录的时间会被同步更新.设置的两个时间create_time.update_time,按照预期来讲,应该是创建记录的时候会同步更新create_time,update_time,而在更新记录的时候,只有update_time会被同步更新.但实际情况却是update记录时,两个time都会被同步更新. 在代码中并没有对时间进行显性的设置,对时间的维护是MySQL本身进行管理的,所以就查看了一下之前同事创建表时的

  • 浅谈mysql的timestamp存在的时区问题

    目录 简介 基本概念 timestamp与datetime区别 为什么网上又说timestamp类型存在时区问题? 那为什么网上会说timestamp存在时区问题? serverTimezone的本质 将serverTimezone与mysql时区保持一致 Entity中日期属性是String呢? 最佳实践 数据库中用timestamp还是int来存储时间? 总结 简介 众所周知,mysql中有两个时间类型,timestamp与datetime,但当在网上搜索timestamp与datetime

  • MYSQL 数据库时间字段 INT,TIMESTAMP,DATETIME 性能效率的比较介绍

    目录 一.准备工作 1.1 建表 1.2 插入100万条测试数据 二.MyISAM引擎 2.1 MyISAM 引擎无索引下的 dint/dtimestamp/d_datetime 2.1.1 int 类型是否调用 UNIX_TIMESTAMP 优化对比 2.1.2 timestamp 类型是否调用 UNIX_TIMESTAMP 优化对比 2.1.3 datetime 类型是否调用 UNIX_TIMESTAMP 优化对比 2.2 MyISAM 引擎有索引下的 dint/dtimestamp/d_d

  • MySQL 中 datetime 和 timestamp 的区别与选择

    目录 1 区别 1.1 占用空间 1.2 表示范围 1.3 时区 2 测试 3 选择 MySQL 中常用的两种时间储存类型分别是datetime和 timestamp.如何在它们之间选择是建表时必要的考虑.下面就谈谈他们的区别和怎么选择. 1 区别 1.1 占用空间 类型 占据字节 表示形式 datetime 8 字节 yyyy-mm-dd hh:mm:ss timestamp 4 字节 yyyy-mm-dd hh:mm:ss 1.2 表示范围 类型 表示范围 datetime '1000-01

  • MySQL为JSON字段创建索引方式(Multi-Valued Indexes 多值索引)

    目录 多值索引简介 创建多值索引 JSON对象字段索引 JSON数组对象索引 在组合索引中创建多值索引 多值索引的局限 应用场景 多值索引简介 从MySQL 8.0.17 开始, InnoDB支持创建多值索引(Multi-Valued Indexes),该索引是在JSON存储值数组的列上定义的二级索引,对于单个数据记录可以有多个索引记录.此类索引特定的语法定义: CAST(expression AS type ARRAY),例如CAST(data->'$.zipcode' AS UNSIGNED

  • MySQL如何修改字段的默认值和空值

    目录 MySQL修改字段的默认值和空值 修改字段默认值 修改字段值是否为空 MySQL默认值NULL.空值.Empty String的区别 如何选择? 先说结论 区别 MySQL修改字段的默认值和空值 修改字段默认值 修改: ALTER TABLE 表名 ALTER COLUMN 字段名 SET DEFAULT 默认值 删除: ALTER TABLE 表名 ALTER COLUMN 字段名 DROP DEFAULT 修改字段值是否为空 设为空: ALTER TABLE 表名 MODIFY 字段名

  • mysql timestamp比较查询遇到的坑及解决

    目录 timestamp比较查询遇到的坑 timestamp查询范围问题 timestamp比较查询遇到的坑 记得之前京东要求mysql建表的时候update_time 为timestamp,create_time为datetime.后来阿里的编码规范里要求两者都要是datetime类型的. 对于timestamp和datetime的区别好多地方都有介绍.有时在想为什么京东会要求update_time必须timestamp呢?难道是因为占用的空间少点?还是只有timestamp才能设置默认值(o

  • mysql timestamp字段规范使用详情

    目录 1. 前言 2. mysql中的时间字段 2.1. 数据的存储方式 2.2. DATETIME和TIMESTMAP的区别 3. timestamp字段处理默认值和null值时的行为 3.1. 参数禁用 3.2. 参数启用 4. 总结 1. 前言 这个世界离不开时间,同样,数据库中也是如此,表中的每条记录除了数据模型的时间字段(如生日,生产日期,出发日期等),一般至少还有两个固定的时间字段:记录插入时间,记录更新时间. 然而,看似很简单时间字段,谁能想到会导致应用报错,引发血案: 个中缘由,

  • 使用MYSQL TIMESTAMP字段进行时间加减运算问题

    目录 MYSQL TIMESTAMP字段进行时间加减运算 计算公式如下 DATETIME 与 TIMESTAMP的区别 结论 参考文档 MYSQL TIMESTAMP字段进行时间加减运算 在数据分析过程中,想当然地对TIMESTAMP字段进行运算,导致结果谬之千里 计算公式如下 -- create_time与week_time的声明都是TIMESTAMP(), 要求精确到分钟 -- SELECT (sa.create_time - sa.week_time)/(1000 * 60) from a

  • mysql数据库开发规范【推荐】

    最近一段时间一边在线上抓取SQL来优化,一边在整理这个开发规范,尽量减少新的问题SQL进入生产库.今天也是对公司的开发做了一次培训,PPT就不放上来了,里面有十来个生产SQL的案例.因为规范大部分还是具有通用性,所以也借鉴了像去哪儿和赶集的规范,但实际在撰写本文的过程中,每一条规范的背后无不是在工作中有参照的反面例子的.如果时间可以的话,会抽出一部分或分析其原理,或用案例证明. 一. 命名规范 1.库名.表名.字段名必须使用小写字母,并采用下划线分割 (1)MySQL有配置参数lower_cas

  • MySQL数据库使用规范总结

    导读: 关于MySQL数据库规范,相信大家多少看过一些文档.本篇文章给大家详细分类总结了数据库相关规范,从库表命名设计规范讲起,到索引设计规范,后面又给出SQL编写方面的建议.相信这些规范适用于大多数公司,也希望大家都能按照规范来使用我们的数据库,这样我们的数据库才能发挥出更高的性能. 关于库: 1.[强制]库的名称必须控制在32个字符以内,英文一律小写. 2.[强制]库的名称格式:业务系统名称_子系统名. 3.[强制]库名只能使用英文字母,数字,下划线,并以英文字母开头. 4.[强制]创建数据

  • MySQL timestamp自动更新时间分享

    通常表中会有一个Create date 创建日期的字段,其它数据库均有默认值的选项.MySQL也有默认值timestamp,但在MySQL中,不仅是插入就算是修改也会更新timestamp的值! 这样一来,就不是创建日期了,当作更新日期来使用比较好! 因此在MySQL中要记录创建日期还得使用datetime 然后使用NOW() 函数完成! 1,TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP  在创建新记录和修改现有记

  • MySQL时间类型和模式详情

    目录 1.MySQL时间类型 2.查看时区 3.非法时间值 4.严格模式 5.case汇总 当我在MySQL数据库中尝试插入一条带有时间戳的数据时报错: mysql> insert into alarm_service values (6, '1970-01-01 08:00:00'); ERROR 1292 (22007): Incorrect datetime value: '1970-01-01 08:00:00' for column 'time' at row 1 # 查看表结构 my

  • mysql的数据压缩性能对比详情

    目录 1. 测试环境 1.1 软硬件 1.2 表结构 2. 测试目的 2.1 压缩空间对比 2.2 查询性能对比 3. 测试工具 3.1 mysqlslap 3.2 测试query 4.测试结论 数据魔方需要的数据,一旦写入就很少或者根本不会更新.这种数据非常适合压缩以降低磁盘占用.MySQL本身提供了两种压缩方式――archive引擎以及针对MyISAM引擎的myisampack方式.今天对这两种方式分别进行了测试,对比了二者在磁盘占用以及查询性能方面各自的优劣.至于为什么做这个,你们应该懂的

  • Mysql MVCC多版本并发控制详情

    目录 1.MVCC 2.当前读 3.快照读(提高数据库的并发查询能力) 4.当前读.快照读.MVCC关系 5.MVCC实现原理 6.MVCC核心思想 1.MVCC MVCC,全称Multi-Version Concurrency Control,即多版本并发控制.MVCC是一种并发控制的方法,一般在数据库管理系统中,实现对数据库的并发访问,在编程语言中实现事务内存. MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能,用更好的方式去处理读写冲突,做到即使有>读写冲突时,也能做

  • mysql 索引使用及优化详情

    目录 前言 mysql索引原理 mysql索引分类 索引创建语法 1.创建索引 2.查看索引 3.删除索引 4.为 username和password创建联合索引 5.给user表添加一个info的字段,并为这个字段添加全文索引 已经存在的表创建.删除索引等 1.使用ALTER TABLE语句创建索引 2.使用ALTER TABLE语句删除索引 常用的索引设计原则 索引失效情况总结 尽量使用覆盖索引 前言 索引对有一定开发经验的同学来说并不陌生,合理使用索引,能大大提升sql查询的性能,可以这么

  • mysql时间字段默认设置为当前时间实例代码

    目录 mysql时间字段默认为当前时间 附:mysql 字段 default 默认赋值 当前系统时间 总结 mysql时间字段默认为当前时间 1.直接在创建表时添加该列并声明默认值,如下: CREATE TABLE `table1` ( `id` int(11) NOT NULL, `createtime` timestamp NULL default CURRENT_TIMESTAMP, `updatetime` timestamp NULL default CURRENT_TIMESTAMP

随机推荐