MySQL 数据类型：从底层原理到选型哲学

1. 概述：为什么数据类型很重要

在 MySQL 中，选择正确的数据类型是数据库设计的基石。这不仅决定了数据的存储方式，更深刻影响着：

• 存储空间：占用更少的磁盘和内存，直接影响 I/O 效率。

• 查询性能：合适的数据类型能让索引更高效，减少 CPU 开销。

• 数据完整性：防止无效数据（如将字符串存入数字字段）进入系统。

• 可扩展性：预估数据增长，避免后期因类型过小导致 Out of range 错误。

核心原则：

• 最小化原则：在满足需求的前提下，使用能存储所需数据的最小数据类型。

• 简单化原则：能用内置类型（如 INT）存储，就不用字符串模拟（如 VARCHAR 存数字）。

• 避免 NULL：除非必须，否则尽量定义为 NOT NULL。NULL 会使索引、统计和比较变得更加复杂。

2. 数值类型

数值类型是数据库中最常用的类型之一，MySQL 支持精确数值类型（整数、定点数）和近似数值类型（浮点数）。

2.1 整数类型

关键细节：

1. 有符号 vs 无符号

• Signed (默认)：允许负数。如果业务逻辑中没有负数，可以考虑使用 UNSIGNED。

• Unsigned：可以将正数的上限提高一倍。例如 INT UNSIGNED 最大值为 42亿，适合自增主键（避免主键用尽）。

• 注意：当 UNSIGNED 列与 SIGNED 列进行运算时，结果可能变成浮点数或报错，需谨慎混合使用。

2. 显示宽度 (M) 与 ZEROFILL

• 很多开发者误以为 INT(10) 限制了存储大小，这是错误的。(10) 只是显示宽度，不影响存储范围。

• 配合 ZEROFILL 使用时，不足宽度的数字会补零显示。例如 INT(5) ZEROFILL 存 1 显示为 00001。

• ZEROFILL 自动使该列变为 UNSIGNED。

3. 自增 (AUTO_INCREMENT)

• 通常用于主键。

• 在 InnoDB 引擎中，自增值在 MySQL 8.0 之后是持久化的，解决了重启后自增值回退的问题。

2.2 定点数类型

深入解析：

DECIMAL 是 MySQL 中唯一能精确存储小数的类型（用于货币、财务计算）。

• 精度原理：它不采用二进制浮点存储，而是使用二进制字符串将每 9 位十进制数打包成 4 个字节来存储。

  • 例如：DECIMAL(18, 2) 表示总共 18 位数字，其中小数位 2 位，整数位 16 位。

  • M 最大 65，D 最大 30。

• 性能：DECIMAL 的运算速度比 BIGINT 或 DOUBLE 慢。在高并发财务场景下，一种常见技巧是存储为 BIGINT 乘以 100 后的“分”，在应用层处理换算。

DECIMAL vs FLOAT/DOUBLE

• 如果你需要精确比较（如 WHERE price = 0.1），必须用 DECIMAL，因为浮点数存在精度丢失。

• 如果数据量极大且对精度要求不高（科学计算），浮点数更省空间且计算快。

2.3 浮点数类型

重要警告：

在 MySQL 中，FLOAT 和 DOUBLE 是近似数值。

• 陷阱：SELECT 0.1 + 0.2 在浮点下结果可能为 0.30000000000000004。

• 排序与分组：因为精度问题，对浮点数进行 ORDER BY 或 GROUP BY 可能会产生意外结果。

• MySQL 8.0 变化：强烈建议使用 DECIMAL 代替浮点数进行货币计算，除非有明确的科学计算需求。

2.4 BIT 类型

• BIT(M)：存储位字段值，M 范围 1-64。

• 用途：存储标志位、权限集。例如 BIT(8) 可以存储 8 个布尔状态。

• 注意：BIT 在客户端显示通常为二进制字符串，查询时可能需要 HEX() 或 BIN() 函数转换，直观性较差。对于少量布尔值，TINYINT(1) 更为常用。

3. 字符串类型

3.1 字符类型: CHAR vs VARCHAR

这是开发中最常见的抉择。

选型建议：

• 使用 CHAR：存储长度几乎固定的值。如：身份证号(18位)、MD5(32位)、国家代码、定长编码。

• 使用 VARCHAR：存储长度变化较大的值。如：用户名、地址、标题。

• 注意 UTF8MB4：在 MySQL 5.7+ / 8.0 中，推荐使用 utf8mb4 字符集（支持 Emoji）。此时 VARCHAR(255) 理论上最多占用 255 * 4 = 1020 字节，加上前缀依然受限于行大小限制（65,535 字节）。

行大小限制：

• MySQL 中单行最大大小为 65,535 字节（所有列总和）。

• 如果尝试创建 VARCHAR(20000) 但总行超过限制，会自动转换类型或报错。

3.2 文本类型 (BLOB / TEXT)

当字符串长度超过 VARCHAR 上限 (65,535) 时，需要使用 TEXT 或 BLOB 系列。

重要性能考量：

TEXT vs VARCHAR 对比：

• VARCHAR 在行内存储，TEXT 行外存储。

• VARCHAR 在 8.0 中最大支持 64KB，如果内容不超过此值且长度波动不大，VARCHAR 性能优于 TEXT。

3.3 二进制类型 (BINARY / VARBINARY / BLOB)

• BINARY / VARBINARY：类似于 CHAR / VARCHAR，但存储的是字节串而非字符串。没有字符集概念，比较时按字节值比较。

• BLOB：二进制大对象，用于存储图片、音频、视频、序列化对象、加密数据等。分为 TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB。

• 注意：TEXT 有字符集转换，而 BLOB 不会。如果存储二进制数据（如压缩包、图片），必须用 BLOB，否则字符集转换可能损坏数据。

4. 日期和时间类型

4.1 核心区别：DATETIME vs TIMESTAMP

这是面试和设计中极易混淆的点。

• 时区处理：

  • TIMESTAMP：存储的是相对于 UTC 的时间。存入时，MySQL 将客户端时区的时间转为 UTC；取出时，再转为当前会话时区。这使得它在跨国系统中极其方便。

  • DATETIME：存储的是字面量。你存入 '2023-01-01 00:00:00'，取出来就是它，无论时区如何设置。

• 存储空间：在支持小数秒（毫秒级）后，两者存储空间相近，但 TIMESTAMP 略小（4 vs 5/8）。

• 范围：TIMESTAMP 只能到 2038年（Unix 时间戳溢出）。如果业务需要存 2038 年以后的日期，必须使用 DATETIME。

4.2 小数秒 (Fractional Seconds)

MySQL 5.6.4+ 支持微秒精度。

• 语法：DATETIME(3) 表示毫秒精度，TIMESTAMP(6) 表示微秒精度。

• 存储增加：精度 0 占 0 字节，1-2 占 1 字节，3-4 占 2 字节，5-6 占 3 字节。

4.3 自动初始化与自动更新

这是 ORM 开发中最常用的特性。

sql

— MySQL 5.6.5+ 语法
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

• DEFAULT CURRENT_TIMESTAMP：插入时自动填充当前时间。

• ON UPDATE CURRENT_TIMESTAMP：每次行更新时自动更新为当前时间。

• 注意：一个表中只能有一个 TIMESTAMP 列拥有 CURRENT_TIMESTAMP 默认值的旧限制已放宽，现在 DATETIME 也支持该特性。

5. JSON 类型

MySQL 5.7.8+ 引入了原生 JSON 数据类型，这是 MySQL 向 NoSQL 能力迈进的重要一步。

核心优势

• 自动验证：只允许存储合法的 JSON 文档，否则报错。

• 高效存储：解析后的 JSON 以二进制格式存储，读取时无需重新解析。

• 索引支持：可以通过多值索引或生成列索引来优化 JSON 字段的查询。

常用函数

• JSON_EXTRACT(json_col, '$.key') 或 json_col->'$.key'：提取值。

• json_col->>'$.key'：提取值并去除引号（返回字符串）。

• JSON_ARRAY(), JSON_OBJECT()：构建 JSON。

• JSON_CONTAINS(), JSON_OVERLAPS()：查询包含关系。

性能考量

• 虽然 JSON 提供了灵活性（无模式设计），但不建议将 JSON 作为所有字段的“万能垃圾桶”。

• 如果字段经常作为 WHERE 条件，应提取为普通列并建立索引，而不是在 JSON 内部查询（即使有多值索引，性能也不及原生列）。

• JSON 列会占用额外空间，且更新操作可能产生碎片。

6. 空间类型 (Spatial Types)

用于地理信息存储，如 GPS 坐标、地图数据。

MySQL 8.0 极大增强了空间功能：

• 支持 SRID (空间参考标识符)，如 SRID=4326 代表 WGS84 坐标系（GPS标准）。

• 支持空间索引 (SPATIAL INDEX)，使用 R-Tree 结构，适合范围查询。

• 常见查询：ST_Distance_Sphere() 计算球面距离，MBRContains() 查询矩形范围内的点。

7. 其他数据类型

• ENUM：枚举类型。存储时占用 1-2 字节，内部实际存储索引数字。

  • 优点：紧凑，可读性好。

  • 缺点：扩展性差（修改枚举值需要 ALTER TABLE），排序按内部索引值而非字面值，易产生混淆。

• SET：集合类型。一个字段可以存储多个值（如 'A,B,C'），最多 64 个成员。

• BOOL / BOOLEAN：实际上是 TINYINT(1) 的同义词。0 为 false，非 0 为 true。

8. 数据类型选型策略与最佳实践

8.1 主键选型

• 推荐整数：INT UNSIGNED (最多 42亿) 或 BIGINT (数据量极大)。

• 慎用 UUID：

  • VARCHAR(36) UUID 是随机的，导致 B+Tree 索引页频繁分裂，插入性能极差。

  • 优化方案：使用 BINARY(16) 存储，配合 UUID_TO_BIN 函数转换，或者使用 UUIDv7（基于时间戳排序）。

• 分布式 ID：雪花算法 ID (Snowflake) 通常是 64 位整数，应存储为 BIGINT。

8.2 字符集与排序规则

• utf8 vs utf8mb4：MySQL 中的 utf8 最多 3 个字节，不支持 Emoji。务必使用 utf8mb4。

• 排序规则：

  • utf8mb4_general_ci：速度快，但某些字符比较不够精确（如德语 ß = ss）。

  • utf8mb4_unicode_ci：精确但略慢。

  • utf8mb4_0900_ai_ci (MySQL 8.0 默认)：基于 Unicode 9.0，ai 表示口音不敏感，推荐使用。

8.3 范式与反范式

• 范式化：减少冗余，利于写性能和数据一致性。

• 反范式化：为了提高读性能，适当冗余存储一些字段（如冗余用户名到订单表），避免 Join。使用 VARCHAR 冗余时，要考虑数据同步问题。

8.4 NULL 的处理

• NULL 在索引中需要额外标记，且 COUNT(column) 会忽略 NULL 值，容易导致业务逻辑错误。

• 替代方案：使用 NOT NULL DEFAULT ''（字符串）或 NOT NULL DEFAULT 0（数值）。

8.5 时间类型选择

• 只需要日期：DATE。

• 需要时间点且不涉及时区：DATETIME（推荐，因范围大）。

• 需要自动时区转换且确定不超 2038 年：TIMESTAMP。

• 需要毫秒/微秒级精度：在类型后加括号如 DATETIME(3)。

结语

MySQL 的数据类型设计是一个权衡艺术：空间换时间、精度换性能、扩展性换约束。理解每种类型的底层存储机制和适用场景，能够帮助你构建出既高效又易于维护的数据库架构。

在实际开发中，建议遵循以下步骤：