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

关键实战要点：

• 慎用 UNSIGNED：虽然无符号能扩大正数范围，但插入负数会直接报错，且与有符号计算时易出逻辑问题。建议直接用更大的整数类型（如用 INT 替代 TINYINT UNSIGNED）。
• BIT 类型的坑：BIT 字段存储的是位数据，查询时默认按 ASCII 码显示。例如 bit(8) 存储 10 可能显示为换行符，存储 65 显示为 'A'。仅适合存储简单标识，查询时需转数字。

2.1.1 TINYINT 测试

TINYINT 占用 1 字节，有符号范围 -128127，无符号 0255：

-- 创建有符号表
CREATE TABLE test_tinyint1 (age TINYINT);
INSERT INTO test_tinyint1 VALUES (127); -- 成功（最大值）
INSERT INTO test_tinyint1 VALUES (-128); -- 成功（最小值）
INSERT INTO test_tinyint1 VALUES (128);  -- 报错：Out of range value

-- 创建无符号表
CREATE TABLE test_tinyint2 (age TINYINT UNSIGNED);
INSERT INTO test_tinyint2 VALUES (255); -- 成功
INSERT INTO test_tinyint2 VALUES (-1);   -- 报错：Out of range value

2.1.2 BIT 测试

BIT 存储位数据，默认 1 位（仅支持 0/1），查询时需注意转换：

CREATE TABLE test_bit (gender BIT(1));
INSERT INTO test_bit VALUES (0); -- 成功
INSERT INTO test_bit VALUES (1); -- 成功
INSERT INTO test_bit VALUES (2); -- 报错：Data truncation

-- 查询时转为数字查看
SELECT gender, bin(gender + 0) FROM test_bit;

运行结果中，gender+0 能将 ASCII 显示转换为实际数值，方便调试。

2.1.3 INT 与 BIGINT 对比

手机号通常超过 INT 范围，必须使用 BIGINT 或 CHAR：

-- INT 存储手机号（越界）
CREATE TABLE test_int (phone INT);
INSERT INTO test_int VALUES (13800138000); -- 报错

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

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

核心区别在于精度。FLOAT 是近似存储，DECIMAL 是精确存储。

2.2.1 精度对比测试

CREATE TABLE test_decimal (
    salary_float FLOAT(10,8), 
    salary_decimal DECIMAL(10,8)
);
INSERT INTO test_decimal VALUES (23.12345612, 23.12345612);
SELECT * FROM test_decimal;

结果显示 salary_float 存在精度丢失（23.12345695），而 salary_decimal 保持准确。结论：涉及金额、税率等精确场景，必须使用 DECIMAL。

2.2.2 DECIMAL 范围测试

DECIMAL 的精度由 (M,D) 定义，总长度 M 最大 65，小数位 D 最大 30：

CREATE TABLE test_decimal2 (price DECIMAL(5,2)); -- 总长 5，小数 2
INSERT INTO test_decimal2 VALUES (999.99);       -- 成功
INSERT INTO test_decimal2 VALUES (1000.00);      -- 报错：超出范围

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

字符串类型最容易用错，核心区分 CHAR（定长）和 VARCHAR（变长）。

3.1 核心类型对比

3.2 CHAR 与 VARCHAR 深度测试

以 UTF8 编码为例（1 字符=3 字节）：

CREATE TABLE test_char_varchar (
    c_char CHAR(4), 
    c_varchar VARCHAR(4)
) CHARSET=utf8;

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 字符

SELECT c_char, LENGTH(c_char), c_varchar, LENGTH(c_varchar) FROM test_char_varchar;

结果分析：

• CHAR(4) 无论存多少字符，都占用 12 字节（4×3），不足补空格。
• VARCHAR(4) 存储 1 字符占 4 字节（3+1），2 字符占 7 字节（6+1），更节省空间。

如何选择？

• 定长（CHAR）：数据确定长度一致时使用，如身份证号、手机号、MD5。效率高，但空间可能浪费。
• 变长（VARCHAR）：数据长度变化时使用，如姓名、地址。空间节省，但需额外存储长度信息。

3.3 VARCHAR 长度限制

UTF8 编码下，单行最大 65535 字节，扣除其他字段开销后，VARCHAR 最大字符数约为 21844：

CREATE TABLE test_varchar_limit1 (name VARCHAR(21845)) CHARSET=utf8; -- 报错：Row size too large
CREATE TABLE test_varchar_limit2 (name VARCHAR(21844)) CHARSET=utf8; -- 成功

四、日期时间类型：DATE/DATETIME/TIMESTAMP

三者核心区别在于存储范围、时区特性和自动更新机制。

4.1 TIMESTAMP 自动更新

TIMESTAMP 字段若未指定值，会自动填充当前时间；更新记录时也会自动刷新：

CREATE TABLE test_datetime (
    t1 DATE, 
    t2 DATETIME, 
    t3 TIMESTAMP
);

-- 仅插入前两个字段，t3 自动填充
INSERT INTO test_datetime(t1,t2) VALUES ('1997-07-01','2008-08-08 12:01:01');
SELECT * FROM test_datetime;

-- 更新 t1，观察 t3 是否变化
UPDATE test_datetime SET t1='2000-01-01';
SELECT * FROM test_datetime;

注意：TIMESTAMP 有 2038 年限制，除非明确需要时区转换或自动更新，否则优先选用 DATETIME。

五、特殊字符串类型：ENUM 与 SET

适用于固定选项场景，避免手动校验输入合法性。

5.1 ENUM（单选枚举）

CREATE TABLE test_enum (
    username VARCHAR(30), 
    gender ENUM('男','女')
);

INSERT INTO test_enum VALUES ('雷锋','男'); -- 成功
INSERT INTO test_enum VALUES ('小红',2);    -- 成功（对应第二个选项）
INSERT INTO test_enum VALUES ('Tom','未知'); -- 报错

SELECT username, gender, gender+0 FROM test_enum;

5.2 SET（多选集合）

CREATE TABLE test_set (
    username VARCHAR(30), 
    hobby SET('登山','游泳','篮球','武术')
);

INSERT INTO test_set VALUES ('雷锋','登山，武术'); -- 成功
INSERT INTO test_set VALUES ('Tom','登山，游戏');  -- '游戏'被忽略

-- 查询包含某选项的记录
SELECT * FROM test_set WHERE FIND_IN_SET('登山', hobby);

SET 底层用比特位表示，查询需用 FIND_IN_SET 函数。

六、选型避坑指南与总结

1. 最小必要原则：年龄用 TINYINT，不用 INT；金额用 DECIMAL，不用 FLOAT。
2. 避免字符串存数字：手机号用 BIGINT 或 CHAR(11)，身份证用 CHAR(18)。
3. BIT 查询转数字：直接查显示 ASCII，需用 CAST(... AS UNSIGNED)。
4. 慎用 TEXT/BLOB：大文本降低性能，摘要可拆分为 VARCHAR。
5. 日期类型优先 DATETIME：除非需时区或自动更新，否则避开 TIMESTAMP 的 2038 限制。

掌握这些带测试验证的知识点，能避免大部分数据库设计问题。核心在于'匹配场景 + 平衡性能与空间'。