MySQL 数据类型核心指南:选型、实战与避坑
MySQL 数据类型涵盖数值、字符串、日期等五大类。选型需关注范围、精度及存储效率。整数类型避免无符号陷阱,小数金额必用 DECIMAL。CHAR 定长 VARCHAR 变长,TIMESTAMP 有时区限制。合理选型可提升性能并避免数据溢出。

MySQL 数据类型涵盖数值、字符串、日期等五大类。选型需关注范围、精度及存储效率。整数类型避免无符号陷阱,小数金额必用 DECIMAL。CHAR 定长 VARCHAR 变长,TIMESTAMP 有时区限制。合理选型可提升性能并避免数据溢出。

在 MySQL 数据库设计中,数据类型的选择直接影响存储效率、查询性能和数据准确性。选对数据类型能避免存储空间浪费、数据溢出、精度丢失等问题,而选错则可能导致系统隐患(如用 INT 存储手机号导致截断)。本文将全面拆解 MySQL 核心数据类型,结合实战案例讲解选型技巧。
MySQL 的数据类型丰富,按功能可分为五大类,覆盖数值、字符串、日期、特殊类型等场景:
| 分类 | 核心类型 | 适用场景 |
|---|---|---|
| 数值类型 | BIT、TINYINT、INT、BIGINT、FLOAT、DECIMAL | 存储数字(年龄、金额、计数等) |
| 字符串类型 | CHAR、VARCHAR、TEXT、BLOB | 存储文本(姓名、地址、大文本、二进制数据) |
| 日期时间类型 | DATE、DATETIME、TIMESTAMP | 存储时间(生日、创建时间、时间戳) |
| 特殊字符串 | ENUM(枚举)、SET(集合) | 固定选项(性别、爱好、状态等) |
| 二进制类型 | BLOB | 存储图片、文件等二进制数据 |

数值类型是最常用的类型,核心关注范围和精度,避免数据溢出或精度丢失。
整数类型按占用字节和范围分为 5 类,支持 UNSIGNED(无符号)修饰(默认有符号):
| 类型 | 占用字节 | 有符号范围 | 无符号范围 | 适用场景 |
|---|---|---|---|---|
| BIT(M) | 1-8 | 1-64 位(默认 1 位) | 同有符号 | 存储 0/1(性别)、位掩码 |
| TINYINT | 1 | -128 ~ 127 | 0 ~ 255 | 年龄、状态值(0-255) |
| SMALLINT | 2 | -32768 ~ 32767 | 0 ~ 65535 | 小范围计数(如订单编号) |
| INT | 4 | -2147483648 ~ 2147483647 | 0 ~ 4294967295 | 普通计数(如用户 ID) |
| BIGINT | 8 | -9e18 ~ 9e18 | 0 ~ 1.8e19 | 大数值(如手机号、雪花 ID) |
关键实战要点:
CREATE TABLE tt5 (gender BIT(1));
INSERT INTO tt5 VALUES (0); -- 成功
INSERT INTO tt5 VALUES (1); -- 成功
INSERT INTO tt5 VALUES (2); -- 越界报错(bit(1)仅支持 0/1)

TINYINT 占用 1 字节,有符号范围 -128127,无符号范围 0255:
-- 1. 创建有符号 TINYINT 表
CREATE TABLE test_tinyint1 (age TINYINT);
-- 2. 插入合法值
INSERT INTO test_tinyint1 VALUES (127); -- 成功(最大值)
INSERT INTO test_tinyint1 VALUES (-128); -- 成功(最小值)
-- 3. 插入越界值(报错)
INSERT INTO test_tinyint1 VALUES (128); -- 报错:Out of range value for column 'age' at row 1
-- 4. 创建无符号 TINYINT 表
CREATE TABLE test_tinyint2 (age TINYINT UNSIGNED);
-- 5. 插入无符号合法值
INSERT INTO test_tinyint2 VALUES (255); -- 成功(无符号最大值)
-- 6. 插入负数(无符号越界报错)
INSERT INTO test_tinyint2 VALUES (-1); -- 报错:Out of range value for column 'age' at row 1
BIT 存储位数据,默认 1 位(仅支持 0/1),位数 M 需≤64,查询时按 ASCII 码显示(易踩坑),但是我自己实际测试也有可能会是 16 进制的,大家可以自己去试试:
-- 1. 创建 BIT(1) 字段的表
CREATE TABLE test_bit (gender BIT(1));
-- 2. 插入合法值(0/1)
INSERT INTO test_bit VALUES (0); -- 成功
INSERT INTO test_bit VALUES (1); -- 成功
-- 3. 插入越界值(报错)
INSERT INTO test_bit VALUES (2); -- 报错:Data truncation: Data too long for column 'gender' at row 1
-- 4. 查询 BIT 字段(关键:直接查询显示 ASCII 字符,需转成数字)
SELECT gender, bin(gender+0) FROM test_bit;
运行结果:
+--------+---------------+
| gender | bin(gender+0)|
+--------+---------------+
||0| -- 0 的 ASCII 显示为空白,转数字后是 0
||1| -- 1 的 ASCII 显示为,转数字后是 1
+--------+---------------+
-- 1. INT 存储手机号(越界测试)
CREATE TABLE test_int (phone INT);
INSERT INTO test_int VALUES (13800138000); -- 报错:Out of range value for column 'phone' at row 1(INT 最大值 2147483647 < 13800138000)
-- 2. BIGINT 存储手机号(成功)
CREATE TABLE test_bigint (phone BIGINT);
INSERT INTO test_bigint VALUES (13800138000); -- 成功
SELECT * FROM test_bigint;
+-------------+
| phone |
+-------------+
|13800138000|
+-------------+
小数类型用于存储带小数点的数值,核心区别在于精度:
| 类型 | 占用字节 | 精度特性 | 适用场景 |
|---|---|---|---|
| FLOAT | 4 | 单精度,约 7 位有效数字 | 非精确计算(如身高、体重) |
| DOUBLE | 8 | 双精度,约 15 位有效数字 | 较高精度计算(如温度) |
| DECIMAL | 可变 | 高精度(自定义整数 + 小数位) | 精确计算(如金额、税率) |
-- 1. 创建小数测试表
CREATE TABLE test_decimal (salary_float FLOAT(10,8), salary_decimal DECIMAL(10,8));
-- 2. 插入相同数值
INSERT INTO test_decimal VALUES (23.12345612, 23.12345612);
-- 3. 查询结果(精度差异)
SELECT * FROM test_decimal;
运行结果:
+---------------+----------------+
| salary_float | salary_decimal |
+---------------+----------------+
|23.12345695|23.12345612|
+---------------+----------------+
float 表示的精度大约是 7 位,decimal 整数最大位置 m 为 65,支持小数最大位置 d 是 30,如果 d 被省略,默认位 0,如果 m 被省略,默认是 10。建议如果希望小数的精度高,推荐使用 decimal。

CREATE TABLE test_decimal2 (price DECIMAL(5,2));
-- 总长度 5,小数位 2 → 范围 -999.99~999.99
INSERT INTO test_decimal2 VALUES (999.99); -- 成功
INSERT INTO test_decimal2 VALUES (1000.00); -- 报错:Out of range value for column 'price' at row 1
字符串类型是最容易用错的类型,核心是区分 CHAR(固定长度)和 VARCHAR(可变长度),以及 TEXT(大文本)的适用场景。
| 类型 | 长度限制 | 存储特性 | 适用场景 |
|---|---|---|---|
| CHAR(L) | L ≤ 255(字符) | 固定长度,不足补空格 | 长度固定(身份证、手机号) |
| VARCHAR(L) | L ≤ 65535(字节) | 可变长度,存储实际长度 + 1-3 字节长度标识 | 长度不固定(姓名、地址) |
| TEXT | 最大 65535 字节 | 大文本,不支持默认值 / 全文索引 | 长文本(文章内容、备注) |
| BLOB | 最大 65535 字节 | 二进制存储 | 图片、文件等二进制数据 |
-- 1. 创建 CHAR/VARCHAR 对比表(UTF8 编码,1 字符=3 字节)
CREATE TABLE test_char_varchar (
c_char CHAR(4),
c_varchar VARCHAR(4)
) CHARSET=utf8;
-- 2. 插入不同长度数据
INSERT INTO test_char_varchar VALUES ('abcd','abcd'); -- 4 字符
INSERT INTO test_char_varchar VALUES ('A','A'); -- 1 字符
INSERT INTO test_char_varchar VALUES ('中国','中国'); -- 2 字符
-- 3. 查询数据(查看实际存储)
SELECT c_char, LENGTH(c_char), -- CHAR 长度:固定 12 字节(4×3)
c_varchar, LENGTH(c_varchar) -- VARCHAR 长度:实际字符×3 + 1 字节
FROM test_char_varchar;
运行结果:
+--------+---------------+------------+------------------+
| c_char | LENGTH(c_char)| c_varchar | LENGTH(c_varchar)|
+--------+---------------+------------+------------------+
| abcd |12| abcd |13|| A |12| A |4|| 中国 |6| 中国 |7|
+--------+---------------+------------+------------------+
关键结论:

如何选择定长或变长字符串? 如果数据确定长度都一样,就使用定长(char),比如:身份证,手机号,md5 如果数据长度有变化,就使用变长 (varchar),比如:名字,地址,但是你要保证最长的能存的进去。 定长的磁盘空间比较浪费,但是效率高。 变长的磁盘空间比较节省,但是效率低。 定长的意义是,直接开辟好对应的空间 变长的意义是,在不超过自定义范围的情况下,用多少,开辟多少。
-- UTF8 编码下,VARCHAR 最大字符数=65532/3≈21844(预留 1-3 字节存长度)
CREATE TABLE test_varchar_limit1 (name VARCHAR(21845)) CHARSET=utf8; -- 报错:Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535.
CREATE TABLE test_varchar_limit2 (name VARCHAR(21844)) CHARSET=utf8; -- 成功
日期时间类型用于存储时间相关数据,核心是区分三者的存储范围、时区特性和自动更新机制:
| 类型 | 占用字节 | 时间范围 | 时区特性 | 自动更新 | 适用场景 |
|---|---|---|---|---|---|
| DATE | 3 | 1000-01-01 ~ 9999-12-31 | 无 | 无 | 生日、日期记录 |
| DATETIME | 8 | 1000-01-01 ~ 9999-12-31 | 无 | 无 | 固定时间(如订单创建时间) |
| TIMESTAMP | 4 | 1970-01-01 ~ 2038-01-19 | 受时区影响 | 支持自动更新 | 时间戳(如最后修改时间) |
-- 1. 创建日期测试表
CREATE TABLE test_datetime (t1 DATE, t2 DATETIME, t3 TIMESTAMP);
-- 2. 仅插入 DATE/DATETIME,TIMESTAMP 自动填充当前时间
INSERT INTO test_datetime(t1,t2) VALUES('1997-07-01','2008-08-08 12:01:01');
-- 3. 查询初始结果
SELECT * FROM test_datetime;
运行结果 1:
+------------+---------------------+---------------------+
| t1 | t2 | t3 |
+------------+---------------------+---------------------+
|1997-07-01|2008-08-0812:01:01|2024-05-2015:30:22|-- t3 自动填充当前时间
+------------+---------------------+---------------------+
-- 4. 更新数据,TIMESTAMP 自动刷新
UPDATE test_datetime SET t1='2000-01-01';
-- 5. 查询更新结果
SELECT * FROM test_datetime;
运行结果 2:
+------------+---------------------+---------------------+
| t1 | t2 | t3 |
+------------+---------------------+---------------------+
|2000-01-01|2008-08-0812:01:01|2024-05-2015:35:48|-- t3 自动更新为修改时间
+------------+---------------------+---------------------+
ENUM(枚举)和 SET(集合)适用于固定选项的场景,避免手动校验输入合法性。
-- 1. 创建枚举表
CREATE TABLE test_enum (username VARCHAR(30), gender ENUM('男','女'));
-- 2. 插入合法值
INSERT INTO test_enum VALUES ('雷锋','男'); -- 成功
INSERT INTO test_enum VALUES ('小红',2); -- 成功(2 对应第二个选项'女')
-- 3. 插入非法值(报错)
INSERT INTO test_enum VALUES ('Tom','未知'); -- 报错:Data truncated for column 'gender' at row 1
-- 4. 查询枚举值
SELECT username, gender, gender+0 FROM test_enum; -- gender+0 查看枚举对应的数字
+----------+--------+-----------+
| username | gender | gender+0|
+----------+--------+-----------+
| 雷锋 | 男 |1|| 小红 | 女 |2|
+----------+--------+-----------+
-- 1. 创建集合表
CREATE TABLE test_set (username VARCHAR(30), hobby SET('登山','游泳','篮球','武术'));
-- 2. 插入多选值
INSERT INTO test_set VALUES ('雷锋','登山,武术'); -- 成功
INSERT INTO test_set VALUES ('小红','游泳'); -- 成功(单选)
-- 3. 插入非法值(自动忽略)
INSERT INTO test_set VALUES ('Tom','登山,游戏'); -- '游戏'不是选项,仅插入'登山'
-- 4. 查询包含某选项的记录(find_in_set 函数)
SELECT * FROM test_set WHERE FIND_IN_SET('登山', hobby);
+----------+-----------+
| username | hobby |
+----------+-----------+
| 雷锋 | 登山,武术 || Tom | 登山 |
+----------+-----------+
总结: MySQL 数据类型的选择核心是'匹配场景 + 平衡性能与空间',本文补充了 PPT 中所有核心测试案例,关键要点总结:

微信公众号「极客日志」,在微信中扫描左侧二维码关注。展示文案:极客日志 zeeklog
在线格式化和美化您的 SQL 查询(它支持各种 SQL 方言)。 在线工具,SQL 美化和格式化在线工具,online
解析 INSERT 等受限 SQL,导出为 CSV、JSON、XML、YAML、HTML 表格(见页内语法说明)。 在线工具,SQL转CSV/JSON/XML在线工具,online
CSV 与 JSON/XML/HTML/TSV/SQL 等互转,单页多 Tab。 在线工具,CSV 工具包在线工具,online
将字符串编码和解码为其 Base64 格式表示形式即可。 在线工具,Base64 字符串编码/解码在线工具,online
将字符串、文件或图像转换为其 Base64 表示形式。 在线工具,Base64 文件转换器在线工具,online
将 Markdown(GFM)转为 HTML 片段,浏览器内 marked 解析;与 HTML转Markdown 互为补充。 在线工具,Markdown转HTML在线工具,online