做后端开发的这几年我见过太多人在建表时把注意力放在字段注释上却对 MySQL 数据类型选型一笔带过。等线上出了性能问题、精度问题、主键溢出问题再回头看那段 DDL才意识到字段类型根本不是“随便选一个能存就行”的小事。这篇文章就把常见的 MySQL 数据类型完整过一遍包括数值、字符串、日期时间、JSON、二进制这些类型同时把我在实战里踩过的坑和总结出的选型经验一起写出来方便新手直接照着避坑也方便老开发回查某些冷门知识点。1. 为什么数据类型选型比建表更值得花时间1.1 一次 INT 溢出让我彻底改变看法先说一个我印象特别深的案例。某老项目的订单流水表主键用的是 INT一开始谁也没觉得有问题。后来因为一次误操作项目组从备份里恢复了部分数据自增计数器也被一起还原到了比较早的值。本来就只是数据恢复但接口层又允许调用方传入主键字段结果并发量一上来主键直接被推到了 INT 的上限 2147483647。从那一刻开始所有插入都报主键冲突整个订单系统停了将近一小时。这件事的根子就在建表那行id INT PRIMARY KEY AUTO_INCREMENT上。如果当初用的是 BIGINT或者至少对数据量有预期就不会用一次线上故障来交学费。数据类型选的不是“当下能不能存下”而是“未来三五年、数据量涨几十倍之后还有没有坑”。1.2 字段类型不一致会让一条 JOIN 慢十倍除了存储容量问题类型选得不一致还会悄悄影响 SQL 执行计划。我帮人排查过一个慢查询两张表关联字段一个是 INT另一个是 VARCHAR。按业务设计这两个字段应该完全对应但建表时负责的人没沟通好一张表存的是数字字符串。结果 JOIN 时 MySQL 要对 VARCHAR 列做隐式类型转换索引无法正常走优化器选择了全表扫描。报表本来一秒钟出结果后来变成几十秒甚至超时。类似情况还有WHERE phone 13800138000这种写法如果 phone 列是 VARCHAR等于在等号左边对字段做了类型转换索引基本就废了。所以跨表关联字段不仅要保证“存的都是同一个业务标识”还要保证数据类型、字符集、排序规则都对齐。1.3 选型本质上是在回答三个问题我后来把数据类型选型归纳成三个问题这一列的真实取值范围是什么比如状态字段永远只有 0、1、2那就用 TINYINT而不是 INT。这一列会不会参与计算、排序、关联参与的话精度和类型一致性必须优先考虑。这一列未来会不会膨胀会的话一开始就留足空间不要指望线上 ALTER TABLE 能轻松完成。这三个问题想清楚大多数类型选择都不会跑偏。2. 数值类型整数、浮点、DECIMAL 的取舍2.1 整数类型全家福MySQL 的整数类型有五种按字节数从小到大分别是 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT。它们之间最核心的区别是存储占用和取值范围。类型字节数有符号范围无符号范围典型用途TINYINT1-128 ~ 1270 ~ 255状态字段、开关、0~9 的小数字SMALLINT2-32768 ~ 327670 ~ 65535端口号、小于 6 万的自增编码MEDIUMINT3-8388608 ~ 83886070 ~ 16777215某些统计计数INT4-2147483648 ~ 21474836470 ~ 4294967295常规业务计数、一般主键BIGINT8-2^63 ~ 2^63-10 ~ 2^64-1大表自增主键、雪花 ID、时间戳毫秒值我的建议很简单普通业务表的自增主键新项目直接上BIGINT UNSIGNED。虽然很多业务表一辈子也到不了 21 亿行但 BIGINT 的额外成本也就是每行多 4 个字节换来的是不用再担心主键耗尽。如果项目里已经有雪花 ID 或者分布式 ID 的需求那更是直接上 BIGINT。状态类字段则尽量用 TINYINT。比如用户状态0 未激活、1 正常、2 封禁TINYINT 一字节足够。别再拿 INT 存状态了纯粹浪费空间。2.2 关于 INT(11) 的历史误会很多老开发都见过INT(11)这种写法于是误以为括号里的数字表示“这个字段最长 11 位”。其实在 MySQL 5.7 及更早版本里数字后面的括号表示显示宽度配合 ZEROFILL 才有“右边补零”的实际效果不影响存储范围和索引性能。MySQL 8.0 已经把整数类型的显示宽度移除了现在你再写INT(11)MySQL 也会忽略这个宽度参数。所以看到网上很多“INT(11) 是什么意思”的讨论可以直接略过。新写的建表语句里不需要再关心显示宽度只需要选对类型本身。2.3 小数别用 FLOAT 和 DOUBLE 存金额FLOAT 和 DOUBLE 是浮点数内部按二进制近似存储。0.1 这种看起来很简单的小数在二进制浮点里其实是个无限循环小数做加减乘除时会产生非常小的误差。普通统计场景这点误差无所谓但金额、费率、余额这类字段一分钱都不能差。多年以前我处理过一个退款金额偶尔差 0.01 的工单最后排查下来就是因为字段用了 DOUBLE。数据库里 1.55 和另一个数相加二进制里并不是精确的 1.55。后来全部改成 DECIMAL问题立刻消失。金额字段最稳的方案有两种用DECIMAL(M, D)例如DECIMAL(12, 2)意思是总位数 12 位小数位 2 位。用整数类型存“分”例如BIGINT存单位分展示时再除以 100。两种都行我个人更倾向 DECIMAL因为查询和报表里直接就是元不容易因为读数转换出问题。DECIMAL 也有边界M 最大是 65D 最大是 30并且 D 不能大于 M。实际业务里一般不会接近这个边界但如果字段定义成DECIMAL(20, 10)这种存储和计算开销也会明显变大所以小数位够用就好别贪多。2.4 UNSIGNED 用不用UNSIGNED 表示无符号只能存非负整数。用在自增主键上可以扩大上限到原来的两倍听起来很香。但有一个实际问题如果一张表的关联字段是BIGINT UNSIGNED另一张表是BIGINTJOIN 时 MySQL 会做一次额外的类型转换甚至可能报 “BIGINT UNSIGNED value is out of range” 这种奇怪的错误。实践经验是要么全表统一用 UNSIGNED要么统一不用。我的默认选择是主键用BIGINT UNSIGNED其余有外键关系的字段也跟着用BIGINT UNSIGNED如果只是普通计数、不做跨表关联那就按有符号类型写简单省事。3. 字符串类型CHAR、VARCHAR、TEXT 和字符集的连锁反应3.1 CHAR 与 VARCHAR 的本质差异CHAR 和 VARCHAR 都用于保存字符串但存储方式完全不同。CHAR(N) 是定长N 表示字符数存进去不够长度时会在尾部用空格填充取出时会自动去掉尾部空格。VARCHAR(N) 是变长除了数据本身还要额外用 1 到 2 个字节记录实际长度所以短字符串上 VARCHAR 反而更费空间。类型存储特点最大长度尾部空格处理CHAR(N)定长按声明长度占空间255 字符取出时去掉尾部空格VARCHAR(N)变长额外存长度前缀受行大小限制一般保留选型经验是长度固定且变化很小的字段用 CHAR。比如 MD5 哈希结果固定 32 个字符UUID 固定 36 个字符状态码固定 6 到 8 位用 CHAR(32)、CHAR(36)、CHAR(8) 都比 VARCHAR 省空间且读取更快。而长度不固定、数据长短差异明显的字段比如昵称、地址、标题用 VARCHAR。3.2 VARCHAR 的实际长度上限不是 255很多人被教程洗脑见到字符串就写 VARCHAR(255)。其实 255 只是一个经典默认值不是唯一选择。VARCHAR 的长度单位是字符但底层存储会按字符集换算成字节。在 utf8mb4 字符集下一个字符最多占 4 字节VARCHAR 在单表行大小 65535 字节的限制下理论上最多只能定义到 16383 个字符左右。如果你直接写 VARCHAR(20000)MySQL 会报 “Column length too big” 之类的错误。所以定义 VARCHAR 时第一原则是“够用就好”。昵称一般 30 到 50 个字符足够标题 100 到 200 个字符足够手机号用 VARCHAR(20) 足够一段评论内容给 VARCHAR(2000) 也够。不要毫无理由地全员 255。3.3 utf8mb4 字符集下的索引长度陷阱字符集对索引长度的影响特别大。utf8mb4 是现在的准标配因为真正的 UTF-8 需要支持 4 字节字符比如 emoji。如果建表时用了 utf8用户昵称里一旦出现 emoji写入就会报错或变成乱码。但 utf8mb4 也意味着每个字符最多占 4 字节。InnoDB 的索引键长度在默认 DYNAMIC 行格式下上限是 3072 字节换算成 utf8mb4 字符就是 768 个字符。旧的 COMPACT 行格式上限是 767 字节换算下来只有 191 个字符。这就是为什么网上大量教程让你把索引列定义成 VARCHAR(191)其实那是旧版本留传下来的习惯。真正容易翻车的是组合索引。比如三四个 VARCHAR(255) 字段一起建组合索引一个 255 在 utf8mb4 下是 1020 字节四个就是 4080 字节直接超过 3072MySQL 会拒绝创建索引。解决思路要么是控制单列长度要么只对其中一部分字段建索引要么用前缀索引。3.4 TEXT 家族和它的隐藏成本TEXT 类型家族包括 TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT能存的数据量依次增大。它们和 VARCHAR 最大的区别在于VARCHAR 的行内存储通常更友好而 TEXT 在数据较大时会被 InnoDB 放到溢出页读取时要额外访问一个数据页性能开销更高。更麻烦的是 TEXT 字段不能有默认值至少旧版本是这样MySQL 8.0.13 之后才支持表达式默认值。以前写content TEXT DEFAULT 是会直接报错的很多人被这个坑过。TEXT 最让人头疼的地方在排序和分组。如果查询里ORDER BY或者GROUP BY一个 TEXT 字段MySQL 可能需要创建临时表数据量一大还会落到磁盘临时表慢查询就这么来的。所以业务上能用 VARCHAR 就别用 TEXT实在要存文章正文、长日志这类内容再上 TEXT 或 MEDIUMTEXT。3.5 ENUM 和 SET好用但改起来很麻烦ENUM 和 SET 可以把一组固定值存成紧凑的内部编号。ENUM 存单值SET 存多值。看似节约空间却有两个容易忽略的问题ENUM 的排序规则是按定义顺序不是按字母顺序。如果你把枚举定义成(a, b, c)排序结果会按 a、b、c 排而不是字典序之外的其他预期。枚举值加一两个选项就需要 ALTER TABLE。比如原来的枚举是(待审核, 已发布)想加一个已删除在改表期间可能锁表而且顺序变动会影响历史数据的映射。我的建议是枚举项非常稳定且数量很少比如订单状态可以用 ENUM只要有一点“以后可能会调整”的预期就老老实实用 TINYINT 存状态码或者 VARCHAR 存常量名把校验逻辑放到应用层。4. 日期时间类型DATETIME 与 TIMESTAMP 的时区纠葛4.1 五类日期时间类型参数速查类型字节数范围说明DATE31000-01-01 ~ 9999-12-31只存日期TIME3-838:59:59 ~ 838:59:59可存时长达 800 多小时YEAR11901 ~ 2155只适合年份DATETIME81000-01-01 00:00:00 ~ 9999-12-31 23:59:59日期加时间不带时区TIMESTAMP41970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 UTC内部存 UTC显示时按系统时区转换YEAR 类型用得很少除非只关心年份。TIME 类型有个特点它表示的是“时间段”而不是单纯的时刻所以范围可以到 838 小时跨天时长也能存。4.2 业务时间用 DATETIME事件时间要慎重选 TIMESTAMPTIMESTAMP 和 DATETIME 在存储上最大的差异是时区处理。TIMESTAMP 内部按 UTC 存储查询时会根据数据库连接或系统时区转换成当地时间DATETIME 则是存什么显示什么不带任何时区信息。看起来 TIMESTAMP 很智能但坑也在这里。我曾经遇到过服务器时区从 UTC 改成东八区之后历史 TIMESTAMP 数据的展示结果整体偏移 8 小时。因为数据在库里没有变但是展示时按新时区做了转换。业务方如果没有意识到这一点很容易理解为“数据错了”。我的选型经验订单创建时间、支付时间、审核时间这些和具体业务发生时刻相关的字段直接存DATETIME写入时用应用层统一确定的本地时间。这样不管服务器时区怎么改数据都不会变。如果业务明确要跨时区协作并且需要自动换算才考虑TIMESTAMP或者干脆用BIGINT存毫秒时间戳由应用层做所有时区转换。TIMESTAMP 还有一个绕不开的 2038 问题。它的最大值是 2038 年 1 月 19 日如果业务设计到 2038 年比如保险、长期合约千万别用 TIMESTAMP 存到期时间。4.3 默认值和自动更新的标准写法日常建表里created_at和updated_at非常常见标准写法是这样的CREATE TABLE order ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;created_at在插入时自动写入当前时间updated_at在每次行数据更新时自动刷新。这个写法在 MySQL 5.6.5 之后才完整支持 DATETIME更早的版本只有 TIMESTAMP 能这样用。如果你维护的是老库可能需要在应用层手动赋值。有一点容易被忽略ON UPDATE CURRENT_TIMESTAMP只有在行数据确实发生更新时才变化如果更新内容跟原值一样MySQL 可能不会触发时间变更。所以不要依赖这列做“是否真的被改过”的判断。4.4 不要在日期列上套函数很多人查询某一天的数据时习惯写成SELECT COUNT(*) FROM orders WHERE DATE(created_at) 2025-01-01;看起来很简单但DATE(created_at)相当于在索引列上做了一次函数运算导致 created_at 上的索引无法使用。数据量小无所谓数据量大就是一次全表扫描。更高效的写法是用区间SELECT COUNT(*) FROM orders WHERE created_at 2025-01-01 00:00:00 AND created_at 2025-01-02 00:00:00;这个写法不仅能用索引语义上也更精确。5. 容易被忽略但很实用的 JSON、二进制与布尔类型5.1 JSON 类型不是用来替代表结构的MySQL 5.7.8 之后提供了 JSON 类型能对存入的文档做语法校验并以二进制格式存储比塞进 VARCHAR 当普通文本强得多。JSON 类型最常见的合理使用场景是那些格式不确定、字段经常增减的扩展信息比如用户配置、第三方返回的原始回调数据。但不要把所有业务字段都塞进 JSONMySQL 的优化器对 JSON 内部字段的支持远不如普通列查询和统计会非常吃力。如果业务确实需要按 JSON 里的某个字段做查询可以在 8.0 里用生成列加索引ALTER TABLE user_ext ADD COLUMN nickname VARCHAR(64) GENERATED ALWAYS AS (profile - $.nickname) STORED; ALTER TABLE user_ext ADD INDEX idx_nickname (nickname);这样既保留了 JSON 的扩展性又让 nickname 可以走普通索引。5.2 二进制类型与 BLOB不要把文件丢进数据库BINARY 和 VARBINARY 可以理解为“二进制版的 CHAR 和 VARCHAR”。如果你要存哈希值、摘要签名这类二进制数据用 VARBINARY 比 VARCHAR 更合适因为不需要字符集转换存储也更紧凑。BLOB 家族包括 TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB能存二进制大对象。但我的建议很明确不要把图片、音视频文件本身存进数据库。把文件放到对象存储或文件服务器数据库里只保存路径和 URL这是成本最低、性能最好的方案。数据库里塞大二进制块备份恢复、冷热分离都很难做。5.3 IP 地址用整型或二进制存一个隐藏很深的选型点是 IP 地址。很多表喜欢用 VARCHAR(15) 存 IPv4能存但排序时不方便还占空间。IPv4 本身就是 32 位整数完全可以存进 INT UNSIGNED配合INET_ATON和INET_NTOA做转换INSERT INTO login_log (user_id, client_ip) VALUES (1, INET_ATON(192.168.1.1)); SELECT INET_NTOA(client_ip) FROM login_log;IPv6 需要 128 位用BINARY(16)存配合INET6_ATON和INET6_NTOA。5.4 BOOLEAN 其实就是 TINYINT(1)MySQL 里的 BOOL 和 BOOLEAN 只是 TINYINT(1) 的别名没有独立的布尔类型。TRUE 和 FALSE 本质就是 1 和 0。用 BOOLEAN 写 DDL 是可以的但你要知道底层存的是什么。如果将来字段要做索引直接建 TINYINT(1) 索引更直白。6. 选型三原则和一张可以直接抄的建表模板6.1 三条能减少 80% 类型问题的原则第一存储取最小单位。能存下就够越小空间占用越低索引页能放下更多行扫描越高效。比如设备类型用 TINYINT 不用 INT排序分数用 SMALLINT 不用 BIGINT。只有自增主键这种持续增长且难以预测的字段才建议一上来就给足空间。第二关联字段类型严格一致。两张表做 JOIN 的字段必须保证类型、字符集、排序规则完全一致哪怕业务字段语义一样类型不一样也会引发隐式转换。第三能 NOT NULL 就 NOT NULL。存储 NULL 需要额外的空值标记索引处理 NULL 也更复杂应用层还要多写判空逻辑。大部分业务字段都可以用默认值代替 NULL比如状态默认 0计数默认 0字符串默认空串。6.2 场景速查表业务字段推荐类型理由自增主键BIGINT UNSIGNED容量大未来不易耗尽用户状态TINYINT1 字节足够状态可枚举金额DECIMAL(12, 2) 或 BIGINT 存分精度可靠手机号VARCHAR(20)保留前缀需求不参与运算用户昵称VARCHAR(50)长度够用存储紧凑评论内容VARCHAR(2000) 或 TEXT看数据量合理选择文章正文MEDIUMTEXT文档型长文本创建时间DATETIME不随服务器时区变化消息时间戳BIGINT 毫秒跨时区灵活IP 地址INT UNSIGNED 或 BINARY(16)紧凑且排序高效6.3 一条可复用的评论表 DDL下面这条 DDL 基本覆盖了前面讲过的所有原则字段并不多但每列都说明了类型依据CREATE TABLE comment ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键, user_id BIGINT UNSIGNED NOT NULL COMMENT 用户ID和用户表类型保持一致, topic_id BIGINT UNSIGNED NOT NULL COMMENT 内容ID, content VARCHAR(2000) NOT NULL DEFAULT COMMENT 评论内容, status TINYINT NOT NULL DEFAULT 0 COMMENT 0待审核 1已发布 2已删除, like_count INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 点赞数, client_ip INT UNSIGNED DEFAULT NULL COMMENT IPv4用INET_ATON转换, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, PRIMARY KEY (id), KEY idx_topic_status (topic_id, status), KEY idx_user_id (user_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT用户评论表;这条表里主键用 BIGINT关联字段类型严格对齐状态用 TINYINT长文本用 VARCHAR(2000) 而不是 TEXTIP 用 INT UNSIGNED时间统一 DATETIME字符集选 utf8mb4。整套设计拷到大部分业务场景里都不会有太大问题。最后分享一个小习惯我每次接到新项目都会先执行一遍EXPLAIN看线上慢查询重点看key_len和type两列。一旦发现key_len异常偏大或者关联查询出现了不该有的全表扫描先去对比两张表的列定义多半能抓到类型不一致的问题。这个排查方法帮我省下的回滚次数远比当初多花几分钟思考字段类型的时间多得多。数据类型这件事真的值得在建表的那一刻多较真一下。