MySQL 数据类型核心指南：选型、实战与避坑

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

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

2.2 小数类型

核心区别在于精度：

精度对比测试

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;

结果会发现 FLOAT 存在精度丢失（23.12345695 vs 23.12345612）。结论：涉及金额必须用 DECIMAL。

三、字符串类型：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');
INSERT INTO test_char_varchar VALUES ('A', 'A');
INSERT INTO test_char_varchar VALUES ('中国', '中国');

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），更节省空间。

如何选择？ 如果数据确定长度都一样（如身份证），用 CHAR；如果长度变化大（如姓名），用 VARCHAR。定长效率高但浪费空间，变长省空间但效率略低。

四、日期时间类型选型

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

-- 更新数据，TIMESTAMP 自动刷新
UPDATE test_datetime SET t1='2000-01-01';
SELECT * FROM test_datetime;

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

五、特殊类型：ENUM 与 SET

适用于固定选项场景，避免手动校验。

• ENUM：单选。例如性别。插入非法值会报错。
• SET：多选。例如爱好。插入非法值会自动忽略。

-- ENUM 测试
CREATE TABLE test_enum (username VARCHAR(30), gender ENUM('男','女'));
INSERT INTO test_enum VALUES ('雷锋','男');
INSERT INTO test_enum VALUES ('Tom','未知'); -- 报错

-- SET 测试
CREATE TABLE test_set (username VARCHAR(30), hobby SET('登山','游泳'));
INSERT INTO test_set VALUES ('雷锋','登山，武术'); -- '武术'被忽略
SELECT * FROM test_set WHERE FIND_IN_SET('登山', hobby);

六、避坑指南与总结

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

掌握这些选型逻辑，能避免大部分数据库设计问题。