wonder/cs-note
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
cs-note/hhs/MySQL/01-入门基础/04-数据类型全景.md
T
wonder 30d312ac35 Init
2026-05-24 11:42:38 +08:00

12 KiB
Raw Permalink Blame History

tags, create time
tags create time
MySQL
数据类型
INT
VARCHAR
JSON
2026-05-16 00:30

MySQL 数据类型全景

概述

MySQL 的数据类型可按存储的内容分为六大类。选型的黄金法则：用满足业务需求的最小类型，节省空间、提升索引效率与查询性能。

大类 包含类型 核心考量
整数 TINYINT / SMALLINT / MEDIUMINT / INT / BIGINT 够用就行，别上来就 BIGINT
浮点/定点 FLOAT / DOUBLE / DECIMAL 金额永远用 DECIMAL
字符串 CHAR / VARCHAR / TEXT 系列 定长选 CHAR，不定长选 VARCHAR
日期时间 DATE / TIME / DATETIME / TIMESTAMP / YEAR 跨时区用 TIMESTAMP，否则 DATETIME
JSON JSON 灵活扩展字段，搭配 Generated Column 做索引
枚举/集合 ENUM / SET 值域固定且极少变才考虑
二进制 BINARY / VARBINARY / BLOB 系列 存文件用对象存储，数据库里只存引用

[!QUESTION] 为什么总强调"用小的就行"？ 想象一张百万行的订单表，如果主键用了 BIGINT(8 bytes) 而非 INT(4 bytes)，仅此一列就多花 4MB。这还没算上二级索引——InnoDB 的二级索引叶子节点会完整存储主键值，每个二级索引同样多花 4MB。表越大，连锁放大效应越惊人。

整数类型

graph TD
    INT_TYPES["整数类型家族"]
    INT_TYPES --> TINY["TINYINT<br/>1 byte | ±128"]
    INT_TYPES --> SMALL["SMALLINT<br/>2 bytes | ±32K"]
    INT_TYPES --> MED["MEDIUMINT<br/>3 bytes | ±8M"]
    INT_TYPES --> INTN["INT<br/>4 bytes | ±21亿"]
    INT_TYPES --> BIG["BIGINT<br/>8 bytes | ±9.2×10^18"]

    style INT_TYPES fill:#5F2799,color:#fff
    style TINY fill:#C44569,color:#fff
    style SMALL fill:#C44569,color:#fff
    style MED fill:#C44569,color:#fff
    style INTN fill:#C44569,color:#fff
    style BIG fill:#FF6B6B,color:#000
类型 有符号范围 无符号范围 存储 典型用途
TINYINT -128 ~ 127 0 ~ 255 1 byte 状态标识、布尔标志、年龄
SMALLINT -32K ~ 32K 0 ~ 65K 2 bytes 短编码 ID
MEDIUMINT -8M ~ 8M 0 ~ 16M 3 bytes 中等范围计数
INT ±21 亿 0 ~ 42 亿 4 bytes 常规自增主键、外键
BIGINT ±9.2×10¹⁸ — 8 bytes Snowflake ID、金额计算

[!TIP] 能用小的就不用大的 TINYINT UNSIGNED 能存 255，够用就别用 INT。每列差 3 字节，百万行就是 3MB 额外开销，还会导致索引变厚、缓存命中率下降。

浮点与定点

类型 精度 存储 适用场景
FLOAT 单精度 7 位 4 bytes 科学计算、不需要精确的场景
DOUBLE 双精度 15 位 8 bytes 高精度科学计算
DECIMAL(M,D) 精确定点 M-D 位整数 + D 位小数 可变（约每 9 位数字 4 字节） 金额计算！绝对不要用浮点数存钱 
-- ❌ 错误示范：浮点数误差累积
SELECT 0.1 + 0.2;  -- 结果可能是 0.30000000000000004

-- ✅ 正确做法：DECIMAL
CREATE TABLE orders (
    amount DECIMAL(10, 2) NOT NULL  -- 最大 99999999.99
);
INSERT INTO orders VALUES (19.99 + 29.99);  -- 精确等于 49.98

[!QUESTION] 为什么不能用 FLOAT/DOUBLE 存金额？ IEEE 754 浮点数无法精确表示 0.1 这样的十进制小数。在财务场景中，微小的舍入误差经过多次加减后会累积成显著差异。DECIMAL 以字符串形式存储每一位数字，保证运算精确。

字符串类型

类型 存储规则 最大长度 特点
CHAR(n) 固定长度，不足空格填充 0~255 适合长度固定的数据（MD5 hash、状态码）
VARCHAR(n) 变长，前缀记录实际长度 0~65535（受行大小限制） 最常用的字符串类型
TINYTEXT 1 字节长度前缀 255 极短文本
TEXT 2 字节前缀 65535 文章摘要、评论
MEDIUMTEXT 3 字节前缀 16MB 长文章、富文本
LONGTEXT 4 字节前缀 4GB 超大文本（日志、JSON 文档）

CHAR vs VARCHAR 的选择

-- ✅ 适合 CHAR：定长数据
CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    status TINYINT DEFAULT 1,
    country_code CHAR(3) NOT NULL,  -- ISO 3166-1 alpha-3
    phone_prefix CHAR(4),            -- +86, +1, +44...
    email VARCHAR(255) UNIQUE        -- 不定长，用 VARCHAR
);

-- 为什么 gender 有时用 CHAR(1) 而非 TINYINT？
-- CHAR(1) 语义更明确，但本质上两者存储相同。
-- 关键是保持一致性——团队规范比个人偏好更重要。

[!NOTE] VARCHAR 的长度陷阱 MySQL 行大小上限 65535 字节，但这不只是所有 VARCHAR 加起来的大小。还要考虑：

  • 每列的 1~2 字节长度前缀
  • NULL 位图（允许 NULL 的列）
  • 实际存储使用 utf8mb4 的话，每个字符最多占 4 字节

所以 VARCHAR(255) 在 utf8mb4 下最大占用 255 × 4 + 2 ≈ 1022 字节。

日期和时间类型

类型 格式 存储 时区感知
DATE YYYY-MM-DD 3 bytes 无
TIME HH:MM:SS 3 bytes 无（时间段）
DATETIME YYYY-MM-DD HH:MM:SS 8 bytes 无（存储原始值）
TIMESTAMP YYYY-MM-DD HH:MM:SS 4 bytes 有（UTC 存储，显示时转换）
YEAR YYYY 1 byte — 
-- TIMESTAMP 的时区自动转换特性
CREATE TABLE events (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100),
    event_time DATETIME,     -- 存入什么就读出什么
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP  -- 自动填当前 UTC 时间
);

INSERT INTO events (name, event_time) VALUES ('Meeting', '2026-05-16 14:00:00');
-- 在上海时区 (UTC+8) 显示：14:00:00
-- 在纽约时区 (UTC-4) 显示：14:00:00（DATETIME 不变）
-- 但如果用 TIMESTAMP，它会转换成纽约本地时间 02:00:00

[!WARNING] TIMESTAMP 有保质期 TIMESTAMP 的范围是 1970-01-01 00:00:01 到 2038-01-19 03:14:07（32-bit 上限）。如果你的系统需要支持 2038 年之后的数据，请用 DATETIME。

JSON 类型

MySQL 5.7+ 引入原生 JSON 类型，支持部分查询和索引能力。

CREATE TABLE employees (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100),
    attributes JSON  -- 灵活扩展字段
);

INSERT INTO employees VALUES
(1, 'Alice', '{"dept": "Engineering", "skills": ["Go", "Python"], "level": 5}'),
(2, 'Bob', '{"dept": "Marketing", "skills": ["SEO", "Content"], "level": 3}');

-- JSON 路径查询
SELECT name, attributes->>'$.dept' AS department
FROM employees
WHERE attributes->>'$.level' >= 4;

-- JSON 数组包含判断
SELECT name FROM employees
WHERE JSON_CONTAINS(attributes->'$[*]', '"Go"');

-- 生成虚拟列 + 索引（最佳实践）
ALTER TABLE employees
    ADD COLUMN dept VARCHAR(50) GENERATED ALWAYS AS (attributes->>'$.dept') VIRTUAL,
    ADD INDEX idx_dept (dept);

flowchart LR
    A["JSON Column<br/>原始存储"] --> B["JSON Document"]
    B --> C["Scalar Values"]
    B --> D["Arrays"]
    B --> E["Nested Objects"]

    C --> F["Generated Column"]
    D --> F
    E --> F
    F --> G["Index<br/>Virtual / Stored"]

    style A fill:#00B6BC,color:#fff
    style F fill:#FF9F43,color:#000
    style G fill:#C44569,color:#fff

[!TIP] JSON vs 规范化表设计

  • 适合 JSON：配置项、标签集合、表单动态字段、低频更新的属性
  • 不适合 JSON：需要 JOIN 关联、频繁条件过滤、强一致性约束的字段
  • 关键技巧：用 Generated Column + Index 让 JSON 字段的筛选走索引

枚举与集合

-- ENUM: 限定可选值列表
CREATE TABLE tasks (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200),
    status ENUM('todo', 'in_progress', 'done', 'cancelled') DEFAULT 'todo',
    priority ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium'
);

-- SET: 多选值（逗号分隔存储）
CREATE TABLE tags (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100),
    categories SET('tech', 'business', 'lifestyle', 'health')
);

INSERT INTO tags VALUES (1, 'Go Tips', 'tech,business');

ENUM vs SET 对比

特性 ENUM SET
语义 单选：从列表中取一个值 多选：从列表中取零个或多个值
内部存储 整数索引（1 byte 或 2 bytes） 位图（最多 64 个成员占 8 bytes）
排序规则 按索引顺序，非字母序 按位数值排序
典型场景 工单状态、优先级 标签分类、权限角色

ENUM 还是 TINYINT？这是永恒争论

-- 方案 A：ENUM — 数据库层校验 + 省空间
status ENUM('open', 'closed') NOT NULL  -- 1 byte

-- 方案 B：TINYINT — 代码层校验 + 灵活
status TINYINT UNSIGNED NOT NULL  -- 1 byte
-- 应用层保证只写入 0/1/2/3
维度 选 ENUM 选 TINYINT
校验 数据库自动拒绝非法值 需应用层保证
调试 查出是数字 2，还要查 schema 才知道含义 直接读出原始数字，一目了然
修改成本 增删值需 ALTER TABLE（大表很贵） 随时在应用层加常量枚举
代码可追溯性 IDE 找不到所有使用处 grep 就能定位

[!WARNING] ENUM 的反模式警告

  • 不要用 ENUM 存用户可见的文案——后台查出来是 2，前端还得映射回去
  • 不要把 ENUM 当文档用——队友看不懂 status = 3 是什么意思
  • 推荐方案：中小项目用 TINYINT + Go const / Python IntEnum 在代码里维护枚举定义；只有在值域极稳定且不需要跨语言共享时再用原生 ENUM

[!NOTE] ENUM 的本质 ENUM 在内部存储为整数索引（1, 2, 3...），而不是字符串。这意味着：

  • ENUM 的排序是按索引而非字母顺序
  • 插入不在列表中的值会导致错误（或空字符串，取决于 sql_mode）
  • 修改 ENUM 列表顺序会影响已有数据的解释——谨慎维护

二进制类型

类型 说明 典型用途
BINARY(n) 定长二进制 哈希值（SHA256 = 32 bytes）
VARBINARY(n) 变长二进制 短二进制数据
TINYBLOB ≤ 255 bytes —
BLOB ≤ 65KB 缩略图、序列化对象
MEDIUMBLOB ≤ 16MB 文件附件
LONGBLOB ≤ 4GB 大文件存储 
-- 存储 SHA-256 hash 的推荐方式
CREATE TABLE file_metadata (
    id BIGINT PRIMARY KEY,
    filename VARCHAR(500),
    sha256_hash BINARY(32) NOT NULL,  -- CHAR(64) HEX 也可以，但 BINARY 省一半
    UNIQUE KEY uk_sha256 (sha256_hash)
);

BINARY vs VARBINARY — 定长与变长的选择

-- BINARY(32)：始终占 32 bytes，不足补 0x00
INSERT INTO t VALUES (X'61');       -- 实际存储: 61 00 00 ... 00（32 bytes）
SELECT HEX(col) FROM t;             -- 输出: 610000...（永远 64 个十六进制字符）

-- VARBINARY(32)：只存真实长度 + 1 byte 长度前缀
INSERT INTO t VALUES (X'61');       -- 实际存储: 61 01（2 bytes，01 表示长度）
SELECT HEX(col) FROM t;             -- 输出: 61（只有 2 个十六进制字符）
维度 BINARY(n) VARBINARY(n)
存储 固定 n 字节，右侧用 0x00 补齐 实际长度 + 1~2 bytes 前缀
比较规则 补齐 0x00 后再逐字节比较 按实际长度比较
适用场景 哈希值、加密密钥等定长数据 短二进制流、序列化片段

[!TIP] BINARY vs CHAR 的对称性 理解 BINARY 就理解了 CHAR——它们是对称的定长类型。区别仅在于：CHAR 补空格，BINARY 补零。同理 VARBINARY 对标 VARCHAR。类比记忆比死记硬背更可靠。

[!WARNING] 大文件不要存在数据库里 BLOB 系列看似方便，但会带来三个问题：

  1. 备份膨胀——数据库 dump 体积翻倍，恢复时间成倍增加
  2. 内存压力——SELECT 整行时 BLOB 内容也加载到内存，即使你不需要它
  3. 无法 CDN 加速——图片/附件走 OSS + Signed URL 是标准做法

最佳实践：数据库只存文件引用（OSS URL / 文件系统路径），文件本体放对象存储。

关联笔记

Reference in New Issue View Git Blame Copy Permalink