FORMA

数据类型

MySQL 数据类型详解

MySQL 支持多种数据类型，可分为数值、字符串、日期时间、枚举/集合以及 JSON 等。选择合适的类型可以优化存储空间和查询性能。

一、数值类型

类型字节数有符号范围无符号范围说明
TINYINT1-128 ~ 1270 ~ 255很小的整数（如性别、状态标志）
SMALLINT2-32768 ~ 327670 ~ 65535较小整数（如年龄、评分）
INT / INTEGER4-2^31 ~ 2^31-1（约 -21亿 ~ 21亿）0 ~ 2^32-1常用整数（如 ID、数量）
BIGINT8-2^63 ~ 2^63-10 ~ 2^64-1极大整数（如大型系统主键、时间戳）
DECIMAL(M, D)变长取决于 M, D取决于 M, D精确小数，用于金融、会计等需要精确保留的场景
FLOAT4±1.175E-38 ~ ±3.402E+38同上单精度浮点数，近似值
DOUBLE8±2.225E-308 ~ ±1.798E+308同上双精度浮点数，近似值（精度比 FLOAT 高）

说明：

  • DECIMAL(M, D)：M 表示总位数（精度），D 表示小数位数。例如 DECIMAL(10, 2) 可存储最大 99999999.99。
  • 对于货币金额，永远不要使用 FLOAT/DOUBLE，因为存在精度丢失。应使用 DECIMAL 或以“分”为单位存储整数。
  • 使用 UNSIGNED 属性可使字段只存储非负数，取值范围扩大一倍。

二、字符串类型

类型最大长度说明
CHAR(N)0 ~ 255 字符固定长度，不足部分用空格填充。适合长度固定的短字符串（如国家代码、MD5 哈希）。
VARCHAR(N)0 ~ 65535 字符（实际受行大小限制）可变长度，额外使用 1~2 字节存储长度。适合长度可变的字符串（如用户名、标题）。
TINYTEXT255 字符短文本块
TEXT65535 字符（约 64KB）较长的文本（如文章正文、评论）
MEDIUMTEXT16MB较长文本（如书籍章节目录）
LONGTEXT4GB极大文本
BLOB65535 字节二进制大对象（存储图片、文件等）
MEDIUMBLOB16MB较大二进制数据
LONGBLOB4GB极大二进制数据

选择建议：

  • 短字符串且长度接近固定 → CHAR
  • 长度变化较大且不超过 255 字符 → VARCHAR（但 VARCHAR 实际可更大，只是超过 255 需要额外考虑）
  • 较长的文字内容 → TEXT 系列
  • 二进制文件（图片、音频等）一般不建议存储在数据库中，而是存储文件路径，或用专门的对象存储。但如果必须存，使用 BLOB。

注意：TEXT 与 VARCHAR 的区别：TEXT 会单独存储，可能产生额外的行溢出，影响性能；索引时需指定前缀长度。

三、日期与时间类型

类型格式范围说明
DATE'YYYY-MM-DD''1000-01-01' ~ '9999-12-31'仅日期
TIME'HH:MM:SS' 或 'HHH:MM:SS''-838:59:59' ~ '838:59:59'仅时间（可用于时间段）
DATETIME'YYYY-MM-DD HH:MM:SS''1000-01-01 00:00:00' ~ '9999-12-31 23:59:59'日期+时间（不受时区影响）
TIMESTAMP'YYYY-MM-DD HH:MM:SS''1970-01-01 00:00:01' UTC ~ '2038-01-19 03:14:07' UTC带时区的日期+时间，存储为 UTC 值，转换因时区设置而定
YEAR'YYYY'1901 ~ 2155仅年份

选择建议：

  • 只需要日期 → DATE
  • 只需要时间 → TIME
  • 需要存储具体时刻且时区无关（如生日、历史事件） → DATETIME
  • 需要自动更新为当前时间（如 ON UPDATE CURRENT_TIMESTAMP）或与时区联动 → TIMESTAMP（注意 2038 年问题）
  • 存储占用：MySQL 5.6.4 起，TIMESTAMP 占用 4 字节，DATETIME 占用 5 字节（均为不含小数秒的部分，小数秒精度每级额外占 0~3 字节）；5.6.4 之前 DATETIME 占用 8 字节。根据需求选择。

常用时间函数：

sql
NOW()         -- 当前日期时间
CURDATE()     -- 当前日期
CURTIME()     -- 当前时间
DATE_ADD()    -- 日期加法
DATEDIFF()    -- 日期差

四、其他类型

1. ENUM（枚举）

从预定义列表中选取一个值。存储时占用 1~2 字节，比 VARCHAR 更高效。

sql
gender ENUM('male', 'female', 'other')

优点：数据紧凑、防止非法输入。缺点：修改枚举列表成本高，不灵活。适合状态固定且值很少的字段。

2. SET（集合）

从预定义列表中选取零个或多个值。每个值用一个位表示，最多 64 个选项。

sql
interests SET('sports', 'music', 'reading', 'travel')

存储时按位存储，占用 1~8 字节。适合多选的固定选项，但业务变更时不如关联表灵活。

3. JSON（MySQL 5.7+）

原生支持 JSON 数据类型，可存储和查询 JSON 文档。

sql
CREATE TABLE users (id INT, profile JSON);
INSERT INTO users VALUES (1, '{"name":"Alice", "age":30}');
SELECT profile->>'$.name' FROM users WHERE id = 1;

优点：灵活存储半结构化数据，支持索引（生成列）。缺点：查询性能不如规范化的列，适合不常变动结构的数据。

五、数据类型选择原则

  1. 尽量使用最小满足需求的类型：例如年龄用 TINYINT UNSIGNED（0~255）而非 INT。
  2. 简单优先：能用 INT 就不用 VARCHAR 存储数字，能用 DATE 就不用 DATETIME。
  3. 精度敏感用 DECIMAL：金额、重量等必须精确的场景。
  4. 字符串长度固定用 CHAR：如 MD5 值（32 字符）、国家代码（2 字符）。
  5. 大文本走 TEXT 或文件系统：避免在数据库中存储超大文本或二进制文件影响性能。
  6. 使用 ENUM/SET 谨慎：列表变化频繁时不适用，可改用关联表或应用层校验。
  7. JSON 字段适合存储不常查询的内部结构：如果需要通过 JSON 内部字段过滤，建议创建生成列并建立索引。

六、总结表

分类推荐类型不推荐类型
整数 IDINT / BIGINTVARCHAR（除非有特殊业务需求）
金额DECIMAL(10,2)FLOAT / DOUBLE
用户名VARCHAR(50)CHAR（长字段会浪费空间）
固定代码CHAR(2)VARCHAR（稍浪费存储 + 长度字节）
日期时间DATETIME 或 TIMESTAMPVARCHAR（丧失日期函数便利性和索引效率）
多选标志位运算 INT 或关联表SET（扩展性差）
JSON 数据JSON 类型TEXT（失去 JSON 函数支持）

正确选择数据类型，可以有效减少存储空间、提升查询性能，同时保证数据完整性。建议在表设计阶段仔细斟酌每个字段的类型和约束。

参考文献

资料说明
MySQL 8.0 手册官方
数据存储导读学习路径

相关文章

Series

mysql

11 / 11

常用函数