• Mysql 时间日期类型


    一、概述

    Mysql 中有多种数据类型可以用于日期和时间的表示,不同的版本可能会存在差异,下面的表格中列出了 Mysql 5.7 中所支持的日期和时间类型

    日期和时间类型 存储占用磁盘空间大小 最小值 最大值
    YEAR 1 个字节 1901 2155
    TIME 3 个字节 -839:59:59 839:59:59
    DATE 4 个字节 1000-01-01 9999-12-31
    TIMESTAMP 4 个字节 1970 年的某个时刻 2038 年的某个时刻
    DATETIME 8 个字节 1000-01-01 00:00:00 9999-12-31 00:00:00

    每一种时间日期格式都有一个有效范围,如果超出这个时间范围,在默认的 SQLMode 下,系统会进行错误提示,并且都以 "零值" 来进行存储

    二、各种类型的区别

    每一种时间日期类型都有不同的使用场景,一般来说使用最多的是 TIME、DATE、TIMESTAMP、DATETIME,下面就来简单看一下这几种类型的区别

    创建 test 表

    CREATE TABLE `test` (
      `id` int NOT NULL AUTO_INCREMENT COMMENT '主键 id',
      `d` date NOT NULL DEFAULT '1900-01-01' COMMENT '年月日格式',
      `t` time NOT NULL DEFAULT '00:00:00' COMMENT '时间格式',
      `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '年月日时分秒',
      `dt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '年月日时分秒',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    

    需要注意的是 date 类型不能设置默认值 current_date, time 类型也不能设置默认值 current_time,需要用字符串设置一个默认的时间日期格式

    现在使用 now() 函数插入一组数据

    insert into test (d,t,ts,dt) values (now(),now(),now(),now());

    查看插入的数据

    从上面的信息可以得知

    date 表示的是 年月日 格式

    time 表示的是 时分秒 格式

    timestamp 和 datetime 都是表示 年月日时分秒 格式,并且都可以设置默认值 CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 属性,使得日期列可以随其它列的更新而自动更新(一般建表时 update_time 字段设置为 default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)

    timestamp 和 datetime 都可以表示 ''年月日 时分秒",但是它们之间还是存在一些区别的,具体如下

    1、取值范围

    timestamp 表示的范围较小,其取值范围从  1970 年的某个时刻  到 2038 年的某个时刻,而 datetime 的取值范围是 1000-01-01 00:00:00 到 9999-12-31 00:00:00

    2、存储时占用空间大小

    timestamp 存储时占用磁盘空间大小是 4 个字节,而 datetime 是 8 个字节

    3、时区影响

    timestamp 插入日期时,会先将日期时间转换为本地时区后存放,查询日期时也会先将日期转换为本地时区后显示,这样不同的时区用户看到的同一个日期可能是不一样的,由于 timestamp 插入和查询都受当地时区的影响,所以更能反映出实际的日期,而 datetime 只能反映出插入时当地的时区,其它时区的用户查看数据时必然会收到影响,也就是说 timestamp 会根据时区自适应,而 datetime 不会

     

    三、应用场景

    如何选择合适的日期时间类型

    1、先分析需求,根据实际需要选择能够满足应用的最小存储的日期类型,如果只需要记录 "年份",那么选用存储只占用一个字节的 YEAR 类型就可以了,而不用选择需要使用 4 个字节来存储的 DATE 类型,这样不仅可以节约存储空间,还能提升表的操作性能

    2、如果是要记录 "年月日时分秒",并且记录的年份跨度范围比较大,那么最好使用 DATETIME,而不要使用 TIMESTAMP,因为 TIMESTAMP 记录的时间范围是 1970~2038,如果超出该范围之后就会以零值来进行填充

    3、如果记录的日期类型是要让不同时区的用户使用,那么最好选用 TIMESTAMP,因为日期时间类型中只有它可以和实际的时区相适应

     

  • 相关阅读:
    全局临时表的应用 Timothy
    结合windows服务的Socket聊天室 Timothy
    阿拉伯数字转换成金额大写金额(包括小数) Timothy
    回文字符串和栈 Timothy
    SQL 读取不连续的第30到40之间的数据 Timothy
    C#中as和is关键字 Timothy
    嵌套事务和事务保存点的错误处理 Timothy
    隐式事务 Timothy
    float,double和decimal类型 Timothy
    string 值类型还是引用类型 Timothy
  • 原文地址:https://www.cnblogs.com/xiaomaomao/p/16270855.html
Copyright © 2020-2023  润新知