mysql的timestamp字段和serverTimezone的关系

本文主要是介绍mysql的timestamp字段和serverTimezone的关系，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

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的时区根据这个参数先进行一次转化,例如:

@Testpublic 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时区的转换，一直无法解释测试结果和理论结果对不上的问题。

这篇关于mysql的timestamp字段和serverTimezone的关系的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---