SQL
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