MySQL 数据类型选型避坑实录

INT 装不下手机号

INT 上限 2,147,483,647，11 位手机号轻松溢出，得用 BIGINT 或 CHAR(11)。

CREATE TABLE test_int(phone INT);
INSERT INTO test_int VALUES(13800138000); -- Out of range

CREATE TABLE test_bigint(phone BIGINT);
INSERT INTO test_bigint VALUES(13800138000); -- OK

小数：FLOAT 粗略，DECIMAL 精准

实际测试：

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 稳稳的 23.12345612。所以算钱的东西，别用 FLOAT。

DECIMAL(M,D) 的 M 是总数字位数，D 是小数位数。例如 DECIMAL(5,2) 范围 -999.99 ~ 999.99。

CREATE TABLE test_decimal2(price DECIMAL(5,2));
INSERT INTO test_decimal2 VALUES(999.99); -- OK
INSERT INTO test_decimal2 VALUES(1000.00); -- Out of range

字符串类型：定长还是变长

CHAR(4) 与 VARCHAR(4) 的存储差异

UTF8 编码下，一个字符占 3 字节。插入不同长度的数据时，CHAR(4) 总是固定占用 12 字节（4×3），而 VARCHAR(4) 只占用实际字符字节数 + 1 字节。

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;

可见，VARCHAR 能省不少空间，但查找时 VARCHAR 需要额外计算长度，效率上略低于 CHAR。所以长度完全固定的数据（如身份证 18 位）用 CHAR，长度变化的用 VARCHAR。

VARCHAR 最大长度

在 UTF8 下，VARCHAR 最多存约 21844 个字符（65532 字节除去长度前缀后再除以 3），实际建表超出就会报错。

CREATE TABLE test_varchar_limit1(name VARCHAR(21845)) CHARSET=utf8; -- Row size too large
CREATE TABLE test_varchar_limit2(name VARCHAR(21844)) CHARSET=utf8; -- OK

日期时间类型：选 DATE、DATETIME 还是 TIMESTAMP

TIMESTAMP 有个特性：当插入时没给值，或者更新行时，会自动设置为当前时间。

CREATE TABLE test_datetime(
    t1 DATE,
    t2 DATETIME,
    t3 TIMESTAMP
);
INSERT INTO test_datetime(t1,t2) VALUES('1997-07-01','2008-08-08 12:01:01');
-- t3 自动填充当前时间

UPDATE test_datetime SET t1='2000-01-01';
-- t3 也会刷新为当前时间

除非需要时区或自动更新，否则 DATETIME 兼容范围更大，没有 2038 问题，一般场景优先选它。

ENUM 与 SET：固定选项的轻量约束

ENUM 是单选，SET 是多选，都能在字段层面约束输入值。

ENUM 测试

CREATE TABLE test_enum(
    username VARCHAR(30),
    gender ENUM('男','女')
);
INSERT INTO test_enum VALUES('雷锋','男'); -- OK
INSERT INTO test_enum VALUES('小红',2); -- 2 代表 '女'，插入的也是 '女'
INSERT INTO test_enum VALUES('Tom','未知'); -- 报错
SELECT username, gender, gender+0 FROM test_enum; -- gender+0 显示内部数字

SET 测试

SET 存储时会把选中的项按位组合，查询时可用 FIND_IN_SET 判断包含关系。

CREATE TABLE test_set(
    username VARCHAR(30),
    hobby SET('登山','游泳','篮球','武术')
);
INSERT INTO test_set VALUES('雷锋','登山,武术'); -- 逗号分隔
INSERT INTO test_set VALUES('小红','游泳');
INSERT INTO test_set VALUES('Tom','登山,游戏'); -- '游戏' 不是合法选项，仅保存 '登山'

SELECT * FROM test_set WHERE FIND_IN_SET('登山', hobby);

SET 也可用数字代表，1=登山(0001)，2=游泳(0010)，3=登山+游泳(0011)，以此类推。

选型避坑备忘

• 够用就好：年龄用 TINYINT，别用 INT；金额用 DECIMAL，别用 FLOAT。
• 固定长度选 CHAR，变化长度选 VARCHAR。手机号用 CHAR(11) 比 VARCHAR(11) 查询更快，身份证用 CHAR(18)。
• BIT 查询要转换，否则看到的是乱码，用 +0 或 CAST。
• 大文本慎用 TEXT，能拆字段尽量拆，否则查询、索引都受影响。
• 日期优先 DATETIME，避免 TIMESTAMP 的 2038 天花板，除非你需要自动更新时间。

这些坑我趟过一遍，希望能帮你设计表时少走弯路。后面还可以聊聊约束和索引，把这套设计做扎实。