目录Mysql中如何表示当前时间?结论验证坑mysql中如何表示当前时间? 其实,表达方式还是蛮多的,汇总如下: Data T
其实,表达方式还是蛮多的,汇总如下:
Data Type | “Zero” Value |
---|---|
DATE |
'0000-00-00' |
TIME |
'00:00:00' |
DATETIME |
'0000-00-00 00:00:00' |
TIMESTAMP |
'0000-00-00 00:00:00' |
YEAR |
0000 |
datetime和timestamp这两种类型都是用于表示YYYY-MM-DD HH:MM:SS 这种年月日时分秒格式的数据,但两者还是有些许不同之处的。
环境准备,简而言之就是存在一张表有timestamp字段和datetime字段,且当前服务端为CST时区
mysql> show create table test_time\G;
*************************** 1. row ***************************
Table: test_time
Create Table: CREATE TABLE `test_time` (
`id` int NOT NULL AUTO_INCREMENT,
`ts` timestamp(6) NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
`dt` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | CST |
| time_zone | SYSTEM |
+------------------+--------+
2 rows in set (0.01 sec)
插入一条数据,当前CST时区下ts和dt结果相同
mysql> select * from test_time;
Empty set (0.00 sec)
mysql> insert into test_time() values();
Query OK, 1 row affected (0.00 sec)
mysql> select * from test_time;
+----+----------------------------+----------------------------+
| id | ts | dt |
+----+----------------------------+----------------------------+
| 3 | 2021-12-05 15:04:13.293949 | 2021-12-05 15:04:13.293949 |
+----+----------------------------+----------------------------+
1 row in set (0.00 sec)
将会话的时区设置为UTC时区再次查询,ts由于从CST时区变为UTC时区查询到的结果比之前慢8个小时,由于dt不带时区信息,结果不变
mysql> set time_zone='+00:00';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test_time;
+----+----------------------------+----------------------------+
| id | ts | dt |
+----+----------------------------+----------------------------+
| 3 | 2021-12-05 07:04:13.293949 | 2021-12-05 15:04:13.293949 |
+----+----------------------------+----------------------------+
1 row in set (0.01 sec)
从刚刚insert产生的binlog中也有体现,ts在binlog中存储为时间戳(从1970-01-01 00:00:00 UTC到目前的秒数)相当于带UTC时区信息,dt为不带时区信息,结果为格式化后的字符串2021-12-05 15:04:13.293949,主要关注倒数第4第5行,@2=1638687853.293949表示ts字段的值, @3='2021-12-05 15:04:13.293949'表示dt字段的值
[mysql %] mysqlbinlog -v --base64-output=decode-rows ./mysqlbin.000012
... ...
SET @@SESSioN.GTID_NEXT= '1cf4493a-dafd-11eb-944c-4016af29c14c:1416767';
# at 14220
#211205 15:04:13 server id 1 end_log_pos 14308 CRC32 0x1fd913a3 Query thread_id=137 exec_time=0 error_code=0
SET TIMESTAMP=1638687853.293949;
BEGIN
;
# at 14308
#211205 15:04:13 server id 1 end_log_pos 14368 CRC32 0xbb8937fb Table_map: `testa`.`test_time` mapped to number 121
# at 14368
#211205 15:04:13 server id 1 end_log_pos 14423 CRC32 0x2e0a3baa Write_rows: table id 121 flags: STMT_END_F
### INSERT INTO `testa`.`test_time`
### SET
### @1=3
### @2=1638687853.293949
### @3='2021-12-05 15:04:13.293949'
# at 14423
#211205 15:04:13 server id 1 end_log_pos 14454 CRC32 0x68cee280 Xid = 1416
COMMIT;
到此这篇关于详解MySQL中timestamp和datetime时区问题导致做DTS遇到的坑的文章就介绍到这了,更多相关MySQL timestamp和datetime坑内容请搜索编程网以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程网!
--结束END--
本文标题: 详解MySQL中timestamp和datetime时区问题导致做DTS遇到的坑
本文链接: https://www.lsjlt.com/news/159195.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-21
2024-05-21
2024-05-21
2024-05-21
2024-05-21
2024-05-21
2024-05-21
2024-05-21
2024-05-21
2024-05-21
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0