关于mysql坑爹的timestamp

大家都听说过mysql的datetime有时区问题，timestamp没有时区问题，但是我在公司码坑的时候却发现并不是这句话这么简单。比如每次写入的timestamp的字段总是存的serverTimezone时区的时间。所以准备彻底试一下。

1.mysql的官方解释

2. 开始测试

2.1 表结构

CREATE TABLE `study_test`.`Untitled`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `descript` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `datetime_field` datetime(0) NULL DEFAULT NULL,
  `timestamp_field` timestamp(0) NULL DEFAULT NULL,
  -- 用个长整型时间戳精确存储的时间
  `number_timestamp` bigint(20) NULL DEFAULT NULL,
  -- 虚拟列，用来展示长整型时间戳
  `number_timestamp_view` datetime(0) GENERATED ALWAYS AS (date_format(from_unixtime((`number_timestamp` / 1000)),'%Y-%m-%d %H:%i:%S')) VIRTUAL NULL,
  PRIMARY KEY (`id`) USING BTREE
) ;

数据库时区:

2.2 纯mysql的存取

-- 在0区状态下写入0点
set time_zone = '+0:00';
insert into time_test(descript, datetime_field,timestamp_field,number_timestamp) 
values ('mysql原生写入-0时区','2022/11/11 00:00:00','2022/11/11 00:00:00',1668124800000);

-- 在8区状态下写入8点
set time_zone = '+8:00';
insert into time_test(descript, datetime_field,timestamp_field,number_timestamp) 
values ('mysql原生写入-8时区','2022/11/11 08:00:00','2022/11/11 08:00:00',1668124800000);

康康结果，符合mysql官方的描述，datetime不会根据连接的时区切换而切换，timestamp则会计算

2.3 java jpa的写入

因为公司使用jpa，所以优先测试jpa，随便搭个小的测试jpa项目

// entity
@Getter
@Setter
@Entity
@Table(name = "time_test")
public class TimeTest {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "descript")
    private String descript;

    @Column(name = "datetime_field")
    private Date datetimeField;

    @Temporal(TemporalType.TIMESTAMP)
    @Column(name = "timestamp_field")
    private Date timestampField;

    @Column(name = "number_timestamp")
    private Long numberTimestamp;

    public Date getNumberTimestampView() {
        return numberTimestamp == null ? null : new Date(numberTimestamp);
    }
}
// repository
@Repository
public interface TimeTestRepository extends JpaRepository<TimeTest, Long>, JpaSpecificationExecutor<TimeTest> {
}
// test
@SpringBootTest(classes = {JpaStudyApplication.class})
class TimeTestRepositoryTest {
    @Autowired
    private TimeTestRepository timeTestRepository;
    @Autowired
    private DataSource dataSource;
	/**
	 * 修改java的连接时区时，修改serverTimezone属性
	 */
    @Test
    @Rollback(false)
    void testSaveZone() {
        Date dateTime = new Date(1668124800000L);
        TimeTest timeTest = new TimeTest();
        timeTest.setDescript("java jpa写入-0区连接-0区数据库");
        timeTest.setDatetimeField(dateTime);
        timeTest.setTimestampField(dateTime);
        timeTest.setNumberTimestamp(dateTime.getTime());
        timeTestRepository.save(timeTest);
    }
}

写入的结果，当serverTimezone=Asia/Shanghai的时候，timestamp写入了一个错误的值:

康康java的数据库链接time_zone

	@Test
    void testJdbcTemplate() {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        List<Map<String, Object>> maps = jdbcTemplate.queryForList("show variables like '%time_zone%'");
        for (Map<String, Object> map : maps) {
            System.out.println(map);
        }
    }

结果是，没有变化:

{Variable_name=system_time_zone, Value=}
{Variable_name=time_zone, Value=+00:00}

2.4 jdbc的写入

@Test
    @Rollback(false)
    void testJdbcInsert() throws Exception {
        long stamp = 1668124800000L;
        Connection connection = dataSource.getConnection();
        PreparedStatement statement = connection.prepareStatement(
                "insert into time_test(descript, datetime_field,timestamp_field,number_timestamp) " +
                        "values (?,?,?,?)");
        statement.setString(1, "java jdbc写入-0区连接-0区数据库");
        statement.setDate(2, new java.sql.Date(stamp));
        statement.setDate(3, new java.sql.Date(stamp));
        statement.setLong(4, stamp);
        statement.execute();
    }

似乎timestamp和datetime的表现都一样

2.5 试试pymysql

因为公司的python服务也比较多，所以也测一下

import pymysql
import datetime

with pymysql.connect(user='root', password='123456', host='localhost', database='study_test', port=3307,
                     cursorclass=pymysql.cursors.DictCursor) as conn:
    with conn.cursor() as cursor:
        # 设置连接时区
        cursor.execute("set time_zone='+0:00'")
        timestamp = 1668124800000
        dt = datetime.datetime.fromtimestamp(timestamp / 1000)
        cursor.execute("insert into time_test(descript, datetime_field,timestamp_field,number_timestamp) "
                       "values (%(descript)s,%(datetime_field)s,%(timestamp_field)s,%(number_timestamp)s);",
                       dict(descript='python pymysql写入-0区连接-0区数据库', datetime_field=dt, timestamp_field=dt,
                            number_timestamp=timestamp))
    conn.commit()

python 比较直接，把当地时间字符串直接发送给了mysql程序，然后mysql程序根据时区配置计算时间，并存储起来

3.原理分析

// todo

4.总结

如果项目有跨时区以及跨语言应用，一定要用bigint作为时间字段。
如果只是java，用datetime和timestamp区别不大，java存取没有什么问题。