MySQL 数据类型详解

想不明白的过度思考者

发布于 2025-12-21 09:13:51

360

文章被收录于专栏：OverThinker的专栏OverThinker的专栏

—数据库专栏—

一、引言：为什么数据类型很重要？

在数据库设计过程中，数据类型的选择直接影响数据的存储效率、查询性能、数据一致性与系统的可扩展性。一个合理的数据类型设计不仅能节省存储空间，还能提升索引效率，降低系统负载。

例如，若将一个只需存储 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 存储金额
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    amount DECIMAL(10, 2) NOT NULL COMMENT '订单金额，精确到分'
);

四、字符串类型详解

4.1 固定长度与可变长度字符串

类型	特点	存储方式	适用场景
CHAR(M)	固定长度，M为字符数	始终占用 M 个字符的空间	长度固定的数据（如身份证、邮编）
VARCHAR(M)	可变长度，M为最大字符数	实际使用长度 + 1~2 字节记录长度	长度变化的数据（如姓名、地址）

对比示例：

CREATE TABLE user_info (
    id INT PRIMARY KEY,
    id_card CHAR(18) COMMENT '身份证号',
    name VARCHAR(50) COMMENT '姓名'
);

4.2 文本类型与二进制类型

类型	最大长度	存储方式	适用场景
TEXT	65535 字符	存储在溢出页	文章内容、日志文本
BLOB	65535 字节	存储在溢出页	图片、文件等二进制数据
MEDIUMTEXT	16777215 字符	溢出页存储	较大文本内容
LONGBLOB	4GB	溢出页存储	大型二进制对象

注意：TEXT 和 BLOB 类型无法直接创建普通索引，建议使用 FULLTEXT 索引或前缀索引。

4.3 ENUM 与 SET 类型

ENUM：从预定义列表中选择一个值
SET：从预定义列表中选择多个值

示例：

CREATE TABLE articles (
    id INT,
    status ENUM('draft', 'published', 'archived') DEFAULT 'draft',
    tags SET('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	自动记录时间戳，支持时区

实战示例：

CREATE TABLE logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_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：创建用户表

CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash CHAR(64) COMMENT 'SHA-256哈希',
    gender ENUM('male', 'female', 'other') DEFAULT 'other',
    birth_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

示例2：数据插入与查询

-- 插入数据
INSERT INTO users (username, email, gender, birth_date) 
VALUES ('zhangsan', 'zhangsan@example.com', 'male', '1990-05-20');

-- 查询数据
SELECT * FROM users WHERE gender = 'male' AND birth_date > '1990-01-01';

常见问题：

为什么 VARCHAR 长度不能超过 65535？
- 因为 MySQL 中行的最大长度为 65535 字节，且所有列共享该限制。
TIMESTAMP 与 DATETIME 如何选择？
- 如果需要自动更新时间、支持时区转换，选 TIMESTAMP；
- 如果需要存储大范围日期时间，选 DATETIME。
ENUM 和 SET 是否推荐使用？
- 在固定值较少、不常变更的情况下可以使用，否则建议使用关联表。