Mysql的日期

一时失言乱红尘 2023-10-12 17:46 30阅读 0赞

## 数据库常用的时间日期 ##

<table> 
 <thead> 
  <tr> 
   <th align="left">日期类型</th> 
   <th>存储空间</th> 
   <th>日期格式</th> 
   <th>日期范围</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">time</td> 
   <td>3 bytes</td> 
   <td>HH:MM:SS</td> 
   <td>-838:59:59 ~ 838:59:59</td> 
  </tr> 
  <tr> 
   <td align="left">date</td> 
   <td>3 bytes</td> 
   <td>YYYY-MM-DD</td> 
   <td>1000-01-01 ~ 9999-12-31</td> 
  </tr> 
  <tr> 
   <td align="left">year</td> 
   <td>1 bytes</td> 
   <td>YYYY</td> 
   <td>YEAR(2):1970-2070，year(4) : 1901-2155</td> 
  </tr> 
  <tr> 
   <td align="left">datetime</td> 
   <td>8 bytes</td> 
   <td>YYYY-MM-DD HH:MM:SS</td> 
   <td>1000-01-01 00:00:00 ~ 9999-12-31 23:59:59</td> 
  </tr> 
  <tr> 
   <td align="left">timestamp</td> 
   <td>4 bytes</td> 
   <td>YYYYMMDDHHMMSS</td> 
   <td>1970-01-01 00:00:00 ~ 2037-12-31 23:59:59</td> 
  </tr> 
  <tr> 
   <td align="left">varchar</td> 
   <td>14+ bytes</td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td align="left">int</td> 
   <td></td> 
   <td></td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

日期类型字段的设计，影响数据库的查询效率，同时与业务功能的正确性也有很大的关系。

###### 1. 字符串 存储日期的缺点 ######

*  无法使用mysql中提供的日期函数，为mysql很多查询带来不便：

> 举例：用户表有个字段birthday，表示用户的生日，查询2023年出生的所有用户  
> 如果birthday 是日期类型，可以使用YEAR函数  
> 如果birthday 是字符串类型，无法使用year函数，需要做各种转换处理。

*  占用空间较大：

> 举例：存时间：2023-01-01 00:00:00
> 
> <table> 
>  <thead> 
>   <tr> 
>    <th>存储类型</th> 
>    <th>需要字节长度</th> 
>   </tr> 
>  </thead> 
>  <tbody> 
>   <tr> 
>    <td>varchar</td> 
>    <td>19个字节</td> 
>   </tr> 
>   <tr> 
>    <td>datatime</td> 
>    <td>8个字节</td> 
>   </tr> 
>   <tr> 
>    <td>timestamp</td> 
>    <td>4个字节</td> 
>   </tr> 
>  </tbody> 
> </table>

###### 2. int 类型存时间 ######

存时间戳，存一个int类型的数值，用一个时间戳来表示时间。

*  优点  
    用int存时间，需要进行日期排序以及按日期范围查询时，就变成了普通的数字的比较了，查询速度较快。
 *  缺点  
    致命问题，可读性差。  
    特例，20230412 用int方式存，比时间戳可读性高，但是无法用时间函数进行查询，查一段时间范围内的数据需要报时间转换成这种格式，数据查出来还要手动处理才能转换成Date类型数据。

###### 3. datetime VS timestamep 比较 ######

*  占用空间  
    官网介绍：  
    ![在这里插入图片描述][dfe7fec034b242f1a14ddd6c6fb9c769.png]

> 在 MySQL5.6.4 是一个分水岭  
> 在 MySQL5.6.4 之前，DATETIME 固定占用 8 个字节。  
> 从 MySQL5.6.4 开始，DATETIME 类型开始支持毫秒，DATETIME(N) 中的 N 表示毫秒的精度，例如，DATETIME(6) 表示可以存储 6 位的毫秒值，那么此时，DATETIME 占用的字节数，就跟后面的毫秒数有关了，如果 DATETIME 没有详细到毫秒，那么占用 5 个字节，如果详细到毫秒了，那就看情况，根据毫秒的精度，占用不同的空间，毫秒精度小于等于 2 时，总共占用 6 个字节；毫秒精度小于等于 4 时，总共占用 7 个字节；毫秒精度小于等于 6 时，总共占用 8 个字节。  
> 同样，由上图我们也可以看出，在 MySQL5.6.4 之前，TIMESTAMEP 固定占用 4 个字节，从 MySQL5.6.4 开始，依据毫秒的精度，TIMESTAMEP 占用的字节数介于 4 到 7 之间。

所以无论是 TIMESTAMEP，还是 DATETIME，都是比字符串节省空间的。

*  存储范围

> DATETIME 的存储范围介于 1000-01-01 00:00:00 到 9999-12-31 23:59:59 之间。  
> TIMESTAMP 的存储范围则介于 1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC 之间。

很明显 DATETIME 的存储范围要更大一些。

*  性能比较

> 从毫秒数转换到 TIMESTAMP 并不费事，但是当要进行时区转换的时候，需要调用操作系统底层系统函数，而这个函数需要额外的加锁操作，以确保这时操作系统时区没有修改，一加锁，效率就低了。  
> 对于这个问题，只存在于 TIMESTAMP 中，因为 DATETIME 不存在时区转化问题。  
> 对于 TIMESTAMP，建议使用显式的时区，而不是操作系统时区。

datetime不存在时区转化的问题，timestamep建议使用显式的时区

*  底层存储

> TIMESTAMP 类型最大的优势在于自带时区属性，因为它本质上是从毫秒转化而来。如果你的业务需要对应不同的国家时区，那么类型 TIMESTAMP 是一种不错的选择，TIMESTAMP 类型字段的值会随着服务器时区的变化而变化，自动换算成相应的时间，说简单点就是在不同时区，查询到同一个条记录此字段的值会不一样。

举个 TIMESTAMP 的使用场景例子：

> 新闻类的业务，通常用户想知道这篇新闻发布时对应的自己国家时间，那么 TIMESTAMP 是一种不错的选择。  
> TIMESTAMP 会随着时区的变化而自动调整，而 DATETIME 不会。

时区的问题一定要谨慎，不过时区问题也并非一定要在数据库中解决，也可以在前端或者服务端用代码处理下。

[dfe7fec034b242f1a14ddd6c6fb9c769.png]: https://img-blog.csdnimg.cn/dfe7fec034b242f1a14ddd6c6fb9c769.png