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

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）

注意事项：

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

浮点数类型

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

语法：

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

注意事项：

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

   -- 错误示例
   FLOAT: 0.1 + 0.2 = 0.30000001192092896
   
   -- 正确方案
   DECIMAL(10,2): 0.1 + 0.2 = 0.30
   

2. DECIMAL(5,2) 范围：-999.99 ~ 999.99
3. 存储空间计算：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	年份值

注意事项：

1. TIMESTAMP 的 2038年问题：最大到 2038-01-19，新系统建议用 DATETIME
2. 时区处理：
   • TIMESTAMP 存 UTC 时间，检索时转当前时区
   • DATETIME 按字面值存储，不转换时区
3. 默认值和自动更新：

   -- 自动设置创建时间
   created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
   
   -- 自动更新修改时间
   updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
   

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

---

三、字符串类型

定长字符串

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

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

变长字符串

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

注意事项：

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

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

文本类型

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

注意事项：

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

   • 64KB：MEDIUMTEXT/LONGTEXT

---

四、二进制数据类型

二进制字符串

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

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

二进制大对象（BLOB）

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

注意事项：

1. 与 TEXT 类型区别：
   • BLOB 存储二进制数据（如图片、PDF）
   • TEXT 存储字符数据
2. 性能影响：大 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);

注意事项：

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

---

数据类型选择最佳实践

1. 精确数值计算
   ✅ 用 DECIMAL
   ❌ 避免 FLOAT/DOUBLE

2. 时间存储
   ✅ 用 DATETIME（无2038限制）
   ❌ 避免 TIMESTAMP 长期存储

3. 字符串优化
   ✅ 定长用 CHAR（如 UUID、MD5）
   ✅ 变长用 VARCHAR（<255字符）
   ✅ 大文本用 TEXT

4. 存储空间敏感场景

   • 小整数：TINYINT UNSIGNED（1字节）
   • 状态值：ENUM（1-2字节）
   • 日期：DATE（3字节）

5. 性能关键点：

   • 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));

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