MySQL 数据类型全面指南：详细说明与关键注意事项_mysql的数据类型和规范-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_51288065/article/details/148999931

MySQL 数据类型全面指南：详细说明与关键注意事项

MySQL 提供了丰富的数据类型，合理选择对数据库性能、存储效率和数据准确性至关重要。以下是所有数据类型的详细说明及使用注意事项：

一、数值类型

整数类型

类型	字节	有符号范围	无符号范围	说明
`TINYINT`	1	-128 ~ 127	0 ~ 255	小整数（如状态值）
`SMALLINT`	2	-32,768 ~ 32,767	0 ~ 65,535	较小整数
`MEDIUMINT`	3	-8M ~ 8M-1	0 ~ 16M-1	中等整数
`INT`	4	-2.1B ~ 2.1B-1	0 ~ 4.2B-1	标准整数（最常用）
`BIGINT`	8	-9.2E18 ~ 9.2E18-1	0 ~ 1.8E19-1	大整数（如主键ID）

注意事项：

优先选择能满足需求的最小类型（TINYINT > SMALLINT > INT > BIGINT）
无符号整数用 UNSIGNED 关键字：INT UNSIGNED
ZEROFILL 自动添加 UNSIGNED 并用0填充：INT(5) ZEROFILL
显示宽度(如INT(11))仅影响显示，不影响存储大小

浮点数类型

类型	字节	说明
`FLOAT`	4	单精度浮点数，约7位精度
`DOUBLE`	8	双精度浮点数，约15位精度
`DECIMAL`	变长	精确小数（财务计算首选）

语法：

DECIMAL(M, D)  -- M=总位数(1-65), D=小数位数(0-30)

注意事项：

FLOAT/DOUBLE 有精度损失风险，财务计算必须用 DECIMAL

-- 错误示例
FLOAT: 0.1 + 0.2 = 0.30000001192092896

-- 正确方案
DECIMAL(10,2): 0.1 + 0.2 = 0.30

DECIMAL(5,2) 范围：-999.99 ~ 999.99
存储空间计算：CEILING(M/9)*4 字节（如 DECIMAL(20,6) 占9字节）

二、日期时间类型

类型	格式	范围	字节	说明
`DATE`	‘YYYY-MM-DD’	‘1000-01-01’ ~ ‘9999-12-31’	3	日期值
`TIME`	‘HH:MM:SS[.fraction]’	‘-838:59:59’ ~ ‘838:59:59’	3	时间值（可含毫秒）
`DATETIME`	‘YYYY-MM-DD HH:MM:SS’	‘1000-01-01 00:00:00’ ~ ‘9999-12-31 23:59:59’	5-8	日期+时间（推荐使用）
`TIMESTAMP`	‘YYYY-MM-DD HH:MM:SS’	‘1970-01-01 00:00:01’ UTC ~ ‘2038-01-19 03:14:07’ UTC	4	时间戳（自动转换时区）
`YEAR`	YYYY	1901 ~ 2155	1	年份值

注意事项：

TIMESTAMP 的 2038年问题：最大到 2038-01-19，新系统建议用 DATETIME
时区处理：
- TIMESTAMP 存 UTC 时间，检索时转当前时区
- DATETIME 按字面值存储，不转换时区

默认值和自动更新：

-- 自动设置创建时间
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

-- 自动更新修改时间
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

存储空间优化：
- 只需要日期用 DATE (3字节)
- 只需要时间用 TIME (3字节)

三、字符串类型

定长字符串

类型	最大长度	特点
`CHAR(n)`	255字符	固定长度，空格填充，检索快

适用场景：固定长度数据（MD5、国家代码、邮编）

变长字符串

类型	最大长度	特点
`VARCHAR(n)`	65,535字节	按需存储，节省空间

注意事项：

n 表示字符数而非字节数（UTF8MB4 中 1字符=4字节）
实际存储空间 = 字符数 × 字符集字节 + 长度前缀(1-2字节)

最大长度限制：

-- UTF8MB4下实际最大字符数
65,535 / 4 ≈ 16,383 字符

文本类型

类型	最大长度	特点
`TINYTEXT`	255字节	短文本
`TEXT`	64KB (65,535字节)	标准文本（文章内容）
`MEDIUMTEXT`	16MB	较大文本（书籍章节）
`LONGTEXT`	4GB	超大文本（整个文档）

注意事项：

与 VARCHAR 区别：
- TEXT 类型不能有默认值
- 排序使用磁盘临时表，VARCHAR 优先用内存
使用建议：
- < 255字符：VARCHAR
- 255字符~64KB：TEXT
- 64KB：MEDIUMTEXT/LONGTEXT

四、二进制数据类型

二进制字符串

类型	说明
`BINARY(n)`	定长二进制（最大255字节）
`VARBINARY(n)`	变长二进制（最大65,535字节）

适用场景：加密数据、哈希值

二进制大对象（BLOB）

类型	最大长度	说明
`TINYBLOB`	255字节	小二进制对象
`BLOB`	64KB	标准二进制对象
`MEDIUMBLOB`	16MB	中等二进制对象
`LONGBLOB`	4GB	超大二进制对象

注意事项：

与 TEXT 类型区别：
- BLOB 存储二进制数据（如图片、PDF）
- TEXT 存储字符数据
性能影响：大 BLOB 会显著增加 I/O 负载

五、枚举与集合类型

枚举类型 (ENUM)

gender ENUM('Male', 'Female', 'Other')

特点：单选值，内部存储为整数（1-2字节）
注意事项：
- 避免超过 65,535 个选项
- 插入非列表值会报错（严格模式）或存空字符串

集合类型 (SET)

permissions SET('Read', 'Write', 'Delete', 'Admin')

特点：多选值，按位存储（1-8字节）
注意事项：
- 最大64个选项
- 查询用 FIND_IN_SET()：WHERE FIND_IN_SET('Write', permissions)

六、JSON 类型 (MySQL 5.7+)

user_profile JSON

操作示例：

-- 插入
INSERT INTO users VALUES ('{"name": "John", "age": 30}');

-- 查询
SELECT user_profile->>"$.name" FROM users;

-- 更新
UPDATE users SET user_profile = JSON_SET(user_profile, '$.age', 31);

注意事项：

优势：
- 自动验证JSON格式
- 高效读取（无需解析整个文档）
限制：
- 最大大小同 LONGTEXT (4GB)
- 不支持直接索引（需生成列+索引）

数据类型选择最佳实践

精确数值计算
✅ 用 DECIMAL
❌ 避免 FLOAT/DOUBLE
时间存储
✅ 用 DATETIME（无2038限制）
❌ 避免 TIMESTAMP 长期存储
字符串优化
✅ 定长用 CHAR（如 UUID、MD5）
✅ 变长用 VARCHAR（<255字符）
✅ 大文本用 TEXT
存储空间敏感场景
- 小整数：TINYINT UNSIGNED（1字节）
- 状态值：ENUM（1-2字节）
- 日期：DATE（3字节）
性能关键点：
- CHAR vs VARCHAR：定长字段 CHAR 检索更快
- TEXT/BLOB：避免 SELECT *，单独存储大字段
- 整型索引比字符型快

常见错误案例

错误1：用 VARCHAR 存数字

-- 错误：数字比较需隐式转换
SELECT * FROM products WHERE product_id = 100; 

-- 优化：改为 INT
ALTER TABLE products MODIFY product_id INT;

错误2：日期范围查询

-- 错误：字符串比较低效
SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';

-- 优化：用日期函数
SELECT * FROM orders 
WHERE order_date >= '2023-01-01' 
  AND order_date < '2023-02-01';

错误3：ENUM 滥用

-- 错误：选项过多
color ENUM('red','green','blue', ... 100+ colors);

-- 优化：改用关联表
CREATE TABLE colors (id TINYINT, name VARCHAR(20));

合理选择数据类型是数据库优化的基础，需结合业务需求、存储成本和性能要求综合决策。