开发者

mysql timestamp字段规范使用详情

开发者 https://www.devze.com 2022-12-12 09:25 出处：网络 作者： sdmei
目录1. 前言2. mysql中的时间字段2.1. 数据的存储方式2.2. DATETIME和TIMESTMAP的区别3. timestamp字段处理默认值和null值时的行为3.1. 参数禁用3.2. 参数启用4. 总结1. 前言
相关专题：mysql timestamp字段mysql timestamp字段规范使用
目录
  • 1. 前言
  • 2. mysql中的时间字段
    • 2.1. 数据的存储方式
    • 2.2. DATETIME和TIMESTMAP的区别
  • 3. timestamp字段处理默认值和null值时的行为
    • 3.1. 参数禁用
    • 3.2. 参数启用
  • 4. 总结

    1. 前言

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

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

    mysql timestamp字段规范使用详情

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

    2. mysql中的时间字段

    因时间字段的一些特性与版本有关，且目前我司统一使用的mysql 5.7版本，因此本文内容都基于mysql 5.7。

    mysql时间相关的字段主要有DATE、DATETIME、TIMESTAMP。

    mysql timestamp字段规范使用详情

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

    mysql timestamp字段规范使用详情

    2.1. 数据的存储方式

    DATE：

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

    DATETIME：

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

    mysql timestamp字段规范使用详情

    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_deandroidfaults_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;
—--------------------python+
| 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(nam开发者_NewSQLe,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,
`ts1www.devze.com` 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'js);
ERROR 1364 (HY000): Field ‘ts1' doesn't have a default value     
admin@test 05:59:49>
##注：创建表手动设置not null，插入记录不含timestamp字段时，报错Field doesn't hajavascriptve 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

    上一篇:

    :下一篇

    更多 数据库 相关资讯：

    精彩评论

    暂无评论...
    登录 注册
    Qedev.com
    验证码 换一张
    取 消

      关注公众号

      热门标签

      图文推荐

      Redis数据一致性详解

      Redis数据一致性详解
      基于Redis实现API接口访问次数限制

      基于Redis实现API接口访问次数限制
      RedisTemplate序列化设置的流程和具体步骤

      RedisTemplate序列化设置的流程和具体步骤
      Redis高并发缓存设计问题与性能优化

      Redis高并发缓存设计问题与性能优化
      Navicat最新版安装详细教程(超简单)

      Navicat最新版安装详细教程(超简单)
      数据库rank()分组排序函数的具体使用

      数据库rank()分组排序函数的具体使用
      数据库中row_number() 分组排序函数的具体使用

      数据库中row_number() 分组排序函数的具体使用
      单机离线部署OceanBase3.1.5详解

      单机离线部署OceanBase3.1.5详解

      开发者开发者网给大家分享系统运维,大数据运维,云计算,编程开发技巧,路由交换,运维和开发相关的资讯及技术文章，同时StackOverflow中文社区，知识经验交流分享。

      法律声明：本站内容均为网友上传，网站举办方负责审核和监督，如存在版权或非法内容，欢迎举报，我们将尽快予以删除。邮箱：devze@qq.com

      Copyright © 2018-2020 开发者. All rights reserved. Powered by 开发者ICP备案号: 京ICP备10032868号-9