MySQL建表时如何正确选择数据类型？实战经验总结_创建表如何考虑数据类型-优快云博客

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

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

1. 四大整型对比

类型	字节	有符号范围	无符号范围
TINYINT	1	-128 ~ 127	0 ~ 255
SMALLINT	2	-32768 ~ 32767	0 ~ 65535
INT	4	-2147483648 ~ 2147483647	0 ~ 4294967295
BIGINT	8	-2^63 ~ 2^63-1	0 ~ 2^64-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

类型	特点	适用场景
VARCHAR	变长（+1-2字节长度头）	用户名、地址等变长字段
CHAR	定长	固定长度代码（如ISO国家代码）

存储示例：

# 正确使用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. 主要时间类型

类型	格式	范围	字节	时区敏感
DATETIME	YYYY-MM-DD HH:MM:SS	1000-01-01 ~ 9999-12-31	8	否
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1970-01-01 ~ 2038-01-19	4	是

选择策略：

日志记录：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';

注意事项：