MySQL 数据类型详解
—数据库专栏—
目录
- 一、引言：为什么数据类型很重要？
- 二、数据类型分类概览
- 三、数值类型详解
- 3.1 整数类型
- 3.2 浮点数与定点数
- 四、字符串类型详解
- 4.1 固定长度与可变长度字符串
- 4.2 文本类型与二进制类型
- 4.3 ENUM 与 SET 类型
- 五、日期与时间类型
- 六、数据类型选择策略
- 6.1 CHAR vs VARCHAR
- 6.2 VARCHAR vs TEXT
- 6.3 数值类型选择建议
- 七、实战示例与常见问题
- 八、总结与参考资料
一、引言：为什么数据类型很重要？
在数据库设计过程中，数据类型的选择直接影响数据的存储效率、查询性能、数据一致性与系统的可扩展性。一个合理的数据类型设计不仅能节省存储空间，还能提升索引效率，降低系统负载。
例如，若将一个只需存储 0/1 状态的字段设置为BIGINT，则会浪费大量存储空间；若将较长的文本内容全部使用VARCHAR存储，则可能导致索引失效或查询性能下降。因此，掌握 MySQL 数据类型的特点与适用场景，是每一位数据库开发者与架构师的基本功。
二、数据类型分类概览
MySQL 支持的数据类型主要分为以下几类：
| 类型分类 | 包含类型示例 | 主要用途 |
|---|---|---|
| 数值类型 | INT,FLOAT,DECIMAL | 存储数字，支持整数、浮点数、定点数 |
| 字符串类型 | CHAR,VARCHAR,TEXT | 存储文本、字符串 |
| 二进制类型 | BINARY,BLOB | 存储二进制数据（如图片、文件） |
| 日期时间类型 | DATE,DATETIME,TIMESTAMP | 存储日期、时间、时间戳 |
| 特殊类型 | ENUM,SET | 存储枚举值或集合值 |
三、数值类型详解
3.1 整数类型
MySQL 提供了多种整数类型，适用于不同范围的整数存储：
| 类型 | 存储大小 | 有符号范围 | 无符号范围 |
|---|---|---|---|
| TINYINT | 1 Byte | -128 ~ 127 | 0 ~ 255 |
| SMALLINT | 2 Bytes | -32768 ~ 32767 | 0 ~ 65535 |
| MEDIUMINT | 3 Bytes | -8388608 ~ 8388607 | 0 ~ 16777215 |
| INT | 4 Bytes | -2147483648 ~ 2147483647 | 0 ~ 4294967295 |
| BIGINT | 8 Bytes | -2^63 ~ 2^63-1 | 0 ~ 2^64-1 |
使用建议：
- 尽量选择能满足需求的最小类型，节省存储空间。
- 对于状态标志（如性别、是否删除），可使用
TINYINT或BIT。 - 主键常用
INT或BIGINT，并设置为自增。
3.2 浮点数与定点数
| 类型 | 存储大小 | 精度说明 | 适用场景 |
|---|---|---|---|
| FLOAT | 4 Bytes | 单精度，约7位有效数字 | 对精度要求不高的科学计算 |
| DOUBLE | 8 Bytes | 双精度，约15位有效数字 | 普通浮点计算 |
| DECIMAL(M,D) | 动态 | 精确存储，M为总位数，D为小数位数 | 财务、金额等精确计算 |
示例代码：
-- 创建表，使用 DECIMAL 存储金额CREATETABLEorders(idINTPRIMARYKEYAUTO_INCREMENT,amountDECIMAL(10,2)NOTNULLCOMMENT'订单金额，精确到分');四、字符串类型详解
4.1 固定长度与可变长度字符串
| 类型 | 特点 | 存储方式 | 适用场景 |
|---|---|---|---|
| CHAR(M) | 固定长度，M为字符数 | 始终占用 M 个字符的空间 | 长度固定的数据（如身份证、邮编） |
| VARCHAR(M) | 可变长度，M为最大字符数 | 实际使用长度 + 1~2 字节记录长度 | 长度变化的数据（如姓名、地址） |
对比示例：
CREATETABLEuser_info(idINTPRIMARYKEY,id_cardCHAR(18)COMMENT'身份证号',nameVARCHAR(50)COMMENT'姓名');4.2 文本类型与二进制类型
| 类型 | 最大长度 | 存储方式 | 适用场景 |
|---|---|---|---|
| TEXT | 65535 字符 | 存储在溢出页 | 文章内容、日志文本 |
| BLOB | 65535 字节 | 存储在溢出页 | 图片、文件等二进制数据 |
| MEDIUMTEXT | 16777215 字符 | 溢出页存储 | 较大文本内容 |
| LONGBLOB | 4GB | 溢出页存储 | 大型二进制对象 |
注意：TEXT和BLOB类型无法直接创建普通索引，建议使用FULLTEXT索引或前缀索引。
4.3 ENUM 与 SET 类型
- ENUM：从预定义列表中选择一个值
- SET：从预定义列表中选择多个值
示例：
CREATETABLEarticles(idINT,statusENUM('draft','published','archived')DEFAULT'draft',tagsSET('tech','life','travel'));五、日期与时间类型
| 类型 | 存储大小 | 格式 | 范围 | 适用场景 |
|---|---|---|---|---|
| DATE | 3 Bytes | YYYY-MM-DD | 1000-01-01 ~ 9999-12-31 | 生日、日期记录 |
| TIME | 3 Bytes | HH:MM:SS | -838:59:59 ~ 838:59:59 | 时间间隔 |
| DATETIME | 8 Bytes | YYYY-MM-DD HH:MM:SS | 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 | 记录创建时间、更新时间 |
| TIMESTAMP | 4 Bytes | YYYY-MM-DD HH:MM:SS | 1970-01-01 00:00:01 ~ 2038-01-19 03:14:07 | 自动记录时间戳，支持时区 |
实战示例：
CREATETABLElogs(idINTAUTO_INCREMENTPRIMARYKEY,contentTEXT,created_atTIMESTAMPDEFAULTCURRENT_TIMESTAMP,updated_atTIMESTAMPDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP);六、数据类型选择策略
6.1 CHAR vs VARCHAR
| 对比维度 | CHAR | VARCHAR |
|---|---|---|
| 存储方式 | 固定长度，补空格 | 可变长度，记录实际长度 |
| 存储效率 | 浪费空间（固定分配） | 节省空间（按需分配） |
| 查询性能 | 高（长度固定，索引效率高） | 较低（长度变化，索引效率低） |
| 适用场景 | 身份证、MD5、邮编 | 姓名、地址、备注 |
6.2 VARCHAR vs TEXT
| 对比维度 | VARCHAR | TEXT |
|---|---|---|
| 最大长度 | 65535 字节 | 65535 字符（可自动升级为更大类型） |
| 存储位置 | 行内（<768B）或溢出页 | 始终溢出页 |
| 索引支持 | 支持普通索引 | 仅支持 FULLTEXT 索引 |
| 适用场景 | 短文本、频繁查询字段 | 长文本、不常查询内容 |
6.3 数值类型选择建议
- 金额、价格等精确计算 →
DECIMAL - 计数器、状态码 →
INT或TINYINT - 科学计算、非精确数值 →
FLOAT/DOUBLE
七、实战示例与常见问题
示例1：创建用户表
CREATETABLEusers(idBIGINTUNSIGNEDAUTO_INCREMENTPRIMARYKEY,usernameVARCHAR(50)UNIQUENOTNULL,emailVARCHAR(100)UNIQUENOTNULL,password_hashCHAR(64)COMMENT'SHA-256哈希',genderENUM('male','female','other')DEFAULT'other',birth_dateDATE,created_atTIMESTAMPDEFAULTCURRENT_TIMESTAMP)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;示例2：数据插入与查询
-- 插入数据INSERTINTOusers(username,email,gender,birth_date)VALUES('zhangsan','zhangsan@example.com','male','1990-05-20');-- 查询数据SELECT*FROMusersWHEREgender='male'ANDbirth_date>'1990-01-01';常见问题：
为什么 VARCHAR 长度不能超过 65535？
- 因为 MySQL 中行的最大长度为 65535 字节，且所有列共享该限制。
TIMESTAMP 与 DATETIME 如何选择？
- 如果需要自动更新时间、支持时区转换，选
TIMESTAMP； - 如果需要存储大范围日期时间，选
DATETIME。
- 如果需要自动更新时间、支持时区转换，选
ENUM 和 SET 是否推荐使用？
- 在固定值较少、不常变更的情况下可以使用，否则建议使用关联表。
八、总结
总结
数据类型选择是数据库设计的基础环节，合理的选择能：
- ✅ 提升存储效率
- ✅ 优化查询性能
- ✅ 增强数据一致性
- ✅ 支持系统扩展