mysql转毫秒 mysql 毫秒时间类型

转载

mob64ca14163a4f 2023-08-04 21:15:53

文章标签 mysql转毫秒数据库 mysql 字段 User 文章分类 MySQL 数据库

日期类型

常见的日期类型有year,date,time,datetime,timestamep-精确到秒常用datetime,timestamep

DATETIME

展开形式：YYYY-MM-DD HH:MM:SS 固定占用8字节

5.6版本之后支持到毫秒DATETIME(N)表示毫秒的精度，同时，一些日期函数也支持精确到毫秒，例如常见的函数 NOW、SYSDATE：

SELECT NOW(6);
NOW(6)
2021-05-20 09:21:48.762670

当我们设计表的时候，有两个字段值，一个是register_date 还有就是 last_modify_date。分别表示的是注册事件还有更新时间。这个时候就可以这么设计

register_date DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
last_modify_date DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),

列 register_date 表示注册时间，DEFAULT CURRENT_TIMESTAMP 表示记录插入时，若没有指定时间，默认就是当前时间。

列 last_modify_date 表示当前记录最后的修改时间，DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) 表示每次修改都会修改为当前时间。

验证代码：

CREATE TABLE User (
id BIGINT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
sex CHAR(1) NOT NULL,
`password` VARCHAR(1024) NOT NULL,
money INT NOT NULL DEFAULT 0,
register_date DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
last_modify_date DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
CHECK (sex = 'M' OR sex = 'F'),
PRIMARY KEY(id)
);
INSERT into `user` values(null,'David','M','123456',12345,NOW(),NOW());
SELECT * FROM `user`;
UPDATE User SET money = money - 1 WHERE name = 'David';

id	name	sex	password	money	register_date	last_modify_date
1	David	M	123456	12344	2021-05-20 09:41:09.000000	2021-05-20 09:41:45.082785

TIMESTAMP

在 MySQL 中，由于类型 TIMESTAMP 占用 4 个字节，因此其存储的时间上限只能到‘2038-01-19 03:14:07’。

同类型 DATETIME 一样，从 MySQL 5.6 版本开始，类型 TIMESTAMP 也能支持毫秒。与 DATETIME 不同的是，若带有毫秒时，类型 TIMESTAMP 占用 7 个字节，而 DATETIME 无论是否存储毫秒信息，都占用 8 个字节。

TIMESTAMP带有时区属性，本质是从毫秒转化过来的。参数time_zone制定了当前的时区，默认是SYSTEM 使用操作系统时区，用户可以通过该参数指定所需要的时区。

可以将DATETIME变更为TIMESTAMP

ALTER TABLE User 
 CHANGE register_date 
 register_date TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP
(6);

指定不同的时区

mysql> SELECT name,regist er_date FROM User WHERE name = 'David';

+-------+----------------------------+

| name  | register_date              |

+-------+----------------------------+

| David | 2018-09-14 18:28:33.898593 |

+-------+----------------------------+

1 row in set (0.00 sec)



mysql> SET time_zone = '-08:00';

Query OK, 0 rows affected (0.00 sec)



mysql> SELECT name,register_date FROM User WHERE name = 'David';

+-------+----------------------------+

| name  | register_date              |

+-------+----------------------------+

| David | 2018-09-14 02:28:33.898593 |

+-------+----------------------------+

1 row in set (0.00 sec)

中国的时区是 +08:00，美国的时区是 -08:00

直接加减时区并不直观，需要非常熟悉各国的时区表。在 MySQL 中可以直接设置时区的名字，如：

mysql> SET time_zone = 'America/Los_Angeles';

Query OK, 0 rows affected (0.00 sec)



mysql> SELECT NOW();

+---------------------+

| NOW()               |

+---------------------+

| 2020-09-14 20:12:49 |

+---------------------+

1 row in set (0.00 sec)



mysql> SET time_zone = 'Asia/Shanghai';

Query OK, 0 rows affected (0.00 sec)



mysql> SELECT NOW();

+---------------------+

| NOW()               |

+---------------------+

| 2020-09-15 11:12:55 |

+---------------------+

1 row in set (0.00 sec)

业务表设计

DATETIME vs TIMESTAMP vs INT，怎么选？

在做表结构设计时，对日期字段的存储，开发人员通常会有 3 种选择：DATETIME、TIMESTAMP、INT。

INT 类型就是直接存储 ‘1970-01-01 00:00:00’ 到现在的毫秒数，本质和 TIMESTAMP 一样，因此用 INT 不如直接使用 TIMESTAMP。

当然，有些同学会认为 INT 比 TIMESTAMP 性能更好。但是，由于当前每个 CPU 每秒可执行上亿次的计算，所以无须为这种转换的性能担心。更重要的是，在后期运维和数据分析时，使用 INT 存储日期，是会让 DBA 和数据分析人员发疯的，INT的可运维性太差。

也有的同学会热衷用类型 TIMESTEMP 存储日期，因为类型 TIMESTAMP 占用 4 个字节，比 DATETIME 小一半的存储空间。

但若要将时间精确到毫秒，TIMESTAMP 要 7 个字节，和 DATETIME 8 字节差不太多。另一方面，现在距离 TIMESTAMP 的最大值‘2038-01-19 03:14:07’已经很近，这是需要开发同学好好思考的问题。

总的来说，我建议你使用类型 DATETIME。 对于时区问题，可以由前端或者服务这里做一次转化，不一定非要在数据库中解决。

TIMESTAMP的性能问题

上限值是2038年，毫秒转类型TIMESTAMP本身需要的 CPU 指令并不多，这并不会带来直接的性能问题。但是如果使用默认的操作系统时区，则每次通过时区计算时间时，要调用操作系统底层系统函数 __tz_convert()，而这个函数需要额外的加锁操作，以确保这时操作系统时区没有修改。所以，当大规模并发访问时，由于热点资源竞争，会产生两个问题。