Mysql 数据库日期 存储精度问题

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: Mysql 数据库存储时间精度出现问题 ,timestamp 和 datetime 常用的数据库存储

一、mysql 数据库日期类型

名称 描述 字节数 格式 取值范围 零值
year 表示时间 1 yyyy 1901-2155 0000
date 表示日期 4 yyyy-mm-dd 1000-01-01~9999-12-31 0000-00-00
time 表示一天中的时间 3 HH:mm:ss -838:59:59~838:59:59 00:00:00
dateTime 表示日期 8 yyyy-mm-dd 1000-01-01 00:00:00~9999-12-31 23:59:59 0000-00-00 00:00:00
timestamp 表示日期 4 yyyy-mm-dd 19700101080001~20380119111407 00000000000000

二、常用的两种

1、 timestamp

  1. 5.7版本之前,不支持一张表中存在两个默认值为CURRENT_TIMESTAMP 的timestamp 字段,
    也就是说如果有一个create_time 和 update_time ,两个字段只能有一个默认值为 CURRENT_TIMESTAMP,
    而且需要在第一个timestamp类型字段设置,值得注意的是如果当前的timestamp字段为非空的话默认是根据当时时间戳更新,
    如果当前字段是创建时间的话,每次修改数据可能导致创建时间随之更新,需注意。

  2. 5.7版本到之后的版本,一张表中timestamp字段都可以使用默认值CURRENT_TIMESTAMP,都可以根据当前时间戳更新。
    但是,在5.7版本中,timestamp 类型的字段默认是不允许设置为零日期的。(oracle 数据库迁移到mysql5.7时遇到的问题)

修改步骤:

  1. 执行命令:select @@sql_mode;
  2. 可以看出有两个字段 :NO_ZERO_IN_DATE 和 NO_ZERO_DATE 两个值,限制字段不能为零日期;
  3. 修改配置文件my.cnf,去掉这两个值 ,并且添加一行:
    sql_mode=NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,

NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT,ANSI_QUOTES。

  1. mysql 的timestamp 值自动从当前时区转换到utc时区存储,并且自动从utc时区转换为当前系统时区检索返回。

2、datetime

  1. 在mysql5.7之后,datetime字段也可以指定默认值,并且格式和timestamp一样。

三、数据库时区

1、 查看数据库时区

  • show variables like '%time_zone';

2、 system_time_zone

  • 系统默认时区

3、 time_zone

  • 当前使用的时区,默认使用的是系统时区

4、 修改时区

  1. 通过my.conf 文件修改

在 [mysqld] 之下加

  1. 通过命令行修改

set time_zone='+8:00';

5、验证时区修改是否正确

  • select now();通过结果对比。

四、存储精度问题

  1. 在mysql5.6.4 之前,时间只能精确保存到秒;5.6.4~5.7 之间,精确到分数秒;5.7到之后,精确到微妙。
  2. 在数据库存储中,如果字段类型是datetime或timestamp,长度可以设为3,增加精确度,最后可以设为6,不过一般三位就够用了。
    值得注意的是,如果要加长度,如果数据库版本过低,需要去掉 (根据当前时间戳更新),不然会报错。

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
3天前
|
Java 关系型数据库 MySQL
【MySQL × SpringBoot 突发奇想】全面实现流程 · xlsx文件,Excel表格导入数据库的接口(下)
【MySQL × SpringBoot 突发奇想】全面实现流程 · xlsx文件,Excel表格导入数据库的接口
12 0
|
3天前
|
Java 关系型数据库 MySQL
【MySQL × SpringBoot 突发奇想】全面实现流程 · xlsx文件,Excel表格导入数据库的接口(上)
【MySQL × SpringBoot 突发奇想】全面实现流程 · xlsx文件,Excel表格导入数据库的接口
15 0
|
3天前
|
存储 SQL 关系型数据库
关系型数据库结构化数据存储
【5月更文挑战第9天】关系型数据库结构化数据存储
10 1
|
3天前
|
存储 SQL 关系型数据库
关系型数据库数据结构化存储
【5月更文挑战第8天】关系型数据库数据结构化存储
26 6
|
1天前
|
关系型数据库 MySQL API
实时计算 Flink版产品使用合集之可以通过mysql-cdc动态监听MySQL数据库的数据变动吗
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
11 0
|
1天前
|
存储 Oracle 关系型数据库
关系型数据库文件方式存储CONTROL FILE(控制文件)
【5月更文挑战第11天】关系型数据库文件方式存储CONTROL FILE(控制文件)
8 1
|
1天前
|
存储 关系型数据库 数据库
关系型数据库文件方式存储LOG FILE(日志文件)
【5月更文挑战第11天】关系型数据库文件方式存储LOG FILE(日志文件)
6 1
|
1天前
|
存储 SQL Oracle
关系型数据库文件方式存储DATA FILE(数据文件)
【5月更文挑战第11天】关系型数据库文件方式存储DATA FILE(数据文件)
12 3
|
2天前
|
存储 SQL 关系型数据库
关系型数据库结构化数据存储查询方式
【5月更文挑战第10天】关系型数据库结构化数据存储查询方式
16 2
|
2天前
|
存储 关系型数据库 数据库
关系型数据库结构化数据存储
【5月更文挑战第10天】
18 7

推荐镜像

更多