MySQL 数据类型选型实战与避坑指南

2.1.1 TINYINT 类型测试

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'
-- 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'

2.1.2 BIT 类型测试

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'
-- 4. 查询 BIT 字段（关键：直接查询显示 ASCII 字符，需转成数字）
SELECT gender, bin(gender+0) FROM test_bit;

执行结果如下：

+--------+---------------+
| gender | bin(gender+0)|
+--------+---------------+
|        | 0             |
|        | 1             |
+--------+---------------+

2.1.3 INT/BIGINT 对比测试

-- 1. INT 存储手机号（越界测试）
CREATE TABLE test_int (phone INT);
INSERT INTO test_int VALUES (13800138000); -- 报错：Out of range value for column 'phone'

-- 2. BIGINT 存储手机号（成功）
CREATE TABLE test_bigint (phone BIGINT);
INSERT INTO test_bigint VALUES (13800138000); -- 成功
SELECT * FROM test_bigint;

2.2 小数类型（FLOAT/DOUBLE/DECIMAL）

小数类型用于存储带小数点的数值，核心区别在于精度：

2.2.1 FLOAT/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    |
+---------------+----------------+

发现 DECIMAL 的精度更准确。因此如果我们希望某个数据表示高精度，应选择 DECIMAL。

关键结论：FLOAT 是近似存储，存在精度丢失；DECIMAL 是精确存储，适合金额、税率等场景。FLOAT 表示的精度大约是 7 位，DECIMAL 整数最大位置 m 为 65，支持小数最大位置 d 是 30。如果 d 被省略，默认位 0；如果 m 被省略，默认是 10。建议如果希望小数的精度高，推荐使用 DECIMAL。

2.2.2 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'

三、字符串类型：CHAR 与 VARCHAR 的终极选型

字符串类型是最容易用错的类型，核心是区分 CHAR（固定长度）和 VARCHAR（可变长度），以及 TEXT（大文本）的适用场景。

3.1 核心字符串类型对比

3.2 CHAR 与 VARCHAR 深度测试

-- 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 (4) 无论存储多少字符，都占用 12 字节（4×3），不足补空格；
• VARCHAR (4) 存储 1 字符占用 4 字节（3+1），2 字符占用 7 字节（6+1），4 字符占用 13 字节（12+1），更节省空间。

如何选择定长或变长字符串？ 如果数据确定长度都一样，就使用定长（CHAR），比如：身份证、手机号、MD5。 如果数据长度有变化，就使用变长（VARCHAR），比如：名字、地址，但是你要保证最长的能存进去。 定长的磁盘空间比较浪费，但是效率高。 变长的磁盘空间比较节省，但是效率低。 定长的意义是，直接开辟好对应的空间。 变长的意义是，在不超过自定义范围的情况下，用多少，开辟多少。

3.3 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/DATETIME/TIMESTAMP 选型

日期时间类型用于存储时间相关数据，核心是区分三者的存储范围、时区特性和自动更新机制：

4.1 TIMESTAMP 自动更新测试

-- 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-08 12:01:01 | 2024-05-20 15: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-08 12:01:01 | 2024-05-20 15:35:48 |
+------------+---------------------+---------------------+
-- t3 自动更新为修改时间

五、特殊字符串类型：ENUM 与 SET（单选 / 多选场景）

ENUM（枚举）和 SET（集合）适用于固定选项的场景，避免手动校验输入合法性。

5.1 ENUM（单选枚举）测试

-- 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'
-- 4. 查询枚举值
SELECT username, gender, gender+0 FROM test_enum; -- gender+0 查看枚举对应的数字

运行结果：

+----------+--------+-----------+
| username | gender | gender+0  |
+----------+--------+-----------+
| 雷锋     | 男     | 1         |
| 小红     | 女     | 2         |
+----------+--------+-----------+

5.2 SET（多选集合）测试

-- 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      | 登山      |
+----------+-----------+

SET 的这个也可以用数字来代表，不过是由比特位来算的，比如 1 代表登山，2 代表游泳，3 代表的是登山和游泳 (0011)。

六、数据类型选型避坑指南和总结

• 按'最小必要'原则选型：如年龄用 TINYINT（0-255 足够），不用 INT；金额用 DECIMAL(10,2)，不用 FLOAT。
• 避免用字符串存储数字：如手机号用 BIGINT 或 CHAR(11)，不用 VARCHAR(11)（CHAR 查询更快）；身份证用 CHAR(18)（固定长度），不用 VARCHAR。
• BIT 类型查询需转数字：直接查询 BIT 字段显示 ASCII 字符，需用 gender+0 或 CAST(gender AS UNSIGNED) 转为数字。
• 慎用 TEXT/BLOB：大文本类型会降低查询性能，若能拆分字段（如将文章摘要单独存储为 VARCHAR），尽量避免直接用 TEXT。
• 日期类型优先选 DATETIME：除非明确需要时区转换或自动更新，否则 DATETIME 的兼容性和范围更优，避免 TIMESTAMP 的 2038 限制。

总结： MySQL 数据类型的选择核心是'匹配场景 + 平衡性能与空间'，关键要点如下：

• BIT/TINYINT 有严格范围限制，插入越界值会直接报错，查询 BIT 需转数字；
• FLOAT 存在精度丢失，金额等精确场景必须用 DECIMAL；
• CHAR 适合固定长度字符串（如手机号），VARCHAR 适合可变长度字符串（如姓名）；
• TIMESTAMP 支持自动更新，但有 2038 年限制，普通场景优先用 DATETIME；
• ENUM/SET 简化固定选项存储，SET 查询需用 FIND_IN_SET 函数。