MySQL数据类型选型指南：从底层存储到慢查询优化

做 MySQL 开发这几年，MySQL 数据类型是我见过引发线上事故最多的"基础问题"。很多慢查询、数据错乱、磁盘膨胀，追到根上往往就是建表时某个字段类型拍脑袋选的。这篇文章把 MySQL 数据类型从底层存储、选型逻辑到实操落地完整梳理一遍，包括数值型、字符串型、日期时间型和 JSON 等扩展类型怎么选、怎么避开常见的坑，以及建表、跨系统同步时的具体处理方案。适合刚入门的新手，也适合被线上慢查询折磨过的中级开发者。

1. 一个"选错类型"的慢查询，比事故报告更有说服力

1.1 手机号字段引发的全表扫描

先讲一个我实际处理过的案例。某业务表t_user里有个字段mobile，建表的人图省事用了VARCHAR(255)，因为当时觉得"反正字符串什么都能塞"。业务量小的时候没感觉，等数据量涨到 500 万行之后，运营那边经常要按手机号查人，SQL 长这样：

SELECT id, username, mobile FROM t_user WHERE mobile = 13800138000;

这条 SQL 跑了 3 秒多，EXPLAIN 一看type=ALL，全表扫描。问题出在等号右边的字面量没加引号，MySQL 会把字符串列mobile和数字常量13800138000做比较。按照官方比较规则，字符列和数值常量比较时，字符列会被转换为数值，于是每一行都要做一次隐式的字符串转数字，索引自然废了。

改成下面这样，查询立刻回到毫秒级：

SELECT id, username, mobile FROM t_user WHERE mobile = '13800138000';

这个案例有两个教训：第一，字段类型不能"够用就行"，VARCHAR(255)和VARCHAR(20)存储空间看似差不多，但在索引、排序、内存临时表里的表现天差地别；第二，类型影响的不只是存储，还决定 SQL 怎么写、索引能不能走。数据类型选型，本质上是给整个系统的查询行为定基调。

1.2 数据类型的隐性成本：内存、排序与索引

很多人以为VARCHAR(255)和VARCHAR(20)存同一个值占用的磁盘一样，数据行里确实是按实际内容长度存的，但 MySQL 在计算排序缓冲、内存临时表、以及某些 join 操作时，会按照声明的最大长度去分配内存。一个VARCHAR(255)的列参与ORDER BY，每行就会按 255 个字符的容量去计算，数据量大一点，临时文件和内存消耗成倍增长。

索引的成本更直观。InnoDB 里主键和二级索引都是 B+ 树，索引项越小，一个 16KB 的页能容纳的索引键就越多，树的层数就越低，扫描和回表次数都少。INT只占 4 字节，BIGINT占 8 字节，而一个utf8mb4下的VARCHAR(50)最多可能占 200 字节。把主键和外键设计成字符串，索引膨胀速度是惊人的。这些都不是"功能上不能用"，而是"规模上来之后必然暴露"的隐形问题。

2. 四大类型体系的底层逻辑与选型取舍

2.1 数值类型：INT、BIGINT 与 DECIMAL 怎么选

整数类型的档位很清楚：TINYINT1 字节、SMALLINT2 字节、MEDIUMINT3 字节、INT4 字节、BIGINT8 字节。有符号和无符号范围差一倍，比如INT有符号上限是 21 亿多，无符号可以到 42 亿多。选型逻辑就一句话：按业务量级预估，够用就选小一号的。主键的话，我个人倾向于直接BIGINT UNSIGNED，尤其是互联网业务，别因为省 4 个字节把主键上限卡死在 21 亿，后面迁主键类型是大工程。

有一点必须提：MySQL 8.0 已经废弃了INT(11)这种显示宽度的写法，数字后面括号里的数字不再控制显示长度，也没有任何存储或约束意义。现在建表如果还写INT(11)，虽然不报错，但纯属误导后人。

DECIMAL和浮点类型是另外一个高频翻车点。FLOAT和DOUBLE是近似存储，二进制无法精确表示所有十进制小数，所以金额、余额、单价这类必须精确计算的字段，一律用DECIMAL。DECIMAL(10,2)表示总位数 10、小数位 2，能表示的最大金额是 99999999.99。设计金额字段时，小数位按业务精度来，比如有些平台涉及积分、折扣，会选择DECIMAL(12,4)，避免单位换算后丢精度。

自增主键如果用了UNSIGNED，注意AUTO_INCREMENT是允许配合无符号整数使用的，但一旦到达无符号上限，插入会直接报Duplicate entry或Out of range，不会自动回绕。这个在实际运维中见过，别指望数据库会"聪明地"循环利用 ID。

2.2 字符串类型：CHAR、VARCHAR、TEXT 之间差的不只是长度

CHAR(N)和VARCHAR(N)里的 N 都是字符数，不是字节数，这点在utf8mb4字符集下特别容易混淆。CHAR是定长，存不满会用空格填充，取出来的时候尾部空格会被去掉；VARCHAR是变长，额外需要 1 到 2 个字节记录长度，所以存同样内容时VARCHAR通常更省空间，但行格式也会多一点点开销。

长度阈值有个很重要的数字：VARCHAR的字节总长上限是 65535，这是 MySQL 层面所有 VARCHAR 列加起来的限制，而且受行格式和字符集影响。在utf8mb4下，VARCHAR(255)最多可能用 1020 字节，VARCHAR(16383)这种定义在大多数情况下根本建不出来，因为 16383 乘以 4 已经超过 65535 了。实际设计时完全不需要摸上限，字段语义是什么就定义多长，用户名VARCHAR(32)、手机号VARCHAR(20)、邮箱VARCHAR(128)，这些长度足够。

TEXT家族常见坑有三个：第一，TEXT列不能有默认值，建表时想给TEXT写DEFAULT ''会直接报错；第二，TEXT不能直接建普通索引，必须用前缀索引，比如KEY idx_content (content(100))；第三，如果查询里频繁出现TEXT列，排序和内存临时表会非常吃力，因为 MEMORY 引擎不支持TEXT和BLOB，一旦临时表里出现这类字段，MySQL 会直接落磁盘临时表，性能断崖式下跌。日志、文章正文这类大文本，能不放进 MySQL 就别放。

ENUM和SET我一般建议慎用。ENUM在 MySQL 内部是按索引值排序的，不是按字典序，而且枚举列表一改就要ALTER TABLE，枚举值多了之后维护成本很高。如果状态值是"基本永不变更"的，比如性别、订单状态机上非常稳定的几个节点，用ENUM还能接受；但如果业务方隔三差五想加一个状态，老老实实用TINYINT加一层字典表映射，扩展性完全不同。

2.3 日期时间类型：DATETIME 与 TIMESTAMP 的时区和范围陷阱

DATETIME和TIMESTAMP是新手最容易踩坑的一对。TIMESTAMP占 4 字节，存储范围是 1970 年到 2038 年，并且受时区影响，也就是说你写入一个时间，连接会话的time_zone参数变了，读出来的值可能就不一样。DATETIME占 8 字节，范围是 1000 年到 9999 年，存储和读取完全不依赖时区，存进去的是什么就显示什么。

这里没有绝对标准，但我的经验是：业务需要记录"用户实际操作时刻"并且希望按访问者时区展示的，用TIMESTAMP；纯粹作为数据产生时间、不希望被时区干扰的，用DATETIME。很多团队干脆统一用DATETIME，省得排查时区问题，这也是一种可行的洁癖式约定。

DATETIME在 5.6.5 之后才支持DEFAULT CURRENT_TIMESTAMP，早期版本只能用TIMESTAMP来做这个事。所以老项目里常见到"建表时时间字段全是 TIMESTAMP"的情况，这不是设计者偏爱，而是版本限制留下的遗产。现在新项目直接用：

created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)

(3)表示毫秒精度，(6)是微秒精度。我的建议是：对时间精度敏感的业务，直接上一档DATETIME(3)或TIMESTAMP(3)，不然后面做时序分析和对账的时候精度不够，改表又麻烦。

查询日期范围时有个高频错误：在条件里对日期列做函数处理。比如统计今天的注册用户：

SELECT COUNT(*) FROM t_user WHERE DATE(created_at) = CURRENT_DATE();

这样写很直观，但DATE(created_at)会导致created_at上的索引失效，因为索引里存的是原始时间值，不是函数处理后的值。正确写法是范围查询：

SELECT COUNT(*) FROM t_user WHERE created_at >= CURRENT_DATE() AND created_at < CURRENT_DATE() + INTERVAL 1 DAY;

2.4 JSON、布尔等扩展类型：使用场景与克制原则

MySQL 从 5.7 开始提供原生 JSON 类型，8.0 做了大量优化。JSON 类型的第一个好处是合法性校验，插入时不是合法 JSON 会直接报错；第二个好处是存储上做了二进制格式优化，比字符串列存 JSON 取出来再json_decode要高效得多。对于不确定结构的扩展属性，比如用户的自定义设置、埋点事件的额外参数，这种能塞 JSON 就塞 JSON，别费劲搞一堆可空字段。

但 JSON 不是万能药，最大的问题是它无法像普通列一样高效过滤。虽然 8.0 支持对 JSON 列里的某个字段创建表达式索引，或者更常见的做法是建一个虚拟列再对虚拟列建索引，但维护成本比独立字段高不少。比如：

ALTER TABLE t_user ADD COLUMN city VARCHAR(50) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(profile, '$.city'))) STORED; ALTER TABLE t_user ADD INDEX idx_city (city);

这个方案可读性和使用体验都不错，但要让团队成员理解虚拟列的存在意义，不然别人一看到profile里有 city 就直接用JSON_EXTRACT去查了，索引又没走。

布尔值这块，MySQL 没有真正的BOOL类型，BOOL和BOOLEAN都只是TINYINT(1)的别名。默认用TINYINT(1)存 0 和 1 就够了，加上注释写清楚语义。有人喜欢用BIT(1)表示布尔，但查询结果返回的是二进制值，很多客户端和 ORM 处理得很别扭，不值得。

3. 实操落地：从建表到跨系统同步的完整类型方案

3.1 一套可以直接抄的字段类型选型清单

我按最常见的业务字段整理了推荐类型，这套清单直接照用不会出大问题：

这里想强调一个"最小够用"原则：先把语义想清楚，再选类型。一个状态字段如果只可能有 0 到 5，就犯不上用INT；一个字符串长度上限明确的字段，就犯不上给 255。很多人随手写VARCHAR(255)，觉得"反正以后能存长一点"，实际上给自己埋了一堆索引和内存的雷。

3.2 用户表、订单表、日志表的完整建表范例

以最常见的业务场景为例，我直接给出一套可落地执行的建表 SQL。用户表核心字段包括账号、手机号、状态和时间戳：

CREATE TABLE `t_user` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键', `username` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '用户名', `mobile` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '手机号', `gender` TINYINT NOT NULL DEFAULT 0 COMMENT '性别: 0未知, 1男, 2女', `status` TINYINT NOT NULL DEFAULT 1 COMMENT '用户状态: 1正常, 2冻结', `profile` JSON DEFAULT NULL COMMENT '扩展属性', `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间', `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新时间', PRIMARY KEY (`id`), UNIQUE KEY `uk_mobile` (`mobile`), KEY `idx_username` (`username`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户表';

手机号做了唯一键，因为一个手机号通常只对应一个账号；用户名加了普通索引，支持前缀模糊查询的前缀部分。状态字段用TINYINT加注释，比用字符串好扩展，也不存在大小写和排序问题。

订单表的核心是金额和订单号，金额必须DECIMAL，订单号为了可读性用VARCHAR(32)并加唯一索引：

CREATE TABLE `t_order` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键', `order_no` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '订单号', `user_id` BIGINT UNSIGNED NOT NULL COMMENT '下单用户ID', `total_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '订单总金额', `status` TINYINT NOT NULL DEFAULT 0 COMMENT '订单状态: 0待支付, 1已支付, 2已发货, 3已完成, 4已取消', `pay_time` DATETIME(3) DEFAULT NULL COMMENT '支付时间', `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间', PRIMARY KEY (`id`), UNIQUE KEY `uk_order_no` (`order_no`), KEY `idx_user_id` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='订单表';

日志表则要刻意控制大字段的使用。log_content如果可能很长就选TEXT，但查询时不要无条件SELECT *，只取需要的字段。时间字段要特别设计，因为日志查询几乎总带时间范围，所以log_time必须有索引，这也意味着不能用DATETIME函数包住它。

3.3 与 Python/pandas/数据同步链路中的类型映射

数据开发链路里 MySQL 往往不是终点，从 MySQL 同步到 ClickHouse、或者用 Python/pandas 做分析，都是日常操作。这里最容易出问题的是DECIMAL和日期类型。

Python 的 MySQL 驱动返回DECIMAL时是decimal.Decimal对象，pandas 读进来之后通常变成objectdtype，直接做sum()、mean()会有一堆麻烦。处理办法要么在 SQL 里提前转成浮点或字符串，要么在 pandas 里显式转换：

df["total_amount"] = df["total_amount"].astype(float)

日期类型在 pandas 里一般会映射成datetime64[ns]，但如果 MySQL 连接参数没配好，比如驱动把TIMESTAMP读成了字符串，数据就变成了 object，需要再用pd.to_datetime拉回来。反过来从 Python 往 MySQL 写数据时，pandas 的Timestamp对象要转成 Python 原生的datetime.datetime，否则 MySQL 驱动可能不认识。

跨系统同步时有一个通用原则：源端是什么类型，同步到目标端时先想清楚目标端是否保留同样的精度和语义。比如 MySQL 的DATETIME(3)同步到 ClickHouse 时用DateTime64(3)对应；DECIMAL(12,2)同步过去要选Decimal(12, 2)，不要顺手变成Float64，否则金额误差会在聚合统计中暴露出来。热点里经常出现 mysql 同步到 ClickHouse 的场景，这类同步任务里，类型映射表应该提前整理好，别等数据对不上账再回头改。

4. 常见问题与排查技巧实录

4.1 隐式转换：索引失效的头号元凶

隐式转换是数据类型问题里出现频率最高、查起来最隐蔽的。最常见的就是字符串字段和数字常量比较，前面手机号的案例就是典型。排查方法很简单，抓到慢查询后先看 EXPLAIN，如果明明有条件字段也有索引，但type变成了ALL，就优先怀疑隐式转换。

另一种隐式转换发生在表连接时，两个表的关联字段类型不一致。比如t_order.user_id是BIGINT UNSIGNED，关联的t_user_log.user_id是VARCHAR(20)，MySQL 会把BIGINT隐式转成字符串或者反过来转换，关联时无法高效使用索引，还可能造成结果集扩大。这类问题在数据库里很难一眼看出来，需要把关联字段的类型在项目规范里统一掉。我的习惯是：所有表的主键、逻辑外键、用户 ID 这类字段，全部统一成BIGINT UNSIGNED，没有例外。

还有一种隐蔽的隐式转换是字符集不一致导致的。两张表一张是utf8mb4，一张是utf8，关联同一个字符串字段时 MySQL 会做字符集转换，同样会导致索引失效。所以跨表关联时，字符集和排序规则最好全局统一，8.0 默认utf8mb4_0900_ai_ci，老项目还有utf8_general_ci的话，尽早规划迁移。

4.2 utf8 与 utf8mb4：乱码、表情包与索引长度超限

MySQL 的utf8其实只是utf8mb3，最多支持 3 字节，存不了 emoji 和部分生僻字。从 MySQL 8.0 开始默认字符集改成utf8mb4，4 字节完整覆盖 Unicode。新项目直接用utf8mb4不用犹豫，老项目如果还在utf8，至少把用到的列尽快转掉，不然后面用户昵称里出现一个 emoji 就是乱码甚至报错。

索引长度超限是utf8mb4下特别容易踩的坑。旧版本 InnoDB 单列索引键长度最大 767 字节（8.0 默认是 3072 字节），而utf8mb4下一个字符最多 4 字节，所以哪怕VARCHAR(255)也有可能算出 1020 字节，建索引就报Specified key was too long。常见解法是把列缩短到VARCHAR(191)（191 乘 4 等于 764，小于 767），或者给索引加前缀，比如KEY idx_content (content(100))。这种问题在开发环境数据量小时感觉不到，上生产导数据或者跑迁移脚本时才炸。

4.3 DATETIME 默认值、严格模式与历史脏数据

DATETIME默认值在 MySQL 5.6 之前的版本是做不到的，现在新版本没问题，但老库迁到新版本后往往会暴露出0000-00-00 00:00:00这样的零值日期。在开启NO_ZERO_DATE严格模式的情况下，读取或更新这类数据会直接报错。

处理历史脏数据时，不要直接在应用层存傻字符串，而是先查出来统一改成合理的默认时间。比如把零值统一刷成项目上线日期或1970-01-01 00:00:00（注意 TIMESTAMP 下限是 1970）。更关键的是建表规范要守住：时间字段要么允许 NULL，要么给DEFAULT CURRENT_TIMESTAMP，从源头杜绝零值日期进场。

还有一个细节：DATETIME和TIMESTAMP在存储上一个是 8 字节一个是 4 字节，但你在 InnoDB 里看到DATETIME(3)时并不会觉得它多占空间。这里真正的成本在索引：时间字段经常做排序和范围查询，索引宽度越大，扫描代价越高。能用DATE表达的就不上DATETIME，能用DATETIME的别因为"方便打印"就随手加一长串默认值。

4.4 大表改类型的代价与正确姿势

线上表发现类型选错了，第一反应是ALTER TABLE改字段类型。如果表只有几十万行，直接改问题不大；但如果表已经几千万行，一条简单的ALTER TABLE MODIFY也可能锁表、复制全量数据，在读写高峰期执行几乎等于事故。

MySQL 8.0 对部分 DDL 做了优化，但很多类型变更仍然需要重建表。比如VARCHAR长度从 50 扩到 200，某些情况下可以做 in-place 修改；但从VARCHAR改成TEXT、从INT改成BIGINT，基本都逃不掉重建。正确姿势是使用pt-online-schema-change或gh-ost这类在线改表工具，通过临时表、触发器、批量拷贝的方式在业务不中断的情况下完成变更。

在线改表也不是万能的，大事务、外键约束、复杂触发器都可能让工具失效。所以最根本的解法还是在建表阶段就把类型选对，把字段类型当作对外 API 的一部分来审视，因为它一旦上线，改的代价会远超你建表时花的那几分钟。我个人做表结构评审时，会对着information_schema.COLUMNS把每个表扫一遍，专门查VARCHAR(255)滥用的、时间字段没有索引的、金额字段用了DOUBLE的，这些问题在业务还小的时候整改成本最低。

最后再分享一个小习惯：建表时不光写类型，一定要写COMMENT。类型只是机器的约束，注释是给未来的自己和其他同事的解释。一个TINYINT不写注释，三个月后没人知道它代表什么；写了注释，状态机的含义一目了然。类型选对 + 注释写清，MySQL 表结构设计里一半的坑就提前避开了。