跳到主要内容

MySQL 数据类型

数据类型是数据库设计的基础。选择正确的数据类型不仅能确保数据的完整性和准确性，还能显著影响存储空间、查询性能和应用的可维护性。本章将深入讲解 MySQL 支持的各种数据类型、它们的存储原理以及实际应用中的最佳实践。

数据类型选择的重要性​

在开始学习具体类型之前，先理解数据类型选择对数据库的影响：

影响存储空间​

不同的数据类型占用不同的存储空间。例如，存储年龄使用 TINYINT（1字节）比 INT（4字节）节省 75% 的空间：

-- 不好的设计：浪费存储空间
age INT              -- 范围 -21亿到21亿，但年龄最多 0-150

-- 好的设计：选择合适的类型
age TINYINT UNSIGNED -- 范围 0-255，足够存储年龄

影响查询性能​

数据类型影响索引效率和查询速度。较小的数据类型意味着：

  • 更小的索引，可以缓存更多索引页
  • 更少的磁盘 I/O
  • 更快的排序和连接操作

影响数据完整性​

正确的数据类型可以在数据库层面约束数据的有效性：

-- 使用 ENUM 约束状态值
status ENUM('pending', 'approved', 'rejected')

-- 使用 UNSIGNED 约束非负数
quantity INT UNSIGNED

-- 使用 DECIMAL 确保金额精度
price DECIMAL(10, 2)

知识速查表​

类型示例适用场景
TINYINTTINYINT UNSIGNED状态值、年龄、开关标志
INTINTID、数量、计数器
BIGINTBIGINT UNSIGNED大ID、浏览量、时间戳毫秒
DECIMALDECIMAL(10, 2)金额、精确计算
DOUBLEDOUBLE科学计算、近似值
CHARCHAR(32)MD5、固定长度编码
VARCHARVARCHAR(255)姓名、邮箱、地址
TEXTTEXT文章内容、JSON数据
DATETIMEDATETIME创建时间、事件时间
TIMESTAMPTIMESTAMP自动更新的时间戳
JSONJSON配置、属性、扩展字段
ENUMENUM('A', 'B')状态、类型等固定值

数值类型​

数值类型是 MySQL 中最基础的数据类型，分为整数、浮点数和定点数三类。

整数类型详解​

MySQL 支持 5 种整数类型，主要区别在于存储空间和数值范围：

类型存储有符号范围无符号范围典型用途
TINYINT1字节-128 ~ 1270 ~ 255状态、开关、年龄
SMALLINT2字节-32,768 ~ 32,7670 ~ 65,535年份、小计数
MEDIUMINT3字节-838万 ~ 838万0 ~ 1677万中等计数
INT4字节-21亿 ~ 21亿0 ~ 42亿ID、数量
BIGINT8字节-922亿亿 ~ 922亿亿0 ~ 1844亿亿大ID、浏览量

存储原理​

整数以二进制形式存储，有符号数使用补码表示。例如 TINYINT 存储：

有符号 TINYINT：
  127  → 01111111
  0    → 00000000
  -1   → 11111111
  -128 → 10000000

无符号 TINYINT：
  255  → 11111111
  0    → 00000000

使用示例​

CREATE TABLE user_status (
    id INT AUTO_INCREMENT PRIMARY KEY,
    
    -- 状态值：0=禁用, 1=启用
    is_active TINYINT UNSIGNED DEFAULT 1,
    
    -- 用户类型：1=普通, 2=VIP, 3=管理员
    user_type TINYINT UNSIGNED DEFAULT 1,
    
    -- 年龄：0-255 足够
    age TINYINT UNSIGNED,
    
    -- 年份
    birth_year SMALLINT UNSIGNED,
    
    -- 普通计数
    login_count INT UNSIGNED DEFAULT 0,
    
    -- 大数值（如微博粉丝数）
    follower_count BIGINT UNSIGNED DEFAULT 0
);

-- 插入数据
INSERT INTO user_status (is_active, user_type, age, birth_year)
VALUES (1, 2, 25, 1999);

UNSIGNED 属性​

UNSIGNED 表示无符号，只存储非负数，范围扩大一倍：

-- 有符号 INT：-2147483648 到 2147483647
-- 无符号 INT：0 到 4294967295

-- 当确定数值不会为负时，使用 UNSIGNED
CREATE TABLE products (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    stock INT UNSIGNED DEFAULT 0,    -- 库存不可能为负
    price INT UNSIGNED               -- 价格（以分为单位）
);

注意事项：UNSIGNED 可能导致意外的计算结果：

-- 创建测试表
CREATE TABLE test_unsigned (
    a INT UNSIGNED,
    b INT UNSIGNED
);

INSERT INTO test_unsigned VALUES (1, 2);

-- 减法结果为负数时会溢出
SELECT a - b FROM test_unsigned;
-- 结果：18446744073709551615（不是 -1！）

-- 解决方案：使用 CAST 转换
SELECT CAST(a AS SIGNED) - CAST(b AS SIGNED) FROM test_unsigned;
-- 结果：-1

显示宽度（已弃用）​

-- INT(M) 中的 M 是显示宽度，不影响存储范围
-- MySQL 8.0.17+ 已弃用，仅用于兼容

-- 旧写法（不推荐）
id INT(11)

-- 推荐写法
id INT

显示宽度配合 ZEROFILL 可以在数值前补零，但这个特性也已弃用。如果需要格式化输出，建议在应用层处理。

浮点类型：FLOAT 和 DOUBLE​

浮点数用于存储近似值，适合科学计算等不需要精确值的场景。

类型存储精度说明
FLOAT4字节约7位有效数字单精度浮点数
DOUBLE8字节约15位有效数字双精度浮点数

存储原理​

浮点数遵循 IEEE 754 标准，由三部分组成：

FLOAT (32位):
┌───────┬────────────┬───────────────────────┐
│ 符号位 │   指数位    │       尾数位           │
│  1位  │    8位     │        23位            │
└───────┴────────────┴───────────────────────┘

DOUBLE (64位):
┌───────┬────────────┬───────────────────────┐
│ 符号位 │   指数位    │       尾数位           │
│  1位  │   11位     │        52位            │
└───────┴────────────┴───────────────────────┘

由于尾数位有限，某些十进制小数无法精确表示，这就是浮点数精度问题的根源。

精度问题演示​

CREATE TABLE float_test (
    id INT PRIMARY KEY,
    f FLOAT,
    d DOUBLE
);

-- 插入测试数据
INSERT INTO float_test VALUES (1, 0.1, 0.1);

-- 查看精度问题
SELECT f, f + 0.1, d, d + 0.1 FROM float_test WHERE id = 1;
-- 结果可能不是精确的 0.2

-- 更明显的例子
INSERT INTO float_test VALUES (2, 0.1 + 0.2, 0.1 + 0.2);
SELECT f, d FROM float_test WHERE id = 2;
-- f 可能显示 0.30000000447...
-- d 更精确但仍有微小误差

重要结论：永远不要用 FLOAT 或 DOUBLE 存储金额！

定点类型：DECIMAL​

DECIMAL 用于存储精确的小数，是存储金额的唯一正确选择。

存储原理​

DECIMAL 将数值转换为二进制编码的十进制（BCD）存储，每个 9 位十进制数字占用 4 字节：

DECIMAL(10, 2) 存储示例：
值：12345678.90

整数部分：12345678（8位）→ 需要 4 字节
小数部分：90（2位）→ 需要 1 字节
总存储：4 + 1 = 5 字节

存储需求计算：
┌─────────────────┬─────────────┐
│ 剩余位数        │ 所需字节     │
├─────────────────┼─────────────┤
│ 0 位            │ 0 字节      │
│ 1-2 位          │ 1 字节      │
│ 3-4 位          │ 2 字节      │
│ 5-6 位          │ 3 字节      │
│ 7-9 位          │ 4 字节      │
└─────────────────┴─────────────┘

使用语法​

DECIMAL(M, D)

-- M：总位数（精度），默认 10，最大 65
-- D：小数位数（标度），默认 0，最大 30

-- 示例
price DECIMAL(10, 2)    -- 总共10位，小数2位，整数8位
                       -- 范围：-99999999.99 到 99999999.99

salary DECIMAL(12, 2)   -- 适合薪资
rate DECIMAL(5, 4)      -- 适合利率：0.0001 到 9.9999

金额存储最佳实践​

CREATE TABLE orders (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    
    -- 金额使用 DECIMAL
    -- 建议：整数位足够大，小数位2位
    total_amount DECIMAL(12, 2),      -- 支持 9999999999.99
    
    -- 或者以"分"为单位存储整数
    total_cents BIGINT UNSIGNED,       -- 存储分，避免小数
    
    -- 税率等比例值
    tax_rate DECIMAL(5, 4) DEFAULT 0.0800  -- 8.000%
);

-- 插入数据
INSERT INTO orders (total_amount, total_cents, tax_rate)
VALUES (1234.56, 123456, 0.0800);

-- 精确计算
SELECT total_amount * tax_rate AS tax_amount FROM orders;
-- 结果：98.7648，精确无误

DECIMAL vs FLOAT vs DOUBLE 对比​

特性DECIMALFLOATDOUBLE
存储二进制编码十进制IEEE 754 单精度IEEE 754 双精度
精度精确值约7位有效数字约15位有效数字
性能较慢快快
适用场景金额、财务科学计算科学计算

BIT 类型​

BIT 用于存储位字段，适合存储多个开关或标志位：

CREATE TABLE permissions (
    id INT PRIMARY KEY,
    
    -- 8位权限标志
    -- 位1: 可读, 位2: 可写, 位3: 可删除, ...
    flags BIT(8)
);

-- 插入二进制值
INSERT INTO permissions VALUES (1, b'00000101');  -- 读和删除权限

-- 查询时转换为十进制或十六进制
SELECT id, flags, flags + 0 AS decimal_value, BIN(flags) AS binary_value
FROM permissions;

-- 使用位运算检查权限
SELECT * FROM permissions WHERE flags & b'00000001';  -- 有读权限

字符串类型​

字符串类型是 MySQL 中最常用的数据类型之一，正确选择和使用对性能有重要影响。

CHAR 与 VARCHAR 对比​

特性CHARVARCHAR
存储方式定长，不足补空格变长，按实际长度
最大长度255 字符65535 字节
存储开销M × 字符集字节数实际长度 + 1-2字节
更新性能不易产生碎片可能产生碎片
适用场景固定长度数据变长数据

存储原理​

CHAR 的存储：

-- CHAR(10) 在 utf8mb4 字符集下
-- 每个 ASCII 字符占 1 字节，汉字占 3 字节

CREATE TABLE char_test (
    code CHAR(10)  -- 固定10字符，不足用空格填充
);

INSERT INTO char_test VALUES ('ABC');
-- 实际存储：'ABC       '（补7个空格）

-- 查询时自动去除尾部空格
SELECT code, LENGTH(code) FROM char_test;
-- 结果：'ABC', 3

VARCHAR 的存储：

-- VARCHAR 需要 1-2 字节存储长度前缀
-- 长度 <= 255 字节：1 字节前缀
-- 长度 > 255 字节：2 字节前缀

CREATE TABLE varchar_test (
    name VARCHAR(100)
);

INSERT INTO varchar_test VALUES ('张三');
-- utf8mb4 下存储：
-- 长度前缀：1 字节（值为 6）
-- 数据：6 字节（'张三' 各占 3 字节）
-- 总计：7 字节

使用场景示例​

CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    
    -- 固定长度：使用 CHAR
    
    -- 手机号：固定11位
    phone CHAR(11),
    
    -- 身份证号：固定18位
    id_card CHAR(18),
    
    -- MD5 哈希：固定32位
    password_hash CHAR(32),
    
    -- 变长：使用 VARCHAR
    
    -- 姓名：变长，一般不超过20字
    name VARCHAR(20),
    
    -- 邮箱：变长
    email VARCHAR(100),
    
    -- 地址：变长
    address VARCHAR(200),
    
    -- 简介：较长文本
    bio VARCHAR(500)
);

VARCHAR 长度选择原则​

-- 原则：够用即可，不要过度预留

-- 不好的设计
name VARCHAR(1000)   -- 太长，影响索引效率

-- 好的设计
name VARCHAR(50)     -- 根据实际需求设置

-- 注意：VARCHAR(N) 中的 N 是字符数，不是字节数
-- VARCHAR(100) 可以存储 100 个汉字（utf8mb4 下最多 400 字节）

TEXT 类型详解​

当需要存储大段文本时，使用 TEXT 类型：

类型最大长度存储适用场景
TINYTEXT255 字节数据 + 1字节长度短文本
TEXT64 KB数据 + 2字节长度文章摘要
MEDIUMTEXT16 MB数据 + 3字节长度文章内容
LONGTEXT4 GB数据 + 4字节长度大文档

TEXT 与 VARCHAR 的关键区别​

-- 1. TEXT 不能有默认值
CREATE TABLE text_test (
    content TEXT DEFAULT ''  -- 错误！
);

-- 2. TEXT 只能使用前缀索引
CREATE TABLE articles (
    id INT PRIMARY KEY,
    content TEXT,
    INDEX idx_content (content(100))  -- 前100字符索引
);

-- 3. TEXT 在排序时只使用前 max_sort_length 字节
SET max_sort_length = 1000;
SELECT * FROM articles ORDER BY content;  -- 只比较前1000字节

-- 4. TEXT 可能存储在行外（溢出页）
-- InnoDB 当行大小超过阈值时，TEXT 值可能存储在单独的溢出页

存储溢出机制​

InnoDB 的行大小限制为 65535 字节。当一行数据超过这个限制时，变长列（如 TEXT）会存储到溢出页：

InnoDB 行存储：
┌─────────────────────────────────────┐
│ 列1 │ 列2 │ TEXT列指针 │ 列4 │ ...  │
└─────────────────────────────────────┘
              │
              ▼
     ┌─────────────────┐
     │   溢出页         │
     │  (TEXT实际数据)  │
     └─────────────────┘

这意味着读取 TEXT 字段可能需要额外的 I/O 操作。

BINARY 与 VARBINARY​

用于存储二进制字符串，适合存储哈希值、加密数据等：

CREATE TABLE binary_test (
    id INT PRIMARY KEY,
    
    -- 存储 MD5 哈希（16字节二进制）
    md5_hash BINARY(16),
    
    -- 存储 UUID（16字节）
    uuid BINARY(16),
    
    -- 变长二进制
    encrypted_data VARBINARY(1000)
);

-- 插入数据
INSERT INTO binary_test (id, md5_hash, uuid)
VALUES (1, UNHEX('d41d8cd98f00b204e9800998ecf8427e'), UUID_TO_BIN(UUID()));

-- 查询时使用 HEX 转换
SELECT id, HEX(md5_hash) AS md5, HEX(uuid) AS uuid FROM binary_test;

BLOB 类型​

BLOB（Binary Large Object）用于存储大二进制数据：

类型最大长度适用场景
TINYBLOB255 字节小二进制数据
BLOB64 KB图片缩略图
MEDIUMBLOB16 MB图片、文档
LONGBLOB4 GB视频、大文件

最佳实践：不建议在数据库中存储大文件！

-- 不推荐的做法
CREATE TABLE files_bad (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    data LONGBLOB  -- 存储整个文件
);

-- 推荐的做法：存储文件路径
CREATE TABLE files_good (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    path VARCHAR(500),      -- 文件存储路径
    size BIGINT UNSIGNED,   -- 文件大小
    mime_type VARCHAR(100), -- MIME类型
    storage_type ENUM('local', 's3', 'oss') DEFAULT 'local'
);

日期时间类型​

正确处理日期时间是数据库设计的重要环节，涉及时区、格式和存储效率等问题。

类型概览​

类型格式范围存储时区处理
DATEYYYY-MM-DD1000-99993字节无
TIMEHH:MM:SS[.ffffff]-838~838小时3-6字节无
DATETIMEYYYY-MM-DD HH:MM:SS1000-99995-8字节不转换
TIMESTAMPYYYY-MM-DD HH:MM:SS1970-20384-7字节自动转换
YEARYYYY1901-21551字节无

DATETIME vs TIMESTAMP：关键区别​

这是开发者最常遇到的问题：

CREATE TABLE time_test (
    id INT PRIMARY KEY,
    
    -- DATETIME：存储原样，不受时区影响
    created_datetime DATETIME,
    
    -- TIMESTAMP：存储为 UTC，查询时转换为当前时区
    created_timestamp TIMESTAMP
);

-- 设置时区
SET time_zone = '+08:00';  -- 北京时间

-- 插入数据
INSERT INTO time_test (id, created_datetime, created_timestamp)
VALUES (1, '2024-01-15 10:00:00', '2024-01-15 10:00:00');

-- 切换时区
SET time_zone = '+00:00';  -- UTC

-- 查询数据
SELECT id, created_datetime, created_timestamp FROM time_test;
-- created_datetime: 2024-01-15 10:00:00 (不变)
-- created_timestamp: 2024-01-15 02:00:00 (转换为UTC)

选择建议​

场景推荐类型原因
用户本地时间（生日）DATE不涉时区
全球统一事件时间TIMESTAMP自动时区转换
日志、创建时间TIMESTAMP自动更新
预约时间（固定时区）DATETIME不受时区影响
历史数据（超过2038年）DATETIMETIMESTAMP有范围限制

自动初始化和更新​

CREATE TABLE auto_time (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    
    -- 创建时自动设置当前时间
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- 创建和更新时自动设置
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- DATETIME 也可以（MySQL 5.6.5+）
    created_datetime DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_datetime DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 插入数据
INSERT INTO auto_time (id, name) VALUES (1, 'Test');
-- created_at 和 updated_at 自动设置为当前时间

-- 更新数据
UPDATE auto_time SET name = 'Updated' WHERE id = 1;
-- updated_at 自动更新

小数秒精度​

MySQL 5.6.4+ 支持时间类型的小数秒精度：

CREATE TABLE precision_time (
    id INT PRIMARY KEY,
    
    -- 指定小数秒精度
    created_at TIMESTAMP(3),   -- 精确到毫秒
    updated_at TIMESTAMP(6),   -- 精确到微秒
    
    -- DATETIME 也支持
    event_time DATETIME(3)
);

-- 存储需求随精度增加
-- 精度 0：0 字节额外存储
-- 精度 1-2：1 字节
-- 精度 3-4：2 字节
-- 精度 5-6：3 字节

时区处理最佳实践​

-- 1. 查看当前时区设置
SELECT @@global.time_zone, @@session.time_zone;

-- 2. 设置全局时区
SET GLOBAL time_zone = '+08:00';

-- 3. 设置会话时区
SET time_zone = '+08:00';

-- 4. 使用命名时区（需要加载时区数据）
SET time_zone = 'Asia/Shanghai';

-- 5. 时区转换函数
SELECT CONVERT_TZ('2024-01-15 10:00:00', '+08:00', '+00:00');
-- 结果：2024-01-15 02:00:00

-- 6. 存储和读取时区的最佳实践
CREATE TABLE events (
    id INT PRIMARY KEY,
    event_time DATETIME,           -- 存储 UTC 时间
    timezone VARCHAR(32),          -- 存储时区信息
    name VARCHAR(100)
);

-- 插入时转换为 UTC
INSERT INTO events (event_time, timezone, name)
VALUES (CONVERT_TZ('2024-01-15 10:00:00', 'Asia/Shanghai', 'UTC'), 
        'Asia/Shanghai', '会议');

-- 读取时转换回本地时间
SELECT 
    CONVERT_TZ(event_time, 'UTC', timezone) AS local_time,
    name
FROM events;

YEAR 类型​

CREATE TABLE year_test (
    id INT PRIMARY KEY,
    year_value YEAR
);

-- 插入年份
INSERT INTO year_test VALUES (1, 2024);

-- 两位数年份转换规则
INSERT INTO year_test VALUES (2, 24);  -- 转换为 2024
INSERT INTO year_test VALUES (3, 99);  -- 转换为 1999
INSERT INTO year_test VALUES (4, 00);  -- 转换为 2000

-- 范围：1901-2155

JSON 类型​

MySQL 5.7.8+ 原生支持 JSON 类型，提供高效的 JSON 存储和查询能力。

JSON 存储原理​

JSON 类型使用二进制格式存储，而非纯文本：

文本 JSON：'{"name": "张三", "age": 25}'
              ↓
二进制格式：
┌────────────────────────────────────┐
│ 类型标识 │ 长度 │ 偏移表 │ 数据...  │
└────────────────────────────────────┘

优势：
1. 快速定位元素（不需要完整解析）
2. 支持索引（通过虚拟列）
3. 自动验证 JSON 格式

基本操作​

CREATE TABLE json_test (
    id INT PRIMARY KEY,
    data JSON,
    config JSON
);

-- 插入 JSON 数据
INSERT INTO json_test (id, data, config) VALUES (
    1,
    '{"name": "张三", "age": 25, "hobbies": ["阅读", "游泳"]}',
    '{"theme": "dark", "language": "zh-CN"}'
);

-- 使用 JSON 函数查询
SELECT 
    data->>'$.name' AS name,           -- 提取文本值
    data->'$.age' AS age,              -- 提取 JSON 值
    data->'$.hobbies[0]' AS hobby      -- 数组元素
FROM json_test;

JSON 路径表达式​

$           根元素
$.name      对象属性
$[0]        数组元素
$.hobbies[*]   所有数组元素
$.data.*    所有子属性
$**.name    递归查找所有 name 属性

常用 JSON 函数​

-- 创建 JSON
SELECT JSON_ARRAY(1, 2, 3);                    -- [1, 2, 3]
SELECT JSON_OBJECT('name', '张三', 'age', 25); -- {"name": "张三", "age": 25}

-- 提取值
SELECT JSON_EXTRACT(data, '$.name');           -- 提取 JSON 值
SELECT JSON_UNQUOTE(JSON_EXTRACT(data, '$.name')); -- 提取文本
SELECT data->>'$.name';                        -- 简写形式

-- 修改值
UPDATE json_test 
SET data = JSON_SET(data, '$.age', 26)         -- 设置值
WHERE id = 1;

UPDATE json_test 
SET data = JSON_ARRAY_APPEND(data, '$.hobbies', '编程')  -- 追加数组元素
WHERE id = 1;

-- 删除值
UPDATE json_test 
SET data = JSON_REMOVE(data, '$.age')
WHERE id = 1;

-- 查询
SELECT JSON_CONTAINS(data, '"张三"', '$.name');  -- 是否包含
SELECT JSON_SEARCH(data, 'one', '张三');         -- 搜索路径
SELECT JSON_TYPE(data);                          -- 类型
SELECT JSON_LENGTH(data->'$.hobbies');           -- 数组长度

JSON 索引​

JSON 列不能直接创建索引，但可以通过虚拟列实现：

CREATE TABLE products (
    id INT PRIMARY KEY,
    attributes JSON
);

-- 创建虚拟列
ALTER TABLE products 
ADD COLUMN brand VARCHAR(50) 
GENERATED ALWAYS AS (attributes->>'$.brand') STORED;

-- 在虚拟列上创建索引
CREATE INDEX idx_brand ON products(brand);

-- 查询可以使用索引
SELECT * FROM products WHERE brand = 'Apple';

JSON 应用场景​

-- 1. 用户配置
CREATE TABLE user_settings (
    user_id INT PRIMARY KEY,
    settings JSON,
    INDEX idx_theme ((settings->>'$.theme'))
);

INSERT INTO user_settings VALUES (
    1,
    '{
        "theme": "dark",
        "language": "zh-CN",
        "notifications": {
            "email": true,
            "push": false
        }
    }'
);

-- 2. 商品属性（灵活扩展）
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(200),
    attributes JSON,  -- 不同商品类型有不同属性
    INDEX idx_attr_brand ((attributes->>'$.brand'))
);

-- 3. 日志记录
CREATE TABLE api_logs (
    id BIGINT PRIMARY KEY,
    request JSON,    -- 请求参数
    response JSON,   -- 响应数据
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

ENUM 与 SET 类型​

ENUM 类型​

ENUM 是枚举类型，只能取预定义的值之一：

CREATE TABLE orders (
    id INT PRIMARY KEY,
    
    -- 状态枚举
    status ENUM('pending', 'paid', 'shipped', 'delivered', 'cancelled') 
        DEFAULT 'pending',
    
    -- 支付方式
    payment_method ENUM('credit_card', 'alipay', 'wechat', 'bank_transfer')
);

-- 插入数据
INSERT INTO orders (id, status, payment_method) 
VALUES (1, 'pending', 'alipay');

-- 无效值会报错（严格模式）或存储为空字符串（非严格模式）
INSERT INTO orders (id, status) VALUES (2, 'invalid');  -- 错误！

-- 查询
SELECT * FROM orders WHERE status = 'pending';

-- 按数值查询（从1开始）
SELECT status, status + 0 AS status_int FROM orders;
-- pending → 1, paid → 2, shipped → 3, ...

-- 查看枚举值列表
SHOW COLUMNS FROM orders LIKE 'status';

ENUM 存储原理​

ENUM 值存储为整数（1, 2, 3, ...），而非字符串：

ENUM('pending', 'paid', 'shipped', 'delivered', 'cancelled')

值          存储整数    存储空间
NULL        NULL       1 字节
''          0          1 字节
'pending'   1          1 字节
'paid'      2          1 字节
...
（超过255个值需要2字节）

ENUM 优缺点​

优点缺点
存储紧凑（1-2字节）添加值需要 ALTER TABLE
数据验证不易查询可选值
可读性好与其他数据库不兼容

SET 类型​

SET 是集合类型，可以存储多个值的组合：

CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    
    -- 标签集合（最多64个成员）
    tags SET('技术', '生活', '旅行', '美食', '编程', '设计')
);

-- 插入多个值
INSERT INTO articles (id, title, tags) 
VALUES (1, 'MySQL教程', '技术,编程');

INSERT INTO articles (id, title, tags) 
VALUES (2, '周末游记', '生活,旅行');

-- 查询包含特定标签的文章
SELECT * FROM articles WHERE FIND_IN_SET('技术', tags);

-- 使用位运算
SELECT * FROM articles WHERE tags & 1;  -- '技术' 是第1位

SET 存储原理​

SET 使用位图存储，每个值对应一个位：

SET('技术', '生活', '旅行', '美食', '编程', '设计')
     ↓       ↓       ↓       ↓       ↓       ↓
     1       2       4       8       16      32

'技术,编程' = 1 + 16 = 17（二进制：010001）

存储空间：
1-8 个成员：1 字节
9-16 个成员：2 字节
...
57-64 个成员：8 字节

数据类型选择指南​

决策流程​

需要存储什么类型的数据？
       │
       ├── 数值
       │      ├── 整数 → 根据范围选择 TINYINT/SMALLINT/INT/BIGINT
       │      ├── 金额 → DECIMAL（必须）
       │      └── 科学计算 → FLOAT/DOUBLE
       │
       ├── 文本
       │      ├── 固定长度 → CHAR
       │      ├── 变长（短）→ VARCHAR
       │      └── 长文本 → TEXT
       │
       ├── 时间
       │      ├── 仅日期 → DATE
       │      ├── 需要时区 → TIMESTAMP
       │      └── 固定时区 → DATETIME
       │
       ├── JSON → JSON 类型
       │
       └── 固定选项 → ENUM

常见场景类型选择​

场景推荐类型示例
主键 IDBIGINT UNSIGNED AUTO_INCREMENTid BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY
金额DECIMAL(10, 2) 或更大price DECIMAL(12, 2)
手机号CHAR(11)phone CHAR(11)
邮箱VARCHAR(100)email VARCHAR(100)
密码哈希CHAR(60) (bcrypt)password_hash CHAR(60)
状态ENUM(...) 或 TINYINTstatus ENUM('active', 'inactive')
创建时间TIMESTAMPcreated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
用户配置JSONsettings JSON
文章内容MEDIUMTEXTcontent MEDIUMTEXT

性能优化建议​

1. 选择最小的数据类型​

-- 不好的设计
CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    age INT,              -- 太大，TINYINT 足够
    status INT,           -- 太大，TINYINT 足够
    name VARCHAR(1000)    -- 太长
);

-- 好的设计
CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,  -- 考虑增长
    age TINYINT UNSIGNED,          -- 0-255 足够
    status TINYINT UNSIGNED,       -- 0-255 足够
    name VARCHAR(50)               -- 根据实际需求
);

2. 使用合适的字符串类型​

-- 固定长度用 CHAR
phone CHAR(11)          -- 手机号
md5 CHAR(32)            -- MD5 哈希

-- 变长用 VARCHAR
name VARCHAR(50)        -- 姓名
email VARCHAR(100)      -- 邮箱

-- 长文本用 TEXT
content TEXT            -- 文章内容

3. 避免使用 NULL​

-- NULL 值影响索引效率，增加存储开销

-- 不好的设计
name VARCHAR(50)        -- 允许 NULL

-- 好的设计
name VARCHAR(50) NOT NULL DEFAULT ''

4. 金额存储的正确方式​

-- 方式一：DECIMAL（推荐）
price DECIMAL(12, 2)    -- 存储元

-- 方式二：整数存储分
price_cents BIGINT UNSIGNED  -- 存储分

-- 错误方式
price FLOAT             -- 精度丢失！
price DOUBLE            -- 精度丢失！

5. 时间戳的最佳实践​

-- 创建时间和更新时间
CREATE TABLE base_table (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    
    -- 使用 TIMESTAMP 自动管理
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 如果需要支持超过 2038 年的日期，使用 DATETIME
CREATE TABLE historical_data (
    id BIGINT UNSIGNED PRIMARY KEY,
    event_date DATETIME,  -- 支持 1000-9999 年
    INDEX idx_date (event_date)
);

常见问题与解决方案​

问题一：浮点数精度丢失​

-- 问题
SELECT 0.1 + 0.2;  -- 可能不是精确的 0.3

-- 原因：二进制无法精确表示某些十进制小数

-- 解决方案：使用 DECIMAL
SELECT CAST(0.1 AS DECIMAL(10,2)) + CAST(0.2 AS DECIMAL(10,2));

问题二：VARCHAR 长度影响性能吗？​

-- VARCHAR(10) 和 VARCHAR(1000) 存储同样的数据，性能基本相同
-- 因为 VARCHAR 只存储实际长度

-- 但索引大小不同：
CREATE TABLE test (
    name1 VARCHAR(10),
    name2 VARCHAR(1000),
    INDEX idx1 (name1),    -- 较小
    INDEX idx2 (name2)     -- 较大
);

-- 建议：根据实际需求设置合理长度

问题三：TIMESTAMP 2038 年问题​

-- TIMESTAMP 范围：1970-01-01 到 2038-01-19
-- 超过这个范围会出错

-- 解决方案
-- 1. 使用 DATETIME（支持到 9999 年）
-- 2. 使用 BIGINT 存储时间戳毫秒

CREATE TABLE future_events (
    id INT PRIMARY KEY,
    event_time DATETIME,           -- 支持更广范围
    timestamp_ms BIGINT UNSIGNED   -- 毫秒时间戳
);

问题四：ENUM vs TINYINT 选择​

-- ENUM 的优点
-- 1. 数据库层面验证
-- 2. 可读性好
status ENUM('pending', 'approved', 'rejected')

-- TINYINT 的优点
-- 1. 不需要 ALTER TABLE 就能添加值
-- 2. 可以在应用层定义常量
status TINYINT UNSIGNED  -- 0=pending, 1=approved, 2=rejected

-- 建议：
-- - 值固定不变 → ENUM
-- - 值可能变化 → TINYINT + 应用层常量

问题五：TEXT 列导致性能问题​

-- 问题：大 TEXT 列影响查询性能

-- 原因：
-- 1. TEXT 可能存储在溢出页，增加 I/O
-- 2. 排序时只使用前 max_sort_length 字节

-- 解决方案：
-- 1. 将大 TEXT 列分离到单独的表
CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    summary VARCHAR(500)
);

CREATE TABLE article_contents (
    article_id INT PRIMARY KEY,
    content MEDIUMTEXT,
    FOREIGN KEY (article_id) REFERENCES articles(id)
);

-- 2. 查询时只选择需要的列
SELECT id, title FROM articles;  -- 不选 content

小结​

本章我们系统学习了 MySQL 数据类型：

  1. 数值类型：整数（TINYINT 到 BIGINT）、浮点数（FLOAT/DOUBLE）、定点数（DECIMAL）
  2. 字符串类型：CHAR、VARCHAR、TEXT 的区别和使用场景
  3. 日期时间类型：DATETIME 和 TIMESTAMP 的关键区别、时区处理
  4. JSON 类型：存储原理、常用函数、索引方法
  5. ENUM 与 SET：枚举和集合类型的使用
  6. 选择原则：根据数据特性、存储效率、查询性能选择合适的类型
  7. 性能优化：最小化存储、避免 NULL、正确处理金额和时间

练习​

  1. 设计一个电商订单表，为每个字段选择合适的数据类型
  2. 比较 VARCHAR(10) 和 CHAR(10) 在存储 'abc' 时的差异
  3. 设计一个支持多时区的事件表
  4. 使用 JSON 类型存储用户配置，并实现按配置项查询
  5. 分析 DECIMAL(10,2) 和 FLOAT 在金额计算上的差异

参考资料​