mysql的timestamp字段和serverTimezone的关系

发布时间:2023年12月26日

1. mysql中timestamp字段类型的定义:表示从1970年1月1日0点0分1秒开始到存储时间之间的秒数,最高到2038年1月19号3点14分07秒

这个怎么理解呢?就是不管你当前的时区是什么,当你存入一个时间类型的数据的时候,mysql都会将该时间转化成utc时间1970-01-01 00:00:00 开始到现在的秒数。
<1>如果mysql服务器设置的时区,也就是time_zone是'+00:00'时
当我们以字符串的形式存入数据的时候,最小只能存入'1970-01-01 00:00:01',如果存'1970-01-01 00:00:00'就会报错(sql_mode为严格)

com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Incorrect datetime value: '1970-01-01 00:00:00' for column 'update_time' at row 1

大于等于'2038-01-19 03:14:08'的时候报同样的错误,其实就是超出范围了。所以使用这个字段类型需要注意,只能到2038年,否则就会出现溢出的问题

<2>如果mysql服务器设置的时区,也就是time_zone是'+08:00'时
当我们以字符串的形式存入数据的时候,最小只能存入'1970-01-01 08:00:01',如果存'1970-01-01 08:00:00'就会报错(sql_mode为严格)
com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Incorrect datetime value: '1970-01-01 00:00:00' for column 'update_time' at row 1
为什么明明要存入的是'1970-01-01 08:00:00',却报错'1970-01-01 00:00:00'呢?
原因就在于time_zone是'+08:00'时,mysql在存储timstamp的时候,会将时间转换为UTC时间。
因为服务器设置的是东八区,所以进行转换的时候会将时间提前8小时,这个就是跟报了sql语句不一样的时间错误,究其原因就是存储时候转换导致的

2. 我们平时在使用jdbc连接mysql的时候,常常会在url中设置一个时区参数,例如:jdbc:mysql://${ip}:3306/test?serverTimezone=Asia/Shanghai&characterEncoding=UTF-8
这个参数到底是干什么用的呢,有什么影响呢?(下载的内容是我根据推测和实验得出的结论,可能不全对,如果哪位兄台发现错误请不吝赐教)
<1> 这个参数只对日期类型的数据起作用,对字符串类型的数据是没有作用。

final Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test?serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
                "utest", "upd@231224aaa");
        final Statement statement = conn.createStatement();
        statement.execute("insert into t_test values(1, '2038-01-19 03:14:08')");

例如:当我们执行insert into t_test values(1, '1970-01-01 08:00:01')的时候,无论jdbcurl中的serverTimezone指定的时区是东八区,还是utc,没有任何区别
那它的作用是什么?经过测试,当我们使用java代码中的Date类型存储数据的时候,这个参数就会起作用,它起的作用是:在进入mysql之前,将jvm中Date的时区根据这个参数先进行一次转化,例如:

@Test
    public void testTimeZone() throws SQLException {
        final Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test?serverTimezone=UTC&characterEncoding=UTF-8",
                "utest", "upd@231224aaa");
        final PreparedStatement preparedStatement = conn.prepareStatement("insert into t_test(update_time) values(?)");
        final DateTime dateTime = DateUtil.parseDateTime("1970-01-01 08:00:01");
        preparedStatement.setObject(1, dateTime);
        preparedStatement.execute();
    }

我代码里使用了hutool的工具,DateTime只是将对Date一个简单封装,可以当成util.Date看待
因为我的本地jvm的时区是东八区,而url中设置的UTC,所以,在存入mysql的时候,会先将DateTime减去8小时,而如果你的mysql的time_zone为东八区的时候,就会报上面同样的错误.
com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Incorrect datetime value: '1970-01-01 00:00:01.0' for column 'update_time' at row 1
这个是因为:在存储的时候,会再一次进行转换,mysql会将已经减了8小时的时间转成utc偏移的秒数,会再减去8小时,此时就会报错

综合上面两点,我们可以看出来,在使用时间字符串存储mysql的timestamp字段的时候,跟url里的serverTimezone无关,而使用Date存储的时候,会根据这个进行时区转换,然后物理存储的时候再转成UTC偏离的秒数.
Date对象进行存储Date时区 -> (如果)serverTimezone不指定,则不会进行这步的转换)serverTimezone的时区 -> 转成UTC秒存储
String语句进行存储 -> 转成UTC秒存储

理解了转换的过程,我们来测试一个现实中的问题,服务器时区设置为time_zone='+8:00'的时候,jvm时区是+8:00,在url不设置时区,存储一个2023-12-25 08:00:00
所看到的时间是2023-12-25 08:00:00
当我们将url中的serverTimezone设置为UTC的时候(注意我的JVM时区仍然是东八区)会是什么结果呢?
1. 从mysql中读取出来的时候仍然是2023-12-25 08:00:00,由于serverTimezone,JVM时区是东八区,会导致进入jvm的时候再转一次,次数该时间变为'2023-12-25 16:00:00'
我最开始测试的时候忽略了JVM时区的转换,一直无法解释测试结果和理论结果对不上的问题。

文章来源:https://blog.csdn.net/shuwei003/article/details/135171173
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。