mysql 善用 bigint 取代 datetime；MySQL如何存日期类型 和 枚举类型；bigint vs datetime

2025.2.10 更新结论

1. 还是使用 datetime 来，精确到秒 适合大多数场景；
2. 若使用索引的情况下，datetime与 bigint 效率差距会缩小；
3. datetime 程序员更喜欢，过度追求效率 也没多大意义，高级语言就是为了满足人类的思维和感觉而设计；
4. json序列化成 yyyy-mm-dd hh:mm:ss 程序员更加直接了当的调试；
5. 若json库支持 datetime 与 yyyy-mm-dd hh:mm:ss 格式的字符串转换，开发人员就可以使用 datetime表示普通的时间，当需要精确到毫秒的时候，去选择 bigint，就是说 开发人员 对datetime和 bigint都有选择的权利，而不只是只能选择 bigint；
6. sqlite不支持 datetime，sqlite属于本地嵌入式数据库，在IM以外的大多数业务里，都是不需要本地数据库的；sqlite 本地数据库应该单独出来设计模块；

故而决定 json库支持 datetime，且默认时间类型是datetime，特殊场景下 开发人员自由使用 bigint;

先上结论：

1. MySQL里 表示 时间 就用 bigint（默认的有符号的就足以了），单位是秒就可；精度有要求的也可以是毫秒，但是感觉没有必要吧；

2. MySQL里 存枚举，就使用 整型 tinyInt 和 smallInt 就可以了，没有必要使用 Int;

   数据类型	字节	范围（有符号）	范围（无符号）	备注
   TINYINT	1	-128 到 127	0 到 255	非常小的整数
   SMALLINT	2	-32,768 到 32,767	0 到 65,535	小的整数
   MEDIUMINT	3	-8,388,608 到 8,388,607	0 到 16,777,215	中等大小的整数
   INT	4	-2,147,483,648 到 2,147,483,647	0 到 4,294,967,295	标准的整数
   BIGINT	8	-9,223,372,036,854,775,808 到 9,223,372,036,854,775,807	0 到 18,446,744,073,709,551,615	大整数

3. 无论是 日期类型的 bigInt 还是 枚举类型的 tinyInt/smallInt都要使用默认的无符号就可；避免有些程序语言 没有无符号的基本类型，比如：golang里有int8和uint8这样的基本类型，而java里只有Byte（有符号单字节），所以为了统一 还是只用 无符号的整型；

---

1. 先看下json支持的数据类型

可见json并不支持日期类型和枚举类型；为什么呢，问下AI机器人：

JSON（JavaScript Object Notation）本身并不直接支持日期和枚举类型，这主要是由其设计目标和简洁性所决定的。以下是对这一问题的详细解释：

不支持日期类型的原因：

1. 设计简洁性：JSON的设计初衷是作为一种轻量级的数据交换格式，因此其数据类型被限制在基本类型（如数字、字符串、布尔值、对象和数组）以及null。这种设计使得JSON易于解析和生成，同时也降低了实现的复杂性。
2. 日期格式的多样性：日期和时间有多种表示方式和格式，如果JSON直接支持日期类型，就需要定义一个标准的日期格式。然而，由于全球各地日期和时间的表示方法存在差异，这可能会引发兼容性问题。
3. 灵活性：虽然JSON不支持直接的日期类型，但开发者可以通过字符串形式表示日期和时间。这种方式提供了更大的灵活性，允许开发者根据需要选择适合的日期格式，并在解析时进行相应的处理。

不支持枚举类型的原因：

1. 语言无关性：JSON是一种语言无关的数据交换格式，这意味着它应该能够在不同的编程语言和平台之间轻松交换数据。然而，枚举类型在不同的编程语言中有不同的实现方式和限制，因此直接在JSON中支持枚举类型可能会导致兼容性问题。
2. 可扩展性：如果JSON直接支持枚举类型，那么当枚举值发生变化时（例如添加或删除枚举项），可能需要更新所有使用这些枚举值的JSON数据。这会降低数据的可扩展性和可维护性。
3. 替代方案：尽管JSON不支持直接的枚举类型，但开发者可以通过使用字符串或数字来表示枚举值。在解析JSON数据时，可以根据这些值映射到相应的枚举类型。这种方法虽然需要额外的解析步骤，但提供了更大的灵活性和可扩展性。

综上所述，虽然JSON不支持日期和枚举类型可能会给开发者带来一些不便，但这种设计选择有助于保持JSON的简洁性、灵活性和语言无关性。开发者可以通过其他方式（如字符串表示）来处理这些数据类型的需求。

---

JSON通讯的一般的流程

通常 日期类型，我们在MySQL里 使用 datetime，通过java 的orm框架 映射出来的 java类 里对应的 datetime，若把这个类的这个datetime字段序列化成json返回给客户端，需要制定序列化的格式，客户端传json过来，服务端把json里的 datetime序列化成 java里的datetime也需要制定 反序列化的格式；比较繁琐；

• 客户端 ---json(datetime)---> 服务端， 客户端需要制定 序列化时间的格式 + 服务端指定反序列化的格式；
• 客户端 <---json(datetime)--- 服务端，客户端指定反序列格式 + 服务端指定序列化格式；

可见 客户端 和 服务端 都需要制定 序列化 和 反序列化时间格式，还必须保持一致，一旦这4个地方不一致就会出现问题；

实例说明，若MySQL里有一个表user里有一个注册时间字段，使用的是datetime类型，则mybatis默认生成的实体类字段，是java.util.date类型 ；

• register_time（java.util.date） --- 按照一定格式( YY-mm-dd HH:mm:ss)---> json( 2024-04-19 16:36:31) ---->返回给客户端，delphi客户端是datetime类型的字段，也需要按照一定的格式（YY-mm-dd HH:mm:ss）来进行反序列化，形成delphi类；
• delphi类的register_time按照一定的时间格式序列化成json -----> spring boot 按照一定的时间格式反序列化成Java.util.date字段 ---> 再通过mybatis 字段映射把java.util.date 转成 MySQL里的 datetime；

我们看下 mybatis <---> java 类型映射表

MySQL 字段类型	Java 类型
BIGINT	Long 或 BigInteger
INT	Integer
SMALLINT	Short
TINYINT	Byte 或 Boolean（如果用于表示布尔值）
FLOAT	Float
DOUBLE	Double
DECIMAL	BigDecimal
CHAR, VARCHAR	String
TEXT	String
DATE, DATETIME, TIMESTAMP	java.util.Date, java.sql.Timestamp
BLOB	byte[]

请注意，这只是一个基本的映射表，具体的映射可能因实际需求和数据库设计而有所不同。例如，有时你可能希望将 TINYINT 映射为 Integer 而不是 Byte，或者你可能希望将 DECIMAL 映射为 Double 而不是 BigDecimal，这完全取决于你的具体需求。

同时，MyBatis 也支持自定义的类型处理器（Type Handler），如果你需要处理特殊的数据类型映射，你可以通过实现 MyBatis 的 TypeHandler 接口来定义自己的类型处理器。

此外，还需要注意的是，虽然上述表格给出了 MySQL 字段类型与 Java 类型之间的常见映射，但在实际开发中，还需要考虑字段的具体用途和含义。例如，如果一个 TINYINT 字段用于表示布尔值（如 0 表示 false，1 表示 true），那么在 Java 中可能更适合映射为 Boolean 类型。

---

Datetime vs bigint

可见 MySQL里 若是使用 datetime，会导致挺多的麻烦，转换来，转换去，故推荐使用 bigint来表示 datetime；优势：

1. 简单方便，bigint 比datetime更加 简单暴力 方便，序列化 反序列化，以及存入MySQL的过程中，都不需要转换来转换去，只需要在终端展示层，转换一次；
2. 查询效率高，bigint 与 datetime都是占8个字节，显然整型 更方便范围查找和排序，比如 between and 、order by，整型更具优势；
3. 更符合json这个协议，json也不支持日期类型；
4. 很多终端数据库，也没有日期类型，如：sqlite 方便终端与服务端数据库表的设计统一；
5. 网络流量优势，若使用 datetime + 时间格式，最终是这样 "login_time":"2024-04-19 16:48:21"，而若是bigint则是"login_time":123456789，更节省流量；
6. 熵值问题，根据热力学第二定律，熵值越高，程序越复杂，越容易混乱；我们使用bigint来表示时间，简单暴力，不需要配置很多时间格式问题；降低熵值，降低程序的维护成本；
7. 序列化 + 反序列化 过程中不需要提前设定好时间格式，降低复杂度 和 维护成本；
8. 目前 抖音、拼多多、快手的 开放平台看，他们的订单表 都是使用 bigint来表示时间，可以看下 他们的订单列表接口的入参就知道；如：

---

之前写的博客 再次说一下：

1.若用int来存秒数的话，用北京时区 最大 可以存到到 什么时间？

若使用int类型来存储以秒为单位的时间戳，并且考虑北京时区，我们需要注意以下几点：

1. int类型的范围：在MySQL中，一个有符号的int类型能够表示的秒数范围是-2,147,483,648到2,147,483,647。但是，由于时间戳通常不会是负数，我们主要关注正数范围，即最大值为2,147,483,647秒。

2. Unix时间戳的起点：Unix时间戳是从1970年1月1日 00:00:00 UTC开始计算的。北京时区是UTC+8，所以对应的北京时间是1970年1月1日 08:00:00。

3. 计算最大时间：将int类型能表示的最大秒数2,147,483,647秒加到Unix时间戳的起点上。这个时间戳对应的UTC时间是2038年1月19日 03:14:07。由于北京时区是UTC+8，因此这个时间在北京时间是2038年1月19日 11:14:07。

综上所述，若使用int类型存储秒数来表示时间，并且考虑北京时区，最大可以存到的时间是2038年1月19日 11:14:07。超过这个时间点，int类型将无法准确表示，因为会超出其能表示的最大范围。如果需要存储更长时间范围内的时间戳，可以考虑使用BIGINT类型或者其他日期时间数据类型。

显然是 int是 不行了 ，最多2038年，不满足，接下来就看下 bigint和 datetime

2. mysql里的datetime 占几个字节 ?

在MySQL中，DATETIME 类型占用8个字节（64位）。这个类型用于表示日期和时间值，范围从1000-01-01 00:00:00到9999-12-31 23:59:59。由于其固定的字节大小和明确的范围，DATETIME 类型在处理日期和时间时提供了稳定性和一致性。

bigInt 也是8个字节；

3. MySQL里 存 日期，用 bigint和 用 datetime 对比？

在MySQL中存储日期时，选择使用BIGINT和DATETIME类型有其各自的优缺点。以下是对这两种数据类型的详细对比：

BIGINT

优点：

1. 灵活性：BIGINT可以存储任何整数，这意味着你可以用它来存储从某个固定时间点（如Unix纪元1970年1月1日）开始的秒数、毫秒数或其他时间单位。这种灵活性允许你根据需要选择时间的精度。

2. 性能：在某些情况下，整数的比较和计算可能比日期时间类型的操作更快，尤其是在进行范围查询或数学运算时。

3. 存储空间：虽然BIGINT占用8个字节，与DATETIME相同，但如果你选择存储更精细的时间单位（如毫秒），则可以在一个字段中记录更多信息。

缺点：

1. 可读性：BIGINT存储的时间戳对人类来说不易读，需要转换为实际的日期和时间格式才能理解。

2. 查询复杂性：进行日期范围查询或提取日期部分（如年、月、日）时，可能需要额外的计算和转换。

3. 时区处理：如果应用需要处理多个时区，使用BIGINT可能需要额外的逻辑来转换时间戳到不同的时区。

DATETIME

优点：

1. 可读性：DATETIME以人类可读的格式（'YYYY-MM-DD HH:MM:SS'）存储日期和时间，这使得数据更容易被理解和解释。

2. 易用性：MySQL为DATETIME类型提供了丰富的日期和时间函数，使得日期计算和格式化变得简单直接。

3. 时区无关性：DATETIME类型记录的是“绝对”时间，与时区无关。这意味着你不需要担心时区转换问题，除非你需要明确处理时区。

缺点：

1. 灵活性限制：与BIGINT相比，DATETIME的精度限制在秒级。如果你需要毫秒或微秒级的精度，你可能需要使用TIMESTAMP(3)或TIMESTAMP(6)等其他类型。

2. 性能考虑：虽然在现代数据库中日期时间类型的操作已经高度优化，但在某些极端情况下，日期时间的比较和计算可能会比整数操作稍慢。

3. 存储空间：虽然DATETIME也占用8个字节，但如果你使用BIGINT并只存储到天的精度，那么BIGINT可能会更加节省空间（尽管这种差异在大多数情况下可以忽略不计）。