MySQL建表时如何正确选择数据类型？实战经验总结

在数据库表结构设计中，数据类型的选择直接影响存储效率、查询性能和数据准确性。本文结合真实开发场景，深入解析常见数据类型的选型原则，帮你避开新手常踩的坑。

一、整数类型：根据业务范围精确选型

1. 四大整型对比

实战建议：

• 用户年龄：TINYINT UNSIGNED（0-255足够）

• 订单数量：INT UNSIGNED（单表42亿条足够）

• 分布式ID：BIGINT UNSIGNED（使用雪花算法时必选）

错误案例：

# 错误！用户ID可能超过INT上限
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY
);

二、浮点与精确计算：金融字段必须警惕

1. 浮点类型

• FLOAT：4字节，约7位精度

• DOUBLE：8字节，约15位精度

2. 精确计算

• DECIMAL(M,D)：M总位数，D小数位数

  • DECIMAL(10,2)：共10位，2位小数

使用场景：

# 正确！金额必须使用DECIMAL
CREATE TABLE orders (
    amount DECIMAL(12,2) NOT NULL
);

# 错误！浮点导致精度丢失
CREATE TABLE payments (
    amount FLOAT NOT NULL  
);

三、字符串类型：定长与变长的博弈

1. VARCHAR vs CHAR

存储示例：

# 正确使用CHAR
CREATE TABLE countries (
    code CHAR(2) NOT NULL  # ISO两位国家代码
);

# 正确使用VARCHAR
CREATE TABLE products (
    sku VARCHAR(20) NOT NULL  # 变长商品编码
);

2. TEXT家族的陷阱

• TEXT：最大64KB

• MEDIUMTEXT：16MB

• LONGTEXT：4GB

使用原则：

• 当长度可能超过VARCHAR(65535)时再使用TEXT

• 避免在WHERE条件中使用TEXT字段

四、时间类型：时区问题关键所在

1. 主要时间类型

选择策略：

• 日志记录：TIMESTAMP（自动转换UTC）

• 生日字段：DATE（无需时间部分）

• 跨时区系统：TIMESTAMP + 存储UTC时间

建表示例：

CREATE TABLE events (
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expire_date DATE NOT NULL
);

五、ENUM与SET：谨慎使用的双刃剑

1. ENUM类型

• 值必须是预定义列表中的一个

• 存储为整数（节省空间）

适用场景：

# 有限状态字段
CREATE TABLE orders (
    status ENUM('pending', 'paid', 'shipped') NOT NULL
);

2. SET类型

• 可保存多个值的组合

• 最大64个成员

使用案例：

# 用户权限存储
CREATE TABLE users (
    permissions SET('read', 'write', 'delete') NOT NULL
);

避坑指南：

• 当可能新增选项时，避免使用ENUM/SET

• 超过20个选项时改用关联表

六、JSON类型：灵活存储的利器（MySQL 5.7+）

使用场景：

• 动态字段（如商品属性）

• 日志详情等非结构数据

示例：

CREATE TABLE product_properties (
    id BIGINT,
    attributes JSON NOT NULL,
    INDEX ((CAST(attributes->'$.color' AS CHAR(20))))
);

# 查询JSON字段
SELECT * FROM product_properties 
WHERE attributes->'$.color' = 'red';

注意事项：

• JSON字段不支持默认值

• 更新整个JSON列会导致重写整个文档

七、终极选型原则

1. 最小够用原则：能用TINYINT就不用SMALLINT

2. 未来扩展性：预计用户量暴增？提前用BIGINT

3. 业务匹配度：金额必须DECIMAL，时间戳选TIMESTAMP

4. 性能权衡：VARCHAR(255)与VARCHAR(1000)的存储开销相同

5. 编码影响：UTF8MB4字符集下，VARCHAR(255)最多占255*4=1020字节

避坑检查清单：

• 主键是否用了足够大的类型？

• 金额字段是否使用DECIMAL？

• 时间字段是否考虑了时区？

• VARCHAR长度是否合理预估？

• 是否滥用TEXT类型？

通过合理选择数据类型，可使表结构更健壮，存储效率提升50%以上。你在建表时最常纠结哪种类型的选择？欢迎在评论区交流实战经验！