04.MySQL数据类型详解

MySQL数据类型详解

文章目录

MySQL数据类型
数据类型分类
数值类型
- tinyint类型
- bit类型
- float类型
- decimal类型
字符串类型
- char类型
- varchar类型
- char和varchar比较
时间日期类型
enum和set类型
数据类型选择的进阶技巧
常见误区与解决方案
性能优化与最佳实践

MySQL数据类型

数据类型的作用

数据类型不仅是存储数据的容器，更是数据库设计的基石。它决定了三个核心要素：

存储空间：例如，存一个整数和一篇长文显然需要不同的空间分配策略。
二进制解析方式：同一串二进制数据，用INT解读是数字，用CHAR解读可能变成乱码。
取值范围限制：年龄不可能是负数，性别只能是有限选项，这些都需要数据类型来规范。

此外，数据类型还影响索引效率、查询优化器的选择，甚至影响数据库的扩展性和维护成本。例如，错误地使用VARCHAR(255)存储固定长度的MD5值会导致空间浪费和查询效率下降。

数据类型分类

MySQL的数据类型大致可分为四类，以下是详细分类表：

分类	数据类型	说明
数值类型	BIT(M)	位类型，M默认1，范围1-64位
	BOOL	布尔值（实际是TINYINT(1)）
	TINYINT/SMALLINT/MEDIUMINT/INT/BIGINT	整型家族，字节数从1到8递增
	FLOAT/DOUBLE/DECIMAL	浮点数和精确小数
字符串类型	CHAR(L)	固定长度字符串（最大255）
	VARCHAR(L)	可变长度字符串（最大65535字节）
	BLOB/TEXT	大文本/二进制数据
日期时间类型	DATE/DATETIME	日期格式YYYY-MM-DD，时间戳
	TIMESTAMP	自动更新的时间戳
集合类型	ENUM	枚举（单选）
	SET	集合（多选）

冷知识：MySQL没有独立的布尔类型，用TINYINT(1)代替，0代表FALSE，1代表TRUE。但在某些ORM框架中，会自动将TINYINT(1)映射为布尔值。

数值类型

tinyint类型

有符号 vs 无符号

有符号：范围-128~127（占1字节）
无符号：范围0~255（同样占1字节）

实战案例：
假设设计一个用户积分表：

CREATE TABLE user_points (user_id INT,points TINYINT UNSIGNED
);

如果积分上限为200，用TINYINT UNSIGNED足够；但若未来需要支持更高积分（如500），则必须改为SMALLINT UNSIGNED，否则插入500会报错。

为何慎用无符号？

无符号类型看似能节省空间，但可能埋下隐患：

扩展性差：如年龄字段若用TINYINT UNSIGNED（0-255），当遇到异常值（如输入300）会直接报错，而有符号类型可临时存储负数用于标记异常。
兼容性问题：某些编程语言或框架对无符号类型支持不佳，可能引发转换错误。

bit类型

位类型的高级应用

BIT类型适合存储开关状态或权限位掩码。例如，一个用户权限字段：

CREATE TABLE user_permissions (user_id INT,perms BIT(8) -- 每位代表一种权限
);

插入权限：

INSERT INTO user_permissions VALUES (1, b'00000011'); -- 同时有第1和第2位权限

通过位运算查询权限：

SELECT * FROM user_permissions WHERE perms & b'00000001'; -- 查找有第一位权限的用户

显示问题与解决方案

BIT类型显示时按ASCII码转换可能导致混乱。例如：

INSERT INTO user_permissions VALUES (2, 10); -- 10对应ASCII换行符

查询结果可能显示为空白或特殊字符。解决方案：

应用层处理：将BIT转换为整数或自定义字符串映射。
使用INT代替BIT：对于不超过32位的权限，直接用INT存储更直观。

float类型

精度陷阱与四舍五入

FLOAT(M,D)的M是总位数，D是小数位数。例如FLOAT(4,2)存储范围是-99.99到99.99，但实际可插入范围是-99.994到99.994，超出时会四舍五入或报错。

CREATE TABLE measurements (val FLOAT(4,2)
);
INSERT INTO measurements VALUES (99.994); -- 存储为99.99
INSERT INTO measurements VALUES (99.995); -- 存储为100.0，触发报错

何时选择FLOAT vs DECIMAL？

FLOAT：适合科学计算，允许一定误差（如传感器数据）。
DECIMAL：金融场景必须使用，如存储账户余额，避免浮点误差。

decimal类型

精确计算的王者

DECIMAL的存储机制使其成为金融系统的首选。例如：

CREATE TABLE accounts (balance DECIMAL(10,2)
);

存储100.01时，DECIMAL确保精确到分，而FLOAT可能存储为100.009999。

存储开销对比：
DECIMAL每4字节存9个数字，小数点单独占1字节。例如DECIMAL(10,2)占用5字节（9个数字+1字节小数点），而FLOAT固定占4字节。

字符串类型

char类型

定长存储的适用场景

CHAR(L)适用于长度固定的字符串，如身份证号（18位）、手机号（11位）。例如：

CREATE TABLE users (id_card CHAR(18)
);

插入不足18位时会自动补空格，查询时尾部空格被自动去除。

性能优势：

定长存储便于快速定位，适合频繁更新的字段。
作为主键时，CHAR比VARCHAR更高效（如UUID）。

varchar类型

变长存储的灵活性

VARCHAR(L)适合长度波动大的字段，如用户名、地址。例如：

CREATE TABLE addresses (street VARCHAR(100)
);

存储"Main St"仅占用7字节（数据+1字节长度标识），而CHAR(100)会占用100字节。

编码对最大长度的影响：

UTF8MB4下，VARCHAR(21844) ≈ 65532字节（21844 × 3字节/字符 + 2字节长度标识）
GBK下，VARCHAR(32766) ≈ 65532字节（32766 × 2字节/字符 + 2字节长度标识）

最佳实践：

定义表时显式指定字符集：CREATE TABLE example (...) CHARSET=utf8mb4;
避免过度使用VARCHAR(255)：根据实际数据长度选择合适值，节省空间并提高缓存效率。

char和varchar比较

特性	CHAR	VARCHAR
空间占用	固定L字符	实际长度+1~2字节
速度	快（定长）	稍慢（需读长度）
适用场景	身份证号、手机号	名字、地址等变长字段

选择建议：

长度固定的字段（如MD5值）用CHAR
长度波动大的字段（如文章内容）用VARCHAR

扩展案例：
存储IP地址时，CHAR(15)（如"192.168.1.1"）比VARCHAR更高效，因为IPv4地址固定为15字符以内。

时间日期类型

三大时间类型对比

类型	格式	占用空间	特点
DATE	YYYY-MM-DD	3字节	只存日期
DATETIME	YYYY-MM-DD HH:MM:SS	8字节	范围1000-9999年
TIMESTAMP	YYYY-MM-DD HH:MM:SS	4字节	自动更新，默认当前时间

实战案例：
设计评论表时，使用TIMESTAMP记录发布时间：

CREATE TABLE comments (id INT,content TEXT,create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

插入数据时无需指定create_time，自动填充当前时间。更新记录时，可设置TIMESTAMP自动更新：

ALTER TABLE comments ADD update_time TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

时区问题：

DATETIME不存储时区信息，显示依赖客户端设置。
TIMESTAMP存储UTC时间，查询时自动转换为当前时区。

enum和set类型

枚举类型（ENUM）

单选场景优化

ENUM适合选项固定的单选字段，如订单状态：

CREATE TABLE orders (status ENUM('pending', 'processing', 'shipped', 'canceled')
);

插入非枚举值会报错，确保数据一致性。

内部存储机制：
ENUM存储为数字索引（1-based），如ENUM('a','b','c')中，a=1，b=2，c=3。可通过数字访问：

SELECT * FROM orders WHERE status = 3; -- 查找所有已取消订单

但不推荐此方式，可读性差。

集合类型（SET）

多选场景优化

SET适合多选字段，如用户兴趣标签：

CREATE TABLE users (interests SET('sports', 'music', 'reading')
);

插入多选值：

INSERT INTO users VALUES ('sports,music');

位运算原理：
SET用位图存储，每个选项对应一个二进制位：

sports = 1 (0b0001)
music = 2 (0b0010)
reading = 4 (0b0100)

查询包含"music"的用户：

SELECT * FROM users WHERE interests & 2;

局限性：

最多64个选项。
修改枚举/集合列表需ALTER TABLE，不适合动态选项。

数据类型选择的技巧

1. 空间与性能的权衡

数值类型：优先使用最小能满足需求的类型。例如，年龄字段用TINYINT而非INT。
字符串类型：避免滥用VARCHAR(255)，根据实际数据长度选择，减少内存占用。
日期类型：若只需日期（如生日），用DATE而非DATETIME，节省5字节存储。

2. 金融场景的必杀技

涉及金额字段必须使用DECIMAL，避免FLOAT/DOUBLE的精度问题。例如：

CREATE TABLE transactions (amount DECIMAL(15,4) -- 精确到分，保留4位小数
);

3. JSON类型的应用

MySQL 5.7+支持JSON类型，适合存储半结构化数据：

CREATE TABLE settings (user_id INT,preferences JSON
);
INSERT INTO settings VALUES (1, '{"theme": "dark", "notifications": true}');

查询JSON字段：

SELECT * FROM settings WHERE JSON_EXTRACT(preferences, '$.theme') = 'dark';

常见误区与解决方案

误区1：盲目使用INT存储一切数值

问题：用INT存储IP地址（如192.168.1.1转为3232235779），查询时需转换回字符串。
解决方案：使用INET_ATON()和INET_NTOA()函数，或直接用CHAR(15)存储。

误区2：过度依赖ENUM/SET

问题：ENUM选项频繁变动时需频繁执行ALTER TABLE。
解决方案：用外键关联独立的状态表，如：

CREATE TABLE order_statuses (id TINYINT PRIMARY KEY,name VARCHAR(20)
);
CREATE TABLE orders (status_id TINYINT,FOREIGN KEY (status_id) REFERENCES order_statuses(id)
);