【重学 MySQL】五十七、日期与时间类型

【重学 MySQL】五十七、日期与时间类型

在MySQL中，日期与时间类型用于存储和操作日期和时间数据。这些类型根据所需存储的信息的精确度和范围而有所不同。

YEAR类型

• 用途：用于存储年份。
• 格式：通常为4位字符串或数字（YYYY），但MySQL也支持2位年份格式（YY），不过这种格式已经不推荐使用。
• 取值范围：
  • 以4位字符串或数字格式表示YEAR类型，其格式为YYYY，最小值为1901，最大值为2155。
  • 以2位字符串格式表示YEAR类型，最小值为00，最大值为99。
    • 当取值为01到69时，表示2001到2069；
    • 当取值为70到99时，表示1970到1999；
    • 当取值整数的0或00添加的话，那么是0000年；
    • 当取值是日期/字符串的'0'添加的话，是2000年。
• 存储需求：1个字节。

从MySQL5.5.27开始，2位格式的YEAR已经不推荐使用。YEAR默认格式就是YYYY，没必要写成YEAR(4)，从MySQL 8.0.19开始，不推荐使用指定显示宽度的YEAR(4)数据类型。

TIME类型

• 用途：用于存储时间，不包含日期部分。
• 格式：HH:MM:SS，其中HH表示小时，MM表示分钟，SS表示秒。
• 取值范围：-838:59:59~838:59:59。TIME类型不仅可以表示一天之内的时间（必须小于24小时），还可以表示某个事件过去的时间或两个事件之间的时间间隔（可大于24小时，甚至为负）。
• 存储需求：3个字节。
• 插入格式：
  • 可以使用带有冒号的字符串，比如’D HH:MM:SS'、‘HH:MM:SS’、‘HH:MM’、‘D HH:MM’、'D HH’或’SS’格式，都能被正确地插入 TIME类型的字段中。其中D表示天，其最小值为0，最大值为34。如果使用带有D格式的字符串插入TIME类型的字段时，D会被转化为小时，计算格式为D*24+HH。当使用带有冒号并且不带D的字符串表示时间时，表示当天的时间，比如12:10表示12:10:00，而不是00:12:10。
  • 可以使用不带有冒号的字符串或者数字，格式为’HHMMSS’或者HHMMSS。如果插入一个不合法的字符串或者数字，MySQL在存储数据时，会将其自动转化为00:00:00进行存储。比如1210，MySQL会将最右边的两位解析成秒，表示00:12:10，而不是12:10:00。
  • 使用CURRENT_TIME()或者NOW()，会插入当前系统的时间。

DATE类型

• 用途：用于存储日期，不包含时间部分。
• 格式：YYYY-MM-DD，其中YYYY表示年份，MM表示月份，DD表示日期。
• 取值范围：1000-01-01~9999-12-31。
• 存储需求：3个字节。
• 插入格式：
  • 可以使用YYYY-MM-DD或YYYYMMDD格式的字符串。
  • 可以使用YY-MM-DD或YYMMDD格式的字符串，其中YY表示两位的年值，MySQL会根据规则将其转换为4位年份。
  • 可以使用CURRENT_DATE()或NOW()函数插入当前系统日期。

DATETIME类型

• 用途：用于存储日期和时间，精确到秒。
• 格式：YYYY-MM-DD HH:MM:SS。
• 取值范围：1000-01-01 00:00:00~9999-12-31 23:59:59。
• 存储需求：8个字节。
• 插入格式：
  • 可以使用YYYY-MM-DD HH:MM:SS或YYYYMMDDHHMMSS格式的字符串。
  • 可以使用YY-MM-DD HH:MM:SS或YYMMDDHHMMSS格式的字符串，其中YY表示两位的年值。
  • MySQL允许“不严格”语法，任何标点符号都可用作日期部分或时间部分之间的间隔符。

TIMESTAMP类型

• 用途：用于存储日期和时间，与DATETIME类型类似，但支持时区转换。
• 格式：YYYY-MM-DD HH:MM:SS。
• 取值范围：1970-01-01 00:00:01 UTC~2038-01-19 03:14:07 UTC。
• 存储需求：4个字节。
• 特点：
  • TIMESTAMP值的存储是以UTC（世界标准时间）格式保存的，存储时对当前时区进行转换，检索时再转换回当前时区。
  • 因此，查询时根据当前时区的不同，显示的时间值是不同的。
• TIMESTAMP和DATETIME的区别：
  • TIMESTAMP存储空间比较小，表示的日期时间范围也比较小
  • 底层存储方式不同，TIMESTAMP底层存储的是毫秒值，距离1970-1-1 0:0:0 0毫秒的毫秒值。
  • 两个日期比较大小或日期计算时，TIMESTAMP更方便、更快。
  • TIMESTAMP和时区有关。TIMESTAMP会根据用户的时区不同，显示不同的结果。而DATETIME则只能反映出插入时当地的时区，其他时区的人查看数据必然会有误差的。

其他注意事项

• 在选择日期与时间类型时，应根据实际需求考虑使用哪种类型，以提高存储效率和查询性能，同时保证数据的准确性。
• 对于不需要精确到秒的时间存储，可以考虑使用TIME或DATE类型以减少存储空间的使用。
• 对于需要同时存储日期和时间且需要时区转换的场景，应使用TIMESTAMP类型。

通过了解这些日期与时间类型及其特点和用法，您可以更好地设计数据库以满足各种存储和操作需求。

开发中经验

用得最多的日期时间类型，就是 DATETIME。虽然 MySQL 也支持 YEAR（年）、 TIME（时间）、DATE（日期），以及 TIMESTAMP 类型，但是在实际项目中，尽量用 DATETIME 类型。因为这个数据类型包括了完整的日期和时间信息，取值范围也最大，使用起来比较方便。毕竟，如果日期时间信息分散在好几个字段，很不容易记，而且查询的时候，SQL 语句也会更加复杂。

此外，一般存注册时间、商品发布时间等，不建议使用DATETIME存储，而是使用时间戳，因为DATETIME虽然直观，但不便于计算。