MySQL日期类型的处理总结

一、概述

MySQL中的日期类型包括以下5种:

类型大小
(字节)
范围格式用途
DATE31000-01-01/9999-12-31YYYY-MM-DD日期值
TIME3'-838:59:59'/'838:59:59'HH:MM:SS时间值或持续时间
YEAR11901/2155YYYY年份值
DATETIME81000-01-01 00:00:00/9999-12-31 23:59:59YYYY-MM-DD HH:MM:SS混合日期和时间值
TIMESTAMP4

1970-01-01 00:00:00/2038

结束时间是第 2147483647 秒,北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨 03:14:07

YYYY-MM-DD HH:MM:SS混合日期和时间值,时间戳

二、DATE

从取值范围来看DATE类型的mysql数据并不受格林尼治时间的限制。

1、使用场景

可以看到DATE类型的数据格式为:YYYY-MM-DD,对于只限制到日期的数据项,我们可以使用此种类型存储,如生日、节假日等。

2、Java存储方式

Java中对于date类型的存储可以使用java.sql.date直接存入。

    public int test() {
        String sql = "INSERT INTO user2(user_name,birthday) VALUES(?,?)";
        Object[] args = { "mht",java.sql.Date.valueOf("1992-11-20") };
        return jdbc.update(sql,args);
    }

执行结果:

这是使用指定日期格式的字符串插入date类型数据的情况,对于那些并不是日期字符串的日期、毫秒值等,可以先通过DateFormat抽象类将其转化为“yyyy-MM-dd”后再传入静态方法Date.valueOf(String dateStr)来实现。

3.Java查询方式

DATE类型的mysql数据查询到Java程序中,我们如何接收和处理。

首先,定义接收的实体类:

执行查询:

    public User test() {
        String sql = "select * from user2";
        List<User> list = jdbc.query(sql, new BeanPropertyRowMapper<>(User.class));
        User user = list.get(0);
        return user;
    }

查询结果:

说明:这里使用了一个静态内部类来接收从数据库中查询的user对象,但是这和普通的实体类并没有什么两样。此处没有截取get、set方法,但是对于使用框架来查询mapper的情况,get、set应该是必须的。

这里可能会有疑问,从数据库中使用的date类型,在Java中可以用util.Date来接收吗?

答案是可以的,当然也可以使用sql.Date,这并不会影响最终我们取得的数据,因为:

public class Date extends java.util.Date {

如果我们将date类型(无论是sqlDate还是utilDate)直接返回给页面,那么他们拿到的都是一个毫秒值(时分秒部分全为0),所以无论如何都需要在页面通过js来实现日期的格式化,当然这不在我们的讨论范畴之内。

总之,在Java中接收DATE日期,直接使用java.util.Date即可,也方便我们做其他转化工作。

三、TIME

MySQL中的TIME时间类型,可以用于存储时长或时间点。

1、Java存储

    public int test() {
        String sql = "INSERT INTO user2(user_name,time_on_foot) VALUES(?,?)";
        Object[] args = { "mht",java.sql.Time.valueOf("0:5:30") };
        
        return jdbc.update(sql, args);
    }

存储结果:

2、Java查询

实体类:

对于TIME类型的mysql数据,Java中有对应的java.sql.Time来接收其值,而且,返回页面的数据也会正常显示数据库中存储的样子而不会“变形”。

    public User test() {
        String sql = "select * from user2";
        List<User> list = jdbc.query(sql, new BeanPropertyRowMapper<>(User.class));
        User user = list.get(0);
        return user;
    }

查询结果:

但是用java.util.Date来接收TIME类型的MySQL数据就会出现问题,所以对于TIME类型的数据,貌似只能用java.sql.Time类来接收数据。在实际开发中注意体会和总结。

四、DATETIME和TIMESTAMP

终于到了重头戏部分!

DATETIME代表的语言含义是“日期时间”;而TIMESTAMP则表示“(人能看懂的)时间戳”。

两种类型数据的格式都是“YYYY-MM-DD HH:MM:SS”。那在实际应用中我们的数据到底应该使用前者还是后者呢?

1、两者的区别

a)、两者的范围不同。

b)、两者的存储大小不同。TIMESTAMP存储空间是DATETIME的一半!

c)、虽然管理工具中并未显示,但实际上TIMESTAMP可以精确到毫秒。

d)、TIMESTAMP有个特性:在insert、update数据时,TIMESTAMP列可以自动以当前时间(CURRENT_TIMESTAMP)填充/更新,MySQL引擎可以自动为更新的数据维护这个值。我们可以通过勾选“根据当前时间更新”复选框来调整MySQL数据库对该字段的维护操作。如果有多个TIMESTAMP列,并且都勾选了此项,那么MySQL会对这些列都进行维护操作。

e)、TIMESTAMP会受到时区timezone的影响以及MySQL和SQL mode的影响。

2、TIMESTAMP设置默认值的方式

sql方式:

ALTER TABLE user2 MODIFY upd_time TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP

管理工具方式:

新增记录:

INSERT INTO user2(user_name) VALUES("MHT")

结果展示:

2.Java存储

先看一下db结构:

对于TIMESTAMP类型的数据,如果设置了默认值,且更新时数据库会自动维护,那么在Java 中就不需要对其进行处理。如果需要Java进行插入,我们也可以通过如下方式来添加时间戳数据:

    public int test() {
        String sql = "INSERT INTO user2(user_name,upd_time2) VALUES(?,?)";
      Object[] args = { "test1", new java.sql.Timestamp(System.currentTimeMillis())};
      
      return jdbc.update(sql,args);
    }

我们通过java.sql.Timestamp对象来存储不需要MySQL为我们维护的TIMESTAMP数据即可。

还以上表说话。

对于DATETIME类型的数据,create_time。实际上我们可以将这个字段设计为一个不会随系统时间改变的TIMESTAMP。即只有insert的时候添加默认值,update时不会更新此值。

如果确定是要用datetime来表示该字段,那么,我们需要在Java中通过java.util.Date来存储该值。因为java.util.Dated 默认包含时分秒数据,可以完美对应MySQL中DATETIME类型的数据。

        String sql = "UPDATE user2 SET create_time = ? WHERE user_name = 'test1'";
        Object[] args = {new java.util.Date(System.currentTimeMillis())};
        jdbc.update(sql,args);

执行结果为:

3、Java查询

表中存在三个字段create_time、upd_time、upd_time2,第一个是DATETIME类型,后面两个是TIMESTAMP类型数据。

我们的User对象定义三个字段的类型如下:

执行查询:

    public User test() {
        String sql = "select * from user2";
        List<User> list = jdbc.query(sql, new BeanPropertyRowMapper<>(User.class));
        User user = list.get(0);
        return user;
    }

输出结果:

可以看到MySQL中TIMESTAMP类型的数据无论用java.sql.Timestamp还是java.util.Date接收传到页面都是一个只精确到秒的毫秒值的形式。所以这两种方式并没有太大的区别。

DATETIME数据由于Java中只有java.util.Date可对应其类型,因此我们用其接收数据,也完全没问题。为了方便记忆,我们可以在查询DATETIME和TIMESTAMP两种类型数据的时候都通过java.util.Date来接收即可。

五、常用的MySQL日期函数

SELECT NOW(),CURRENT_DATE(),CURRENT_TIME(),CURRENT_TIMESTAMP(),LOCALTIMESTAMP(),LOCALTIME()

输出结果:

-----------------------------------------------------我是分割线--------------------------------------------------------

综上,就是对MySQL日期类型的处理总结,如有疑问,欢迎文末留言。

参考文章:《MySQL日期数据类型、时间类型使用总结

相关推荐
©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页