mysql零碎小知识

Mysql日期

MySQL中的时间类型有三大类：日期(Date)、时间(Time)和年(Year)

类型	格式	范围	零值	空间大小(MySQL 8.0)
YEAR	‘YYYY’	1901 to 2155	0000	1字节
DATE	‘YYYY-MM-DD’	'1000-01-01' to '9999-12-31'	‘0000-00-00’	3字节
TIME	‘hh:mm:ss’	'-838:59:59' to '838:59:59'	‘00:00:00’	3字节
TIMESTAMP	‘YYYY-MM-DD hh:mm:ss’	'1970-01-01 00:00:01.000000' UTC to '2038-01-19 03:14:07.999999' UTC	‘0000-00-00 00:00:00’	4字节
DATETIME	‘YYYY-MM-DD hh:mm:ss’	'1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999'	‘0000-00-00 00:00:00’	5字节

类型	MySQL 5.6.4之前	MySQL 5.6.4及之后
YEAR	1字节，小端序	1字节，小端序
DATE	3字节，小端序	3字节，小端序
TIME	3字节，小端序	3字节+小数秒存储，大端序
TIMESTAMP	4字节，小端序	4字节+小数秒存储，大端序
DATETIME	8字节，小端序	5字节+小数秒存储，大端序

字符串

这是初学者很容易犯的错误，容易直接将字段设置为 VARCHAR 类型，存储"2021-01-01 00:00:00"这样的字符串。当然这样做的优点是比较简单，上手快。

但是极力不推荐这样做，因为这样做有两个比较大的问题：

• 字符串占用的空间大
• 这样存储的字段比较效率太低，只能逐个字符比较，无法使用 MySQL 提供的日期API

DATETIME

DATETIME 在数据库中存储的形式为：YYYY-MM-DD HH:MM:SS，固定占用 8 个字节

从 MySQL 5.6 版本开始，DATETIME 类型支持毫秒，DATETIME(N) 中的 N 表示毫秒的精度。例如，DATETIME(6) 表示可以存储 6 位的毫秒值。

TIMESTAMP

TIMESTAMP 实际存储的内容为‘1970-01-01 00:00:00’到现在的毫秒数。在 MySQL 中，由于类型 TIMESTAMP 占用 4 个字节，因此其存储的时间上限只能到‘2038-01-19 03:14:07’。

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

​ 类型 TIMESTAMP 最大的优点是可以带有时区属性，因为它本质上是从毫秒转化而来。如果你的业务需要对应不同的国家时区，那么类型 TIMESTAMP 是一种不错的选择。

​ 比如新闻类的业务，通常用户想知道这篇新闻发布时对应的自己国家时间，那么 TIMESTAMP 是一种选择。 Timestamp 类型字段的值会随着服务器时区的变化而变化，自动换算成相应的时间，说简单点就是在不同时区，查询到同一个条记录此字段的值会不一样。

当MySQL服务器启动的时候，会查找系统环境来决定时区；

1. 可以使用–default-time-zone来指定时区；
2. 客户端连接时，默认使用服务器的时区，当客户端与服务器处于同一个时区的时候没什么问题，但是当时区不同时，应该显式设置连接的时区；
3. 客户端传递给服务器的TIMESTAMP值，服务器会先转换成UTC时间戳存储；当客户端查询时，再转换成客户端时区的值；
4. 其它时间相关的类型没有时区信息。

自动更新

TIMESTAMP和DATETIME可以设置自动初始化与更新：

CREATE TABLE t1 (
	id int,
	ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

这样，如果新增记录时没有指定ts和dt的值，那它们默认就是当前时间。

更新记录的时候，也会更新为当前时间。

一般来说我们会创建两个时间字段，一个用于记录创建时间，一个用于记录更新时间：

CREATE TABLE t1 (
	id int,
	ctime DATETIME DEFAULT CURRENT_TIMESTAMP,
	utime DATETIME DEFAULT CURRENT_TIMESTAMP `ON UPDATE CURRENT_TIMESTAMP`
);

TIMESTAMP 的性能问题

​ TIMESTAMP 还存在潜在的性能问题。

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

• 性能不如 DATETIME：DATETIME 不存在时区转化问题。

• 性能抖动：海量并发时，存在性能抖动问题。

  ​ 为了优化 TIMESTAMP 的使用，建议使用显式的时区，而不是操作系统时区。比如在配置文件中显示地设置时区，而不要使用系统时区：
  [mysqld]
  time_zone = “+08:00”

简单总结一下这两种数据类型的优缺点：

• DATETIME 没有存储的时间上限，而TIMESTAMP存储的时间上限只能到‘2038-01-19 03:14:07’
• DATETIME 不带时区属性，需要前端或者服务端处理，但是仅从数据库保存数据和读取数据而言，性能更好
• TIMESTAMP 带有时区属性，但是每次需要通过时区计算时间，并发访问时会有性能问题
• 存储 DATETIME 比 TIMESTAMEP 多占用一部分空间

数值型时间戳（INT）

​ 我们也会使用 int 或者 bigint 类型的数值也就是时间戳来表示时间。

这种存储方式的具有 Timestamp 类型的所具有一些优点，并且使用它的进行日期排序以及对比等操作的效率会更高，跨系统也很方便，毕竟只是存放的数值。

​ 缺点也很明显，就是数据的可读性太差了，你无法直观的看到具体时间。

如果需要查看某个时间段内的数据

select * from t where created_at > UNIX_TIMESTAMP(‘2021-01-01 00:00:00’);

总结

• 支持时间的类型有：TIME、DATETIME和TIMESTAMP；
• 支持日期的类型有：DATE、DATETIME和TIMESTAMP；
• 支持小数秒的类型有：TIME、DATETIME和TIMESTAMP；
• 特殊的类型：YEAR；
• MySQL 8.0不支持两位的YEAR类型；
• 小数秒的精度可选值是0-6，默认是0，3代表毫秒，6代表微秒，而2代表10毫秒（如0.11就是110毫秒）；
• 存储TIMESTAMP值时会将时间从当前时区转换成UTC时间，返回时再转换回当前时区；
• 默认情况下连接的时区就是服务器的时区，当然每个连接也可以设置自己的时区；
• TIME类型还可以用来表示时间间隔；
• 合法但是超过范围的TIME值会保存为最近的边界值，比如-850:00:00保存为-838:59:59；
• TIMESTAMP有2038问题；
• TIMESTAMP和DATETIME都可以设置自动插入时间与更新时间；
• 使用频率最高的是DATETIME和TIMESTAMP。

常见用法

获取当前时间

1. CURDATE(), CURTIME(), NOW()：可以获取客户端所在时区的当前时间；
2. UTC_DATE(), UTC_TIME(), UTC_TIMESTAMP()：可以获得当前的UTC时间；
3. CURRENT_DATE(), CURRENT_TIME(), CURRENT_TIMESTAMP()：就是CURDATE(), CURTIME(), NOW()的别名

时间的解析

函数	返回值
YEAR()	日期的年份
MONTH()	月份的数字，从1到12
MONTHNAME()	月份名字，January到December
DAYOFMONTH()	日期的数字，从1到31
DAYNAME()	星期的名字，Sunday到Saturday
DAYOFWEEK()	星期的数字，周日开始，范围是1到7
WEEKDAY()	星期的数字，周一开始，范围是0到6
DAYOFYEAR()	日期在一整年中的数字，1到366
HOUR()	小时数，0到23
MINUTE()	分钟数，0到59
SECOND()	秒数，0到59
MICROSECOND()	微秒数，0到999999

-----------------------后续更新

COND() | 秒数，0到59 | |MICROSECOND()` | 微秒数，0到999999 |