聊到 MySQL很多人第一反应是 SQL 怎么写、索引怎么建、事务怎么开却常常忽略最底层的数据库设计基础——数据类型。它藏在每张建表语句里看起来无非是 INT、VARCHAR、DATETIME 几个关键字但选错一个字段的类型轻则多占几个 GB 磁盘重则让一条明明有索引的 SQL 变成全表扫描。这篇文章我会按 MySQL 官方对数据类型的分类把数值、字符串、日期、JSON 等类型的区别和选型思路拆开讲也会分享几个我在实际业务里踩过的类型转换和索引失效的坑。适合刚接触 MySQL 的同学系统入门也适合写过不少 SQL 但没时间回头补数据库基础的同学查漏补缺。不管你是准备面试还是正在优化线上表结构这部分内容都值得花十分钟读完。1. 为什么数据类型是 MySQL 设计的基石1.1 从一张建表语句看起先看一个最常见的用户表定义这是我在项目中反复推荐的示例结构CREATE TABLE user ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, username VARCHAR(50) NOT NULL COMMENT 登录名, nickname VARCHAR(50) NOT NULL COMMENT 昵称, age TINYINT UNSIGNED DEFAULT NULL COMMENT 年龄, gender TINYINT NOT NULL DEFAULT 2 COMMENT 0女 1男 2未知, balance DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 账户余额, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci;你看每一行都有讲究。id 用 INT UNSIGNED而不是 INT 或 BIGINT是因为用户量大概率到不了 42 亿用正数吃掉负数那部分空间就浪费了。username 用 VARCHAR(50) 而不是 TEXT是因为登录名是短数据且要建唯一索引TEXT 没法直接做完整索引查询也会慢一大截。balance 用 DECIMAL 而不是 FLOAT是因为金额不能接受精度损失。一张表是否好用从建表语句就已经注定了。那些上线后跑不动的慢查询绝大多数不是 SQL 写得不对而是字段类型从一开始就埋下了隐患。1.2 选错类型的代价存储空间与索引效率数据类型的第一个直接影响是存储空间。InnoDB 的数据页默认是 16KB同一个页里能放多少行取决于每一行占多少字节。行越宽页能容纳的行数越少则扫描全表时需要读的页越多内存缓存命中率也会下降。举个例子一张 1 亿行的表主键如果从 INT(4字节) 改成 BIGINT(8字节)每行多个 4 字节如果有 3 个二级索引每个二级索引叶子节点都要冗余一份主键值那这 4 字节会被复制 3 次共增加约 1.2GB 的存储还拖慢写入速度。数据类型的第二个影响是索引效率。BTree 的高度往往决定一次查询要读几层节点而节点能存多少个键取决于键的大小。同样的页如果索引键是 VARCHAR(191) 和 VARCHAR(64)前者占的空间多树的扇出就低层数可能更高随机 IO 就更多。虽然 3 层和 4 层之差看着不大但每层都是一次磁盘 IO在高并发下就是量级差异。还有很多人会犯的错给短字符串用 CHAR(255)。CHAR 是定长存 5 个字符也占 255 个字符的存储空间在 utf8mb4 下就是 1020 字节这对 InnoDB 的单行长度限制和缓冲区都是灾难。而同样的内容用 VARCHAR(50)实际只存 20 字节左右外加长度前缀。建表时少写一个数字运行时多付几倍代价。1.3 数据类型与存储引擎的适配MySQL 5.7 时代很多人还在 MyISAM 和 InnoDB 之间纠结到了 8.0MyISAM 基本被官方边缘化默认引擎固定为 InnoDB。但理解存储引擎与数据类型的关系仍然有价值InnoDB 是聚簇索引主键决定了数据行的物理排列所以主键类型选择比 MyISAM 更敏感MyISAM 是堆表行数据独立存放主键选择对物理存储的影响没那么大。另外InnoDB 对 TEXT/BLOB 这类大字段如果一行的数据长度超过阈值会触发溢出页存储主键页只在原地留一个指针。滥用 TEXT 会让主键页频繁分裂、缓冲池命中率下降这也是为什么我建议能用短字符串解决的问题就别上 TEXT。还有一个容易忽略的点NULL 值。MySQL 的每一行都有一个 NULL 位图来标记哪列为 NULL所以 NULL 并不像很多人想的那样“不占空间”。而且使用 NULL 的列在查询条件、索引统计、COUNT 等操作上都会更麻烦。实际操作中我更倾向于所有字段都 NOT NULL再给一个默认值用 0、空字符串或默认时间来表示“无”除非业务非要区分“空字符串”和“没填值”。2. MySQL 数据类型全景拆解2.1 数值类型整数和浮点数别再看走眼数值类型是最好理解的但也是出错最多的。先看整数类型字节有符号范围无符号范围TINYINT1-128 ~ 1270 ~ 255SMALLINT2-32768 ~ 327670 ~ 65535MEDIUMINT3-8388608 ~ 83886070 ~ 16777215INT4-2147483648 ~ 21474836470 ~ 4294967295BIGINT8-9223372036854775808 ~ 92233720368547758070 ~ 18446744073709551615选整数类型的核心原则只有一句话按业务真实边界选最小够用的类型。状态码能用 TINYINT 就别用 INT主键如果预估过不了一千万就用 INT别一上来 BIGINT。另外很多教材里写的 INT(11)、TINYINT(4)这个“显示宽度”在 MySQL 8.0 里基本已经是历史遗留概念它不限制存储大小也不要指望它能帮你约束输入范围。字段的取值范围由类型字节数决定由应用层校验。浮点数场最经典的问题FLOAT/DOUBLE 用于金额。FLOAT 占 4 字节约为 6~7 位有效数字DOUBLE 占 8 字节约为 15~16 位有效数字。但它们都是二进制近似存储0.1 0.2 永远不等于 0.3。所以金额、汇率、税率这些对精度敏感的数据必须用 DECIMAL(M,D)。DECIMAL(10,2) 表示总位数 10 位小数 2 位整数部分 8 位属于定点数按字符串或压缩十进制存储计算精确。代价是它比整数更占空间、计算也慢一些但是钱的事宁可慢不可错。还有一个常见类型 BOOLEANMySQL 里其实只是 TINYINT(1) 的别名你建表写 BOOLEAN它自动变成 TINYINT(1)用 1/0 表示真/假并没有真正的布尔类型。2.2 字符串类型CHAR 和 VARCHAR 的经典博弈字符串是日常最常用、但也最容易“随手一写”的类型。核心区别CHAR 是定长字符VARCHAR 是变长字符。CHAR(32) 永远占用 32 个字符的存储空间适合存 MD5、UUID 这类固定长度的散列值VARCHAR(50) 根据实际内容占用空间但需额外 1~2 字节记录长度适合用户名、标题、备注等长短不一的字段。在 utf8mb4 字符集下一个汉字最多占 4 字节。整表行最大 65535 字节所以 VARCHAR 的理论上限不是 255而在 16383 字符左右16383 * 4 2 ≤ 65535。但如果你要给这个字段建索引还得受限 InnoDB 的索引键长度一般默认最多 3072 字节utf8mb4 下单列索引上限就是 768 个字符实际很常见的经验值是 VARCHAR(191) 或 VARCHAR(128)因为 191 * 4 764 字节正好小于 767 字节的历史兼容上限。新版本虽然放宽了但你真没什么理由把索引字段设得比 191 更大。TEXT 和 BLOB 也要说。TEXT 存字符BLOB 存二进制它们都有 TINY/MEDIUM/LONG 前缀最多能到 4GB。但这类型最大的问题是不能设置默认值不能像普通列那样直接完整加索引只能加前缀索引在行内可能被溢出到外部存储。所以能不用尽量不用。真要存长文本单独拆一张子表主表只存摘要或外键往往是更好的方案。字符集和排序规则则决定了比较和排序结果utf8mb4_0900_ai_ci 是 8.0 默认不区分大小写和重音如果需要区分用 utf8mb4_bin。2.3 日期时间类型DATETIME 与 TIMESTAMP 不是随便选日期时间有四种常用类型DATE、TIME、DATETIME、TIMESTAMP还有一个 YEAR。DATETIME 和 TIMESTAMP 是最容易纠结的。DATETIME 占 8 字节范围是 1000-01-01 00:00:00 到 9999-12-31 23:59:59它不依赖时区存进去是什么就取出什么。TIMESTAMP 占 4 字节范围到 2038 年它存储的是从 1970-01-01 00:00:00 UTC 到当前时间的秒数写入时 MySQL 会根据会话时区把业务时间转成 UTC 存储读取时再转回会话时区。实操建议如果业务只需要记录时间点且所有服务器都在同一时区DATETIME 是最省心的选择不用担心时区换算导致的值漂移。如果系统要面向全球用户需要按用户时区展示那 TIMESTAMP 或 BIGINT 存 Unix 时间戳更合适。很多人拿 VARCHAR 存时间“2024-06-01 12:00:00”这在我看来是最该改掉的行为字符串时间无法用 DATE_SUB、DATE_FORMAT 等函数排序也好、范围查询也罢全部会被类型转换拖累索引也形同虚设。另外建表时给 created_at 设置DEFAULT CURRENT_TIMESTAMP给 updated_at 设置DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP是 MySQL 帮我们省维护成本的默认姿势。注意 DATETIME 和 TIMESTAMP 都可以使用这两个语法。2.4 特殊类型ENUM、SET、JSON 和空间类型ENUM 是 MySQL 里很有特色但争议很大的枚举类型。它底层用 1~2 字节整数存储定义时按顺序编号比如ENUM(待支付,已支付,已取消)实际插入字符串底层存的是整数。好处是存储空间极小语义清晰坏处是它把取值约束写死在表结构里一旦业务需要加新状态只能 ALTER TABLE 修改枚举定义非常不灵活。更隐蔽的坑是 ENUM 排序是按定义顺序不是按字符串顺序很多人在这里翻车。SET 和 ENUM 类似但它是集合类型最多可以存 64 个成员内部按位图存储适合一个字段里要勾选多个选项的场景比如“爱好篮球、足球、游泳”。但它同样有改结构难的问题而且取出后要自己做位运算解析业务代码稍复杂。JSON 类型从 MySQL 5.7 开始提供8.0 之后做了很多优化内部以二进制格式存储自动校验格式。JSON 列本身不能直接建索引常用做法是建虚拟列从 JSON 中提取某个字段生成一个新的虚拟列再在这个虚拟列上加索引。空间类型有 GEOMETRY、POINT、LINESTRING、POLYGON主要用于地理位置和地图业务日常项目接触不多需要时再查文档即可。3. 实操建表时如何正确选择数据类型3.1 一个订单表从 0 开始的选型理论说再多不如跟着走一遍完整建表流程。我现在要建一张订单核心表先列出业务需求订单号全局唯一、用户 ID 关联 user 表、金额要精确到分、订单状态有多个、支付时间可空、订单过期时间必填。我的建表语句如下CREATE TABLE order ( order_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL, user_id INT UNSIGNED NOT NULL, total_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00, pay_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00, status TINYINT NOT NULL DEFAULT 0 COMMENT 0待支付 1已支付 2已发货 3已完成 4已取消, pay_time DATETIME DEFAULT NULL, expire_at DATETIME NOT NULL, source TINYINT NOT NULL DEFAULT 0 COMMENT 渠道 0网页 1App 2小程序, PRIMARY KEY (order_id), UNIQUE KEY uk_order_no (order_no), KEY idx_user_id (user_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单表;一步步解释。order_id 是内部主键用 BIGINT UNSIGNED订单表比用户表更容易上亿INT 到 21 亿虽然也够但从长期考虑取更大值代价只是每个索引多几个字节可接受。order_no 是外部业务号可能有其他系统同时生成需要唯一所以给它唯一索引长度设 32 是因为常见雪花 ID 字符串长度在 20 位左右加一些业务前缀也不会超。user_id 必须和 user 表的主键INT UNSIGNED保持一致这是 join 走索引的前提。金额统一 DECIMAL(12,2)大型订单金额也不会超过百亿够了。status 用 TINYINT不用 VARCHAR 和 ENUM理由在后面案例里细说。pay_time 允许 NULL因为没支付就是没支付区分于默认零值。expire_at 用 DATETIME 而不是DATETIME NULL DEFAULT NULL因为必须有过期时间。这样的表结构无论做统计、排序还是 join类型对得非常齐索引能稳定命中也不会有隐式转换的隐患。3.2 经验法则这些选型规则我用了五年以下几条是我每次评审建表语句时都会对照的清单基本能覆盖 90% 的场景。能用整数不用字符串字符串在存储空间、比较速度、索引效率上全面落于下风。整数按业务边界选最小类型状态 0~255 用 TINYINT年龄用 TINYINT主键用 INT/BIGINT不要全部 BIGINT 一把梭。金额、精确小数一律 DECIMAL不要用 FLOAT/DOUBLE。短字符串优先 VARCHAR定长散列值用 CHAR。VARCHAR 长度别随便写 255够用就好。日期时间用 DATE/DATETIME/TIMESTAMP不要用 VARCHAR 模拟否则无法参与日期函数计算。状态字段优先 TINYINT COMMENT枚举值稳定、量少时才用 ENUM。大文本和 BLOB 慎用先想清楚是不是必须放在主表里。尽量所有字段 NOT NULL并给 DEFAULT。NULL 会让索引统计更复杂也让应用代码多出一堆判空逻辑。全库统一用 utf8mb4 字符集和统一排序规则避免 join 时字符集不一致导致隐性转换。这些规则看起来保守但保守方案往往最稳定。线上出过太多事故都是因为“灵活”的字段设计比如一个状态字段一会儿存字符串、一会儿存数字最后查询那叫一个痛苦。3.3 用 SHOW CREATE TABLE 与 INFORMATION_SCHEMA 复核建完表不是结束我要提醒你养成复核的习惯。最直接的命令是SHOW CREATE TABLE order\G它会列出最终实际建表语句包括 MySQL 对类型自动做的调整比如 BOOLEAN 会显示为 tinyint。对于排查“为什么我建的类型不对”这个命令比任何工具都可靠。需要批量看表结构时我习惯查 INFORMATION_SCHEMA.COLUMNSSELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, CHARACTER_SET_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA DATABASE() AND TABLE_NAME order;这样能把每个字段的类型、是否可空、默认值、字符集一次拉出来。检查时重点看三点一是 join 字段两侧类型是否一致二是字符集是否全库统一三是有没有应改成整数但仍是 VARCHAR 的字段。用 EXPLAIN 验证 SQL 时如果看到 type 是 ALL或者 rows 远大于预期优先怀疑类型或者隐式转换。4. 数据类型转换的坑与排查4.1 隐式转换为什么索引在你面前却用不上这是我见过最多的慢查询原因之一。MySQL 有一个规则如果比较的双方类型不一致它会把其中一个转换为另一个的类型转换发生在哪个对象上决定了索引是否失效。举个例子phone 字段是 VARCHAR(20)有索引但你的查询是这样SELECT * FROM user WHERE phone 13800138000;右边的查询参数是整数MySQL 会把 phone 列转换成整数再比较。列上发生了隐式转换函数一样的作用索引自然失效变成全表扫描。而正确写法是SELECT * FROM user WHERE phone 13800138000;另一个常见场景是表 join左表 user.id 是 INT UNSIGNED右表 order.user_id 是 VARCHAR即使数据内容一样MySQL 也会在每一行做类型转换最终 join 性能瞬间崩掉。老规矩同一种含义必须用同一种类型而且尽量用整数类型。你可以在 EXPLAIN 的 extra 里看到一些提示也可以直接看 key 是否为 NULL、type 是否为 ALL。排查思路是检查查询条件值是否加了引号检查 join 字段类型是否一致检查排序字段是否在列上做了函数操作。4.2 CAST/CONVERT把类型控制权拿回自己手里当确实需要类型转换时要明确地用 CAST 或 CONVERT不要依赖隐式转换。语法很好记CAST(expr AS type) CONVERT(expr, type)比如把字符串 123 转成整数SELECT CAST(123 AS UNSIGNED); SELECT CONVERT(2024-06-01 12:00:00, DATETIME);此外 CONVERT 还支持字符集转换SELECT CONVERT(中文 USING utf8mb4);但在索引字段上使用 CAST/CONVERT同样会让索引失效。所以更稳妥的做法是只转换查询参数不转换被查询的列。比如一个数字存进了 VARCHAR 字段你查询时可以写成SELECT * FROM order_no WHERE order_no CAST(202401010001 AS CHAR);这里把整数参数转成字符串而不是对列做转换索引就能正常使用。不过在实际业务里更干脆的做法是把 order_no 建表时就定义为 VARCHAR查询条件就用字符串常量少一层弯弯绕绕。4.3 问题速查表现象原因解法中文乱码表是 latin1连接字符集不一致统一 utf8mb4检查SET NAMES utf8mb4日期排序不对日期字段用 VARCHAR 存储ALTER TABLE 改成 DATETIME/TIMESTAMP有索引却全表扫描隐式类型转换、列上函数比较类型统一避免对列用函数金额对不上DECIMAL 被误用成 FLOAT改用 DECIMAL必要时重建表data truncated for column字符串超过了 CHAR/VARCHAR 长度加长或改用 TEXT 前先评估业务主键自增溢出主键类型不够大改为 BIGINT UNSIGNED注意停写窗口排查时建议先跑一遍SHOW WARNINGS很多插入或更新报错都会给出具体原因。再加上通常会用EXPLAIN配合基本能快速定位。5. 真实案例三个高频场景的类型设计教训5.1 主键BIGINT 自增还是 VARCHAR 业务键有一段时间流行用业务编号做主键比如订单表用 order_no 当主键用户表用“用户唯一标识”当主键。理由是“自然有序”“查询不用回表找主键”。但我要泼一盆冷水。自增整数主键在 InnoDB 里非常省心插入走顺序数据页自动填充页分裂概率低主键只占 4~8 字节二级索引冗余主键的开销也小。VARCHAR 业务键则完全相反内容随机插入时可能导致随机位置写入页分裂和碎片会变多长度通常 20~32 字节二级索引都会把这个大主键复制一遍索引空间急剧膨胀。而且业务标识一旦发生规则调整主键根本改不动。有一种折中做法让我非常推荐表内部仍用自增 BIGINT 主键业务唯一标识如 order_no、tid、uuid 单独建唯一索引。这样既有全局唯一性又不破坏 InnoDB 的聚簇结构。如果不想暴露自增 ID还可以把外界看到的标识用 UUID 字符串内部用UUID_TO_BIN()转成 BINARY(16) 存储节省空间。5.2 状态字段TINYINT、ENUM 还是 VARCHAR状态字段几乎每张表都有但选型的坑特别多。我曾经在一张订单表看到 order_status 是VARCHAR(20)里面存着“待支付”“已支付”“已发货”“已完成”看起来语义清晰一查慢 SQL 全在这张表上。原因不复杂字符串状态占用空间大比较要走字符集规则最可怕的是排序列性能差。后来改表时我论证过三种方案。ENUM 的优点是存储紧凑底层整数 1~2 字节读出来自动映射成字符串还能在数据库层限制非法值。但它把枚举定义写死在表结构里每次加状态都要 ALTER TABLE。一线的交付环境经常求稳一个 ALTER TABLE 可能要审批、灰度非常痛苦。VARCHAR 的好处是状态码可以自定义比如PAID、SHIPPED加新状态不用改表。但存储冗余、性能和索引都不占优在高频状态字段场景不推荐。最稳妥的还是 TINYINT COMMENT。状态值从 0 开始排加个注释写明每个数字代表的含义例如COMMENT 0待支付 1已支付 2已发货 3已完成 4已取消。加新状态只需要在注释里追加也可以配合字典表做翻译。TINYINT 只有 1 字节排序走整数序索引性能好还不用担心字符串拼写错误。唯一的缺点是人眼看不直观但查询结果交给应用层枚举解析就好数据库里存数字本来就不是给终端用户看的。5.3 IP 地址别再用 VARCHAR(15) 硬扛IPv4 地址形如192.168.1.1很多人顺手用 VARCHAR(15) 存。这样存没问题但在做范围查询、按 IP 段统计时字符串比较不符合 IP 的数值逻辑比如192.168.10.1会排在192.168.2.1前面很容易出问题。更合理的方案是用INT UNSIGNED存储 IPv4。MySQL 提供了两个函数INET_ATON(192.168.1.1)把字符串转成整数INET_NTOA(3232235777)把整数转回字符串。这样只占 4 字节而且可以用BETWEEN做数值范围查询SELECT * FROM access_log WHERE ip BETWEEN INET_ATON(192.168.1.0) AND INET_ATON(192.168.1.255);注意这里是对参数做转换不是对列做函数所以 ip 列上的索引可以正常使用。如果业务要展示原始 IP可以在应用层把整数转回字符串没必要用 VARCHAR 去存冗余。IPv6 建议使用 VARBINARY(16)配合应用层转换工具既紧凑又能排序。额外提醒一句如果你已经有一张用 VARCHAR 存 IP 的大表想改类型不要直接ALTER TABLE强制转可以增加一个新列写脚本分批迁移校验无误后再切换读写线上数据库最忌讳一把梭。写到这里我又想起刚参加工作时的第一个教训给订单表用 VARCHAR 存金额统计图看着正常一求和总有几分钱对不上最后排查半天发现是浮点转字符串再转数字发生了精度丢失。从那以后我特别重视数据库建表时的类型设计这可能比写一千条 SQL 技巧都更能决定一个系统的健康度。如果读到这里你准备动手检查自己的表建议先看三处字段有没有把数值类型存成 VARCHAR日期列是否真的用了 DATETIME以及每个WHERE/JOIN条件右侧的类型和列是否一致。这三处改完性能通常能提升一个量级。