1. 为什么数据类型选型是建表的核心先讲个我印象挺深的线上事故。那时候刚接手一个电商项目订单表里的金额字段用的是FLOAT刚开始数据量小的时候什么问题都没有等跑了一个多月财务那边对账怎么都对不上最后查出来就是金额精度漂移。订单金额明明是0.1存到数据库里变成了0.100000001490116累计下来差了好几块钱。排查到后来发现问题就出在最不起眼的地方——建表时候随手选的FLOAT。从那以后我遇到任何一个建表需求都会先把数据类型这块掰扯清楚。MySQL数据类型这件事看起来好像是个基础得不能再基础的入门知识什么INT、VARCHAR、DATETIME背一遍好像就会了。但实际去翻生产库的时候就会发现各种奇奇怪怪的设计到处都是金额用FLOAT、日期用VARCHAR、状态用VARCHAR(100)存中文、手机号用INT存导致前面0丢失。每一个坑背后都是当初建表时“随手一选”留下的债。这篇内容我打算把MySQL数据类型的核心逻辑讲透包括每一类类型的适用场景、底层存储逻辑、容易踩的坑以及一套可以直接套用的字段设计方案。适合正在学MySQL的入门者、写过一段时间但没系统梳理过的开发以及准备面试需要把数据类型这块讲出深度的人。我见过很多面试者能背出INT占4个字节、VARCHAR最大65535个字节但问他“你线上订单金额用DECIMAL(10,2)还是FLOAT”就答不上来。所以这篇不是给你背知识点的而是告诉你每个类型到底该怎么用以及用了以后会发生什么。2. 数据类型全景拆解三类核心类型的选型逻辑MySQL的数据类型可以粗略分成三大类数值型、字符串型、日期时间型。其他像二进制类型、空间类型、JSON类型属于特定场景才会用到实际业务里90%以上的表都绕不开前面这三类。所以重点先讲这三类但二进制和JSON这种“隐藏选手”我也会提一下毕竟现在JSON字段在业务里的出现频率越来越高了。2.1 数值类型整数、小数与显示宽度的坑数值类型是坑最多的地方也是最容易被忽略的地方。先看整数这一族TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT它们占用的字节数分别是1、2、3、4、8对应的取值范围差异巨大。TINYINT最大能存127有符号或255无符号一般用来做状态位、布尔值这类小字段。SMALLINT最大值32767适合存枚举值、端口号这类小范围数字。MEDIUMINT在MySQL里比较特殊占了3个字节范围到16777215用的场景不多但某些中间表统计数字可以用它省点空间。INT是绝对的主力范围正负21亿一般业务主键、数量、计数都够用。BIGINT就是大数场景雪花ID、订单号这类必须用它。实际使用中我建议记住一个简单规则能用小类型不用大类型。一个TINYINT才1个字节一个BIGINT要8个字节如果一张表几千万行这个差距就是几十MB到几百MB的存储差异还有索引扫描时IO的开销。但这个规则也不要走极端为了省空间把一个未来可能破21亿的业务主键设计成INT而不设计成BIGINT那就是给自己埋雷。关于INT的“显示宽度”INT(5)、INT(11)这种写法有非常多的误解。INT(5)里面的5不是限制最大值为99999它只是显示宽度配合ZEROFILL属性时如果数值不足5位会用0补齐比如00001。它不影响实际存储范围INT(5)能存的最大值仍然是2147483647。MySQL 8.0.17开始已经废弃了整数类型的显示宽度语法所以新库新表就别写INT(11)这种了直接写INT就好旧代码里见到了也可以顺手清掉。小数这块是重灾区。FLOAT和DOUBLE是浮点数存储的是近似值精度会漂移绝对不能用来存金额、库存、费率这种对精度敏感的数据。DECIMAL也叫NUMERIC是定点数存储的是精确的十进制值适合金额计算。DECIMAL(10,2)表示总共10位有效数字小数点后保留2位也就是整数部分最多8位。这里注意一下DECIMAL的精度越高占用的存储空间越大实际设计时要根据业务量级来定不要动不动就DECIMAL(20,6)很多场景根本用不到那么高的精度。一个值得单独说的是BIT类型。BIT(1)可以存0或1适合布尔值BIT(8)可以存一个字节的位掩码适合权限这类需要位运算的场景。但实际业务中我用BIT用得很少因为Java、PHP这类语言的驱动对BIT的映射处理不统一读出来经常是二进制字符串容易引发隐式转换问题。大部分时候用TINYINT(1)其实现在也不用写宽度或者TINYINT存0和1更省心。2.2 字符串类型CHAR与VARCHAR、TEXT与ENUM的取舍字符串是业务表里最常用的类型但也是知识盲区最密集的地方。先解决最基础的CHAR还是VARCHAR的问题。CHAR(N)是定长字符串长度固定为N个字符不足的部分用空格填充检索时会去掉尾部空格。VARCHAR(N)是变长字符串实际存储时只占用实际长度的字节另外还需要1到2个字节记录长度信息。看起来VARCHAR全面优于CHAR但实际不是这样。CHAR的性能优势在于定长MySQL不需要额外去解析长度信息随机IO和行存储的页利用率更稳定。如果字段长度几乎固定比如身份证号、手机号、MD5值、固定长度的编码用CHAR更合适。如果字段长度差异大比如用户名、文章标题、地址用VARCHAR能显著节省空间。VARCHAR里面的N是字符长度不是字节长度这一点很多人会搞混。一个VARCHAR(255)可以存255个英文字母也可以存255个中文汉字前提是字符集是utf8mb4中文每个字符占4个字节。行存储的最大字节数是65535所以如果你用utf8mb4字符集一个VARCHAR列实际最多能存大约16383个字符65535除以4而且这个限制是整个行的所有变长列共享的还要减去变长列表等开销。说到这个就不得不提一个经典的索引场景。在utf8mb4字符集下如果给一个VARCHAR(255)列建索引单个索引键最大字节数是767字节InnoDB早期版本255乘以4刚好超过1020直接超出限制所以会报Specified key was too long错误。这就是为什么很多老经验建议VARCHAR不要超过255的原因之一——超过255在早期MySQL版本里没法直接建索引。现代的MySQL 8.0.12以上版本索引键限制已经放宽到了3072字节但设计时还是要有“一个索引列不要过长”的意识业务上没办法就拆前缀索引。TEXT族和BLOB族属于大对象类型。TEXT存文本BLOB存二进制。它们和VARCHAR最大的区别是TEXT不能有默认值除非显式指定一个表达式默认值MySQL 8.0.13以后支持TEXT需要使用前缀索引TEXT在排序和比较时会有一些限制。实际业务里纯文本内容、JSON字符串、日志详情这种大字段适合用TEXT但要避免把TEXT塞进频繁查询的表中抽到独立的扩展表或者干脆放对象存储里会更好。ENUM和SET这两个类型我的态度是“除非业务场景非常确定否则慎用”。ENUM本质上是一个枚举类型内部存储用整数查询结果会自动转成字符串虽然节省空间但弊端不少改枚举值需要ALTER TABLE排序时按枚举定义顺序而不是字母顺序不同字符集下行为有细微差异如果你的代码里用了MySQL驱动ENUM映射到Java字符串还可能有问题。SET类似它是多选枚举底层用位图存储适合固定、绝不会变化的多选项。我的建议是状态、性别、订单类型这类字段老老实实用TINYINT存编码值在代码里做映射比ENUM灵活得多也不会有那么多边界问题。2.3 日期时间类型DATETIME还是TIMESTAMP日期时间类型有DATE、TIME、DATETIME、TIMESTAMP、YEAR五种。业务里最常用的是DATETIME和TIMESTAMP这俩也经常被拿来对比。DATETIME的范围是1000-01-01 00:00:00到9999-12-31 23:59:59不依赖时区设置存什么就是什么占8个字节。TIMESTAMP的范围是1970-01-01 00:00:01 UTC到2038-01-19 03:14:07 UTC占4个字节存储时会根据会话的time_zone设置将值转成UTC存储查询时再转回当前时区。这个差异带来一个经典问题如果服务器时区设置不对或者连接串里没指定时区TIMESTAMP读出来的时间就会和实际时间差8个小时。选型建议是这样的如果你需要记录一个绝对时间点且不关心服务器时区变换用TIMESTAMP可以节省空间但如果你要存一个业务时间比如“活动开始时间”“下单时间”而且未来系统可能会迁移到不同时区的服务器用DATETIME更稳妥不会因为时区设置不同而产生歧义。MySQL官方和一些云数据库厂商从8.0.19开始建议默认用DATETIME因为TIMESTAMP的2038年问题始终是个坎。再说一个很实用的细节。TIMESTAMP和DATETIME都可以用DEFAULT CURRENT_TIMESTAMP作为默认值也可以在ON UPDATE子句里设CURRENT_TIMESTAMP这样在更新记录时时间字段会自动刷新。这个特性在维护updated_at这类字段时非常方便。MySQL 8.0.13及以上版本还允许DATETIME支持表达式默认值比如DEFAULT (CURRENT_TIMESTAMP INTERVAL 1 DAY)灵活性更高了。3. 实操从零设计一张订单表的字段类型有了前面的类型知识打底这一部分我带你完整走一遍订单表的设计过程。这个案例能覆盖大部分常见字段的类型选择看完直接套用到自己的项目里。3.1 字段设计与类型选择假设我们要设计一张电商订单主表核心字段有主键ID、订单号、用户ID、订单金额、折扣金额、应付金额、订单状态、收货地址、下单时间、支付时间、备注。逐字段来看。主键ID用BIGINT UNSIGNED AUTO_INCREMENT原因很简单单表数据量超过21亿条时INT就不够用了BIGINT直接解决后顾之忧。UNSIGNED是为了把负数空间让给正数让可用的正数范围翻倍。如果你用的是分布式ID方案比如雪花ID那更是必须用BIGINT因为雪花ID本身是19位数字超出INT范围。订单号这个字段很多人会纠结用BIGINT还是VARCHAR。我的习惯是如果订单号全部由数字组成且长度可控就用BIGINT如果订单号可能带前缀字符比如DD202501010001那就用CHAR(32)或VARCHAR(32)。这种“什么时候用什么”取决于业务形态没有绝对标准但一定要给它建唯一索引因为订单号的唯一性是核心约束。用户ID用BIGINT UNSIGNED和用户表主键类型保持一致。这里有个很容易忽略的点如果你用户表主键是BIGINT订单表里存用户ID却用了VARCHAR连表查询时虽然不会报错但会发生隐式转换直接让索引失效慢查询就出现了。金额字段全部用DECIMAL(10,2)。这里的10,2含义是总长度10位、小数2位整数部分最多8位单笔金额最大可以到99999999确实够用。如果你做的是跨境支付、积分系统可能要精确到更多位比如DECIMAL(14,4)但日常电商系统DECIMAL(10,2)足够了。坚决不用FLOAT和DOUBLE存金额这一点上面已经讲过了。订单状态用TINYINT就可以了0待支付、1已支付、2已发货、3已完成、4已取消、5售后中。实际存数字显示文本在代码里做映射。后续如果要加状态代码里加一个枚举就行不用动表结构。收货地址这个字段看起来简单但选型很容易出错。有人说地址可能很长用TEXT吧有人说地址一般一两百字用VARCHAR(255)吧。我实际操作下来收货地址用一个VARCHAR(200)够了多数字段如果不放心就VARCHAR(255)。如果未来要拆省市区应该拆成独立的省、市、区、详细地址字段而不是塞一个超长TEXT。TEXT字段在查询、排序、GROUP BY时都有额外限制能不用就不用。下单时间用DATETIME DEFAULT CURRENT_TIMESTAMP支付时间用DATETIME NULL DEFAULT NULL表示“尚未支付”。这里NULL和DEFAULT NULL是有讲究的。注意DATETIME默认是不允许NULL的如果你不写NULL插入时没给值会报错。有些团队会把“未支付”的支付时间设计成1970-01-01 00:00:00这样可以避免NULL带来的比较问题但这样写语义不清晰我建议还是允许NULL更直白。3.2 完整的建表语句示例把上面的设计串起来建表语句如下CREATE TABLE t_order ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键ID, order_no VARCHAR(32) NOT NULL COMMENT 订单号, user_id BIGINT UNSIGNED NOT NULL COMMENT 用户ID, total_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 订单总金额, discount_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 优惠金额, pay_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 应付金额, status TINYINT NOT NULL DEFAULT 0 COMMENT 订单状态0待支付 1已支付 2已发货 3已完成 4已取消, receive_address VARCHAR(200) NOT NULL DEFAULT COMMENT 收货地址, pay_time DATETIME NULL DEFAULT NULL COMMENT 支付时间NULL表示未支付, 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), UNIQUE KEY uk_order_no (order_no), KEY idx_user_id (user_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci COMMENT订单主表;几个细节展开说一下。ENGINEInnoDB是必须的另一个常见引擎MyISAM不支持事务、不支持行级锁还有表级锁和崩溃恢复问题业务表基本不应该用MyISAM。字符集选utf8mb4而不是utf8因为utf8mb4才是完整的Unicode支持能存emoji和生僻字utf8在MySQL里是utf8mb3的别名已经不建议使用。排序规则utf8mb4_0900_ai_ci是MySQL 8.0默认的大小写不敏感一般够用。如果你需要对某个字段做大小写敏感的查询比如用户名登录可以在查询时用BINARY关键字或单独指定字段的COLLATE。UNIQUE KEY uk_order_no和KEY idx_user_id都是逻辑推导的结果订单号唯一所以建唯一索引用户ID经常用来查订单列表所以建普通索引。索引不是越多越好每个索引都会增加写入成本和存储成本核心查询路径上建索引就够了。3.3 类型变更与ALTER TABLE的注意点表建好之后不是一劳永逸的业务变化经常要改字段类型。ALTER TABLE ... MODIFY COLUMN ...是最直接的改法比如把status从TINYINT改成SMALLINTALTER TABLE t_order MODIFY COLUMN status SMALLINT NOT NULL DEFAULT 0 COMMENT 订单状态;这个操作本身不复杂但要注意的是在大表上执行ALTER TABLE会锁表业务高峰时期执行会导致大量请求阻塞甚至拖垮数据库。常用的解决办法是借助gh-ost或pt-online-schema-change这类工具做在线表结构变更但那是另一个话题了。这里只提醒一件事任何时候改动表结构先备份、先看表大小、先评估业务低峰期不要在生产环境随手敲ALTER TABLE。顺便说一下存储过程里的类型坑。如果你在存储过程中声明变量比如DECLARE amount DECIMAL(10,2);这个变量和表字段做比较、赋值时隐式转换规则同样生效。如果变量类型和字段类型不匹配轻则精度丢失重则报Out of range value错误。存储过程里尽量让变量类型和表字段完全一致别让MySQL帮你“智能转换”那个“智能”往往会反过来坑你。4. 常见问题排查那些年踩过的类型坑类型相关的问题在实际排查中经常会以各种奇奇怪怪的方式冒出来。我整理了一张高频问题速查表后面再逐个展开分析。现象根因解决方案金额对不上用了FLOAT/DOUBLE存金额改为DECIMALWHERE条件查询不走索引隐式类型转换保证字段类型和值类型一致日期差了8小时TIMESTAMP时区设置问题检查time_zone/JDBC连接时区VARCHAR建索引报too longutf8mb4下索引键超长改用前缀索引或缩短字段排序结果不符合预期字符集排序规则不同指定COLLATE手机号前面的0丢了用数值类型存手机号字符串类型存手机号唯一索引允许了重复对NULL列建唯一索引理解NULL语义用空串代替存储过程报Out of range变量类型和字段类型不匹配统一变量类型4.1 隐式类型转换为什么会“吃掉”索引这是我最常被问到的一类问题。举个例子用户在表里有phone字段类型是VARCHAR(20)建了索引。查询写成SELECT * FROM user WHERE phone 13800138000;这里的13800138000在SQL里是一个整数常量MySQL会把phone字段转成数字去比较相当于执行了CAST(phone AS SIGNED)对每个字段值都做一次转换索引自然就没了。解决办法很简单查询字符串加引号SELECT * FROM user WHERE phone 13800138000;这个问题的本质是MySQL在比较不同类型时有一套隐式转换规则数值类型优先于字符串类型日期类型比较特殊。只要一边是数值另一边是字符串MySQL一般会把字符串转成数值。规则本身不是什么秘密关键是你要时刻记住写SQL时字段类型和值类型保持一致不要依赖MySQL帮你转。还有一类比较容易忽略的SELECT * FROM user WHERE id 100主键id是INT等号右边是字符串100虽然这样的SQL也能走索引因为它把字符串转成了数值比较但如果你在连接查询时用错了类型比如a.user_id b.id其中a.user_id是VARCHARb.id是BIGINT那就一定有一个字段会发生隐式转换具体看哪个类型优先级低哪个就吃哑巴亏。排查这类问题最有效的方式是EXPLAIN看type列如果是ALL或者index而没有走ref/eq_ref十有八九就是类型不匹配。4.2 唯一索引与NULL为什么“唯一”却不唯一很多人在唯一索引上被坑过。比如我有一张用户表email字段加了唯一索引但业务上允许部分用户没有邮箱于是插入时email存了NULL执行了两遍同样的插入INSERT INTO user (email) VALUES (NULL); INSERT INTO user (email) VALUES (NULL);结果两条都能成功。原因是在MySQL中NULL不等于NULL唯一索引允许多个NULL值存在。这是个大多数人会忽略的语义细节。解决方案有几个思路。一是字段设计为NOT NULL DEFAULT 把空串当作“没有邮箱”的语义这样唯一索引可以保证最多一条空串记录但如果你允许很多用户没有邮箱这个方案也行不通因为第二条空串插入时就会违反唯一约束。二是改成允许NULL但接受“多个NULL存在”的现状在业务代码里判断时用IS NULL而不是。三是在应用层做唯一性校验但这存在并发问题一般不建议。最合理的看业务语义如果“没有邮箱”是正常状态就用NULL并接受多条如果“邮箱为空只能存在一条”是业务约束就在应用层加锁或用INSERT ... ON DUPLICATE KEY UPDATE来做。4.3 排序结果“不对”字符集和排序规则的影响排序是另一个隐藏雷区。MySQL的排序行为由列的COLLATE决定。默认utf8mb4_0900_ai_ci是大小写不敏感、重音不敏感的排序规则这意味着a和A在排序时被视为相同。如果你希望大小写敏感排序需要给列单独指定COLLATE utf8mb4_bin或者utf8mb4_0900_as_cs。还有中文排序的问题。在utf8mb4字符集下中文排序不是按拼音而是按Unicode码点排序。你执行ORDER BY name看到的“张三”“李四”“王五”排列顺序可能是按编码来的不是按拼音首字母。如果需要按拼音排序可以借助CONVERT(name USING gbk)辅助排序虽然不优雅但确实可用SELECT * FROM user ORDER BY CONVERT(name USING gbk);这个技巧我用了很多年在MySQL 8.0里依然有效。要注意的是CONVERT会阻止这个字段上的索引排序优化所以只适合数据量不大的场景。4.4 日期相差8小时和2038问题时间问题排查起来最让人头疼。TIMESTAMP列在存储和读取时会跟随数据库会话的time_zone设置如果你的数据库时区是SYSTEM而服务器系统时区是UTC就会导致读出来的时间比北京时间少8小时。排查思路从上到下依次检查服务器系统时区、MySQL的time_zone变量、连接串里的serverTimezone参数、应用程序所在机器的时区。一条链路上任何一个环节不一致就会出现“数据库里明明是14:00查出来变成06:00”的情况。更隐蔽的是2038年问题。TIMESTAMP能存储的最大时间是2038-01-19 03:14:07 UTC如果你的业务系统要处理几十年后的日期比如保险合同期限、长期订阅到期时间TIMESTAMP会在2038年到来时直接溢出改为DATETIME是最稳妥的方案。4.5 MySQL 8.0认证插件引发的“连不上”问题这个问题虽然不是数据类型本身但热度很高值得在这里提一嘴。用客户端连接MySQL 8.0时经常会报Authentication plugin caching_sha2_password cannot be loaded也就是热词里那个firedac phys mysql client does not support authentication protocol requested的变种原因是从MySQL 8.0开始默认认证插件从mysql_native_password换成了caching_sha2_password很多老客户端不支持。解决办法有两种一是升级客户端驱动这是首选方案二是把用户改成老插件ALTER USER root% IDENTIFIED WITH mysql_native_password BY your_password;但要注意mysql_native_password在MySQL 9.0里已经被移除所以这不是长久之计最终还是要升级驱动到支持caching_sha2_password的版本。5. 类型选型的几条通用经验最后分享几条我摸爬滚打出来的通用经验既是给新手的避坑指南也是给自己以后review别人建表时的检查清单。第一条所有金额、费率、单价一律用DECIMAL不用FLOAT/DOUBLE。这个规则没有任何例外就算你只是在做一个个人项目也别偷懒否则后面对账、报表、计算均值全都可能出问题。第二条能用数值的别用字符串能用短字符串的别用长字符串。状态用TINYINT不要用VARCHAR(20)存“已支付”这种中文。短字符串这里又是另一个原则能用CHAR(32)存定长编号就不要用VARCHAR(255)存储和性能都比后者好。第三条所有表都建议带上created_at和updated_at这两个时间字段类型用DATETIME默认值用CURRENT_TIMESTAMP。这俩字段不光是审计需要很多业务排查和数据分析都离不开早设计早省事。注意时间字段的默认值和ON UPDATE用法我已经写在上面的建表语句里了直接照抄就行。第四条整型和字符串混合比较时一律加引号。这不算什么高深技巧就是在写SQL时养成好习惯隐式转换问题能从源头避免一大半。第五条谨慎使用ENUM、SET、BIT、空间类型这类特殊类型。它们各有适用场景但灵活性和通用性不如整数和字符串一旦业务变化改类型的成本远高于当初选通用类型。生产环境我用TINYINT和VARCHAR解决95%以上场景剩下5%才考虑特殊类型。第六条如果你在做数据同步或者跨库操作比如热词里那个mysql/sqlserver/postgresql数据库同步软件的场景一定要提前对齐两边的数据类型映射关系。MySQL里的TINYINT(1)在PostgreSQL里可能是BOOLEANMySQL的DATETIME在SQL Server里可能是DATETIME2映射错了数据就乱了。第七条写存储过程或者触发器时变量声明类型一定和表字段类型保持一致。别在存储过程里声明一个INT去接收表里的BIGINT值迟早会溢出。我自己的体会是MySQL数据类型看起来是入门第一课但真正吃透却是在踩了无数坑之后。每次遇到查不出来、排序不对、数据丢了的情况回头一看往往都是类型设计上的一个小失误。希望这篇内容能帮你把这块地基打牢建表的时候多想一步后面就能少加几十个班。 MySQL数据类型选型实战：避开FLOAT精度坑，掌握DECIMAL、VARCHAR与DATETIME
1. 为什么数据类型选型是建表的核心先讲个我印象挺深的线上事故。那时候刚接手一个电商项目订单表里的金额字段用的是FLOAT刚开始数据量小的时候什么问题都没有等跑了一个多月财务那边对账怎么都对不上最后查出来就是金额精度漂移。订单金额明明是0.1存到数据库里变成了0.100000001490116累计下来差了好几块钱。排查到后来发现问题就出在最不起眼的地方——建表时候随手选的FLOAT。从那以后我遇到任何一个建表需求都会先把数据类型这块掰扯清楚。MySQL数据类型这件事看起来好像是个基础得不能再基础的入门知识什么INT、VARCHAR、DATETIME背一遍好像就会了。但实际去翻生产库的时候就会发现各种奇奇怪怪的设计到处都是金额用FLOAT、日期用VARCHAR、状态用VARCHAR(100)存中文、手机号用INT存导致前面0丢失。每一个坑背后都是当初建表时“随手一选”留下的债。这篇内容我打算把MySQL数据类型的核心逻辑讲透包括每一类类型的适用场景、底层存储逻辑、容易踩的坑以及一套可以直接套用的字段设计方案。适合正在学MySQL的入门者、写过一段时间但没系统梳理过的开发以及准备面试需要把数据类型这块讲出深度的人。我见过很多面试者能背出INT占4个字节、VARCHAR最大65535个字节但问他“你线上订单金额用DECIMAL(10,2)还是FLOAT”就答不上来。所以这篇不是给你背知识点的而是告诉你每个类型到底该怎么用以及用了以后会发生什么。2. 数据类型全景拆解三类核心类型的选型逻辑MySQL的数据类型可以粗略分成三大类数值型、字符串型、日期时间型。其他像二进制类型、空间类型、JSON类型属于特定场景才会用到实际业务里90%以上的表都绕不开前面这三类。所以重点先讲这三类但二进制和JSON这种“隐藏选手”我也会提一下毕竟现在JSON字段在业务里的出现频率越来越高了。2.1 数值类型整数、小数与显示宽度的坑数值类型是坑最多的地方也是最容易被忽略的地方。先看整数这一族TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT它们占用的字节数分别是1、2、3、4、8对应的取值范围差异巨大。TINYINT最大能存127有符号或255无符号一般用来做状态位、布尔值这类小字段。SMALLINT最大值32767适合存枚举值、端口号这类小范围数字。MEDIUMINT在MySQL里比较特殊占了3个字节范围到16777215用的场景不多但某些中间表统计数字可以用它省点空间。INT是绝对的主力范围正负21亿一般业务主键、数量、计数都够用。BIGINT就是大数场景雪花ID、订单号这类必须用它。实际使用中我建议记住一个简单规则能用小类型不用大类型。一个TINYINT才1个字节一个BIGINT要8个字节如果一张表几千万行这个差距就是几十MB到几百MB的存储差异还有索引扫描时IO的开销。但这个规则也不要走极端为了省空间把一个未来可能破21亿的业务主键设计成INT而不设计成BIGINT那就是给自己埋雷。关于INT的“显示宽度”INT(5)、INT(11)这种写法有非常多的误解。INT(5)里面的5不是限制最大值为99999它只是显示宽度配合ZEROFILL属性时如果数值不足5位会用0补齐比如00001。它不影响实际存储范围INT(5)能存的最大值仍然是2147483647。MySQL 8.0.17开始已经废弃了整数类型的显示宽度语法所以新库新表就别写INT(11)这种了直接写INT就好旧代码里见到了也可以顺手清掉。小数这块是重灾区。FLOAT和DOUBLE是浮点数存储的是近似值精度会漂移绝对不能用来存金额、库存、费率这种对精度敏感的数据。DECIMAL也叫NUMERIC是定点数存储的是精确的十进制值适合金额计算。DECIMAL(10,2)表示总共10位有效数字小数点后保留2位也就是整数部分最多8位。这里注意一下DECIMAL的精度越高占用的存储空间越大实际设计时要根据业务量级来定不要动不动就DECIMAL(20,6)很多场景根本用不到那么高的精度。一个值得单独说的是BIT类型。BIT(1)可以存0或1适合布尔值BIT(8)可以存一个字节的位掩码适合权限这类需要位运算的场景。但实际业务中我用BIT用得很少因为Java、PHP这类语言的驱动对BIT的映射处理不统一读出来经常是二进制字符串容易引发隐式转换问题。大部分时候用TINYINT(1)其实现在也不用写宽度或者TINYINT存0和1更省心。2.2 字符串类型CHAR与VARCHAR、TEXT与ENUM的取舍字符串是业务表里最常用的类型但也是知识盲区最密集的地方。先解决最基础的CHAR还是VARCHAR的问题。CHAR(N)是定长字符串长度固定为N个字符不足的部分用空格填充检索时会去掉尾部空格。VARCHAR(N)是变长字符串实际存储时只占用实际长度的字节另外还需要1到2个字节记录长度信息。看起来VARCHAR全面优于CHAR但实际不是这样。CHAR的性能优势在于定长MySQL不需要额外去解析长度信息随机IO和行存储的页利用率更稳定。如果字段长度几乎固定比如身份证号、手机号、MD5值、固定长度的编码用CHAR更合适。如果字段长度差异大比如用户名、文章标题、地址用VARCHAR能显著节省空间。VARCHAR里面的N是字符长度不是字节长度这一点很多人会搞混。一个VARCHAR(255)可以存255个英文字母也可以存255个中文汉字前提是字符集是utf8mb4中文每个字符占4个字节。行存储的最大字节数是65535所以如果你用utf8mb4字符集一个VARCHAR列实际最多能存大约16383个字符65535除以4而且这个限制是整个行的所有变长列共享的还要减去变长列表等开销。说到这个就不得不提一个经典的索引场景。在utf8mb4字符集下如果给一个VARCHAR(255)列建索引单个索引键最大字节数是767字节InnoDB早期版本255乘以4刚好超过1020直接超出限制所以会报Specified key was too long错误。这就是为什么很多老经验建议VARCHAR不要超过255的原因之一——超过255在早期MySQL版本里没法直接建索引。现代的MySQL 8.0.12以上版本索引键限制已经放宽到了3072字节但设计时还是要有“一个索引列不要过长”的意识业务上没办法就拆前缀索引。TEXT族和BLOB族属于大对象类型。TEXT存文本BLOB存二进制。它们和VARCHAR最大的区别是TEXT不能有默认值除非显式指定一个表达式默认值MySQL 8.0.13以后支持TEXT需要使用前缀索引TEXT在排序和比较时会有一些限制。实际业务里纯文本内容、JSON字符串、日志详情这种大字段适合用TEXT但要避免把TEXT塞进频繁查询的表中抽到独立的扩展表或者干脆放对象存储里会更好。ENUM和SET这两个类型我的态度是“除非业务场景非常确定否则慎用”。ENUM本质上是一个枚举类型内部存储用整数查询结果会自动转成字符串虽然节省空间但弊端不少改枚举值需要ALTER TABLE排序时按枚举定义顺序而不是字母顺序不同字符集下行为有细微差异如果你的代码里用了MySQL驱动ENUM映射到Java字符串还可能有问题。SET类似它是多选枚举底层用位图存储适合固定、绝不会变化的多选项。我的建议是状态、性别、订单类型这类字段老老实实用TINYINT存编码值在代码里做映射比ENUM灵活得多也不会有那么多边界问题。2.3 日期时间类型DATETIME还是TIMESTAMP日期时间类型有DATE、TIME、DATETIME、TIMESTAMP、YEAR五种。业务里最常用的是DATETIME和TIMESTAMP这俩也经常被拿来对比。DATETIME的范围是1000-01-01 00:00:00到9999-12-31 23:59:59不依赖时区设置存什么就是什么占8个字节。TIMESTAMP的范围是1970-01-01 00:00:01 UTC到2038-01-19 03:14:07 UTC占4个字节存储时会根据会话的time_zone设置将值转成UTC存储查询时再转回当前时区。这个差异带来一个经典问题如果服务器时区设置不对或者连接串里没指定时区TIMESTAMP读出来的时间就会和实际时间差8个小时。选型建议是这样的如果你需要记录一个绝对时间点且不关心服务器时区变换用TIMESTAMP可以节省空间但如果你要存一个业务时间比如“活动开始时间”“下单时间”而且未来系统可能会迁移到不同时区的服务器用DATETIME更稳妥不会因为时区设置不同而产生歧义。MySQL官方和一些云数据库厂商从8.0.19开始建议默认用DATETIME因为TIMESTAMP的2038年问题始终是个坎。再说一个很实用的细节。TIMESTAMP和DATETIME都可以用DEFAULT CURRENT_TIMESTAMP作为默认值也可以在ON UPDATE子句里设CURRENT_TIMESTAMP这样在更新记录时时间字段会自动刷新。这个特性在维护updated_at这类字段时非常方便。MySQL 8.0.13及以上版本还允许DATETIME支持表达式默认值比如DEFAULT (CURRENT_TIMESTAMP INTERVAL 1 DAY)灵活性更高了。3. 实操从零设计一张订单表的字段类型有了前面的类型知识打底这一部分我带你完整走一遍订单表的设计过程。这个案例能覆盖大部分常见字段的类型选择看完直接套用到自己的项目里。3.1 字段设计与类型选择假设我们要设计一张电商订单主表核心字段有主键ID、订单号、用户ID、订单金额、折扣金额、应付金额、订单状态、收货地址、下单时间、支付时间、备注。逐字段来看。主键ID用BIGINT UNSIGNED AUTO_INCREMENT原因很简单单表数据量超过21亿条时INT就不够用了BIGINT直接解决后顾之忧。UNSIGNED是为了把负数空间让给正数让可用的正数范围翻倍。如果你用的是分布式ID方案比如雪花ID那更是必须用BIGINT因为雪花ID本身是19位数字超出INT范围。订单号这个字段很多人会纠结用BIGINT还是VARCHAR。我的习惯是如果订单号全部由数字组成且长度可控就用BIGINT如果订单号可能带前缀字符比如DD202501010001那就用CHAR(32)或VARCHAR(32)。这种“什么时候用什么”取决于业务形态没有绝对标准但一定要给它建唯一索引因为订单号的唯一性是核心约束。用户ID用BIGINT UNSIGNED和用户表主键类型保持一致。这里有个很容易忽略的点如果你用户表主键是BIGINT订单表里存用户ID却用了VARCHAR连表查询时虽然不会报错但会发生隐式转换直接让索引失效慢查询就出现了。金额字段全部用DECIMAL(10,2)。这里的10,2含义是总长度10位、小数2位整数部分最多8位单笔金额最大可以到99999999确实够用。如果你做的是跨境支付、积分系统可能要精确到更多位比如DECIMAL(14,4)但日常电商系统DECIMAL(10,2)足够了。坚决不用FLOAT和DOUBLE存金额这一点上面已经讲过了。订单状态用TINYINT就可以了0待支付、1已支付、2已发货、3已完成、4已取消、5售后中。实际存数字显示文本在代码里做映射。后续如果要加状态代码里加一个枚举就行不用动表结构。收货地址这个字段看起来简单但选型很容易出错。有人说地址可能很长用TEXT吧有人说地址一般一两百字用VARCHAR(255)吧。我实际操作下来收货地址用一个VARCHAR(200)够了多数字段如果不放心就VARCHAR(255)。如果未来要拆省市区应该拆成独立的省、市、区、详细地址字段而不是塞一个超长TEXT。TEXT字段在查询、排序、GROUP BY时都有额外限制能不用就不用。下单时间用DATETIME DEFAULT CURRENT_TIMESTAMP支付时间用DATETIME NULL DEFAULT NULL表示“尚未支付”。这里NULL和DEFAULT NULL是有讲究的。注意DATETIME默认是不允许NULL的如果你不写NULL插入时没给值会报错。有些团队会把“未支付”的支付时间设计成1970-01-01 00:00:00这样可以避免NULL带来的比较问题但这样写语义不清晰我建议还是允许NULL更直白。3.2 完整的建表语句示例把上面的设计串起来建表语句如下CREATE TABLE t_order ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键ID, order_no VARCHAR(32) NOT NULL COMMENT 订单号, user_id BIGINT UNSIGNED NOT NULL COMMENT 用户ID, total_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 订单总金额, discount_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 优惠金额, pay_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 应付金额, status TINYINT NOT NULL DEFAULT 0 COMMENT 订单状态0待支付 1已支付 2已发货 3已完成 4已取消, receive_address VARCHAR(200) NOT NULL DEFAULT COMMENT 收货地址, pay_time DATETIME NULL DEFAULT NULL COMMENT 支付时间NULL表示未支付, 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), UNIQUE KEY uk_order_no (order_no), KEY idx_user_id (user_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci COMMENT订单主表;几个细节展开说一下。ENGINEInnoDB是必须的另一个常见引擎MyISAM不支持事务、不支持行级锁还有表级锁和崩溃恢复问题业务表基本不应该用MyISAM。字符集选utf8mb4而不是utf8因为utf8mb4才是完整的Unicode支持能存emoji和生僻字utf8在MySQL里是utf8mb3的别名已经不建议使用。排序规则utf8mb4_0900_ai_ci是MySQL 8.0默认的大小写不敏感一般够用。如果你需要对某个字段做大小写敏感的查询比如用户名登录可以在查询时用BINARY关键字或单独指定字段的COLLATE。UNIQUE KEY uk_order_no和KEY idx_user_id都是逻辑推导的结果订单号唯一所以建唯一索引用户ID经常用来查订单列表所以建普通索引。索引不是越多越好每个索引都会增加写入成本和存储成本核心查询路径上建索引就够了。3.3 类型变更与ALTER TABLE的注意点表建好之后不是一劳永逸的业务变化经常要改字段类型。ALTER TABLE ... MODIFY COLUMN ...是最直接的改法比如把status从TINYINT改成SMALLINTALTER TABLE t_order MODIFY COLUMN status SMALLINT NOT NULL DEFAULT 0 COMMENT 订单状态;这个操作本身不复杂但要注意的是在大表上执行ALTER TABLE会锁表业务高峰时期执行会导致大量请求阻塞甚至拖垮数据库。常用的解决办法是借助gh-ost或pt-online-schema-change这类工具做在线表结构变更但那是另一个话题了。这里只提醒一件事任何时候改动表结构先备份、先看表大小、先评估业务低峰期不要在生产环境随手敲ALTER TABLE。顺便说一下存储过程里的类型坑。如果你在存储过程中声明变量比如DECLARE amount DECIMAL(10,2);这个变量和表字段做比较、赋值时隐式转换规则同样生效。如果变量类型和字段类型不匹配轻则精度丢失重则报Out of range value错误。存储过程里尽量让变量类型和表字段完全一致别让MySQL帮你“智能转换”那个“智能”往往会反过来坑你。4. 常见问题排查那些年踩过的类型坑类型相关的问题在实际排查中经常会以各种奇奇怪怪的方式冒出来。我整理了一张高频问题速查表后面再逐个展开分析。现象根因解决方案金额对不上用了FLOAT/DOUBLE存金额改为DECIMALWHERE条件查询不走索引隐式类型转换保证字段类型和值类型一致日期差了8小时TIMESTAMP时区设置问题检查time_zone/JDBC连接时区VARCHAR建索引报too longutf8mb4下索引键超长改用前缀索引或缩短字段排序结果不符合预期字符集排序规则不同指定COLLATE手机号前面的0丢了用数值类型存手机号字符串类型存手机号唯一索引允许了重复对NULL列建唯一索引理解NULL语义用空串代替存储过程报Out of range变量类型和字段类型不匹配统一变量类型4.1 隐式类型转换为什么会“吃掉”索引这是我最常被问到的一类问题。举个例子用户在表里有phone字段类型是VARCHAR(20)建了索引。查询写成SELECT * FROM user WHERE phone 13800138000;这里的13800138000在SQL里是一个整数常量MySQL会把phone字段转成数字去比较相当于执行了CAST(phone AS SIGNED)对每个字段值都做一次转换索引自然就没了。解决办法很简单查询字符串加引号SELECT * FROM user WHERE phone 13800138000;这个问题的本质是MySQL在比较不同类型时有一套隐式转换规则数值类型优先于字符串类型日期类型比较特殊。只要一边是数值另一边是字符串MySQL一般会把字符串转成数值。规则本身不是什么秘密关键是你要时刻记住写SQL时字段类型和值类型保持一致不要依赖MySQL帮你转。还有一类比较容易忽略的SELECT * FROM user WHERE id 100主键id是INT等号右边是字符串100虽然这样的SQL也能走索引因为它把字符串转成了数值比较但如果你在连接查询时用错了类型比如a.user_id b.id其中a.user_id是VARCHARb.id是BIGINT那就一定有一个字段会发生隐式转换具体看哪个类型优先级低哪个就吃哑巴亏。排查这类问题最有效的方式是EXPLAIN看type列如果是ALL或者index而没有走ref/eq_ref十有八九就是类型不匹配。4.2 唯一索引与NULL为什么“唯一”却不唯一很多人在唯一索引上被坑过。比如我有一张用户表email字段加了唯一索引但业务上允许部分用户没有邮箱于是插入时email存了NULL执行了两遍同样的插入INSERT INTO user (email) VALUES (NULL); INSERT INTO user (email) VALUES (NULL);结果两条都能成功。原因是在MySQL中NULL不等于NULL唯一索引允许多个NULL值存在。这是个大多数人会忽略的语义细节。解决方案有几个思路。一是字段设计为NOT NULL DEFAULT 把空串当作“没有邮箱”的语义这样唯一索引可以保证最多一条空串记录但如果你允许很多用户没有邮箱这个方案也行不通因为第二条空串插入时就会违反唯一约束。二是改成允许NULL但接受“多个NULL存在”的现状在业务代码里判断时用IS NULL而不是。三是在应用层做唯一性校验但这存在并发问题一般不建议。最合理的看业务语义如果“没有邮箱”是正常状态就用NULL并接受多条如果“邮箱为空只能存在一条”是业务约束就在应用层加锁或用INSERT ... ON DUPLICATE KEY UPDATE来做。4.3 排序结果“不对”字符集和排序规则的影响排序是另一个隐藏雷区。MySQL的排序行为由列的COLLATE决定。默认utf8mb4_0900_ai_ci是大小写不敏感、重音不敏感的排序规则这意味着a和A在排序时被视为相同。如果你希望大小写敏感排序需要给列单独指定COLLATE utf8mb4_bin或者utf8mb4_0900_as_cs。还有中文排序的问题。在utf8mb4字符集下中文排序不是按拼音而是按Unicode码点排序。你执行ORDER BY name看到的“张三”“李四”“王五”排列顺序可能是按编码来的不是按拼音首字母。如果需要按拼音排序可以借助CONVERT(name USING gbk)辅助排序虽然不优雅但确实可用SELECT * FROM user ORDER BY CONVERT(name USING gbk);这个技巧我用了很多年在MySQL 8.0里依然有效。要注意的是CONVERT会阻止这个字段上的索引排序优化所以只适合数据量不大的场景。4.4 日期相差8小时和2038问题时间问题排查起来最让人头疼。TIMESTAMP列在存储和读取时会跟随数据库会话的time_zone设置如果你的数据库时区是SYSTEM而服务器系统时区是UTC就会导致读出来的时间比北京时间少8小时。排查思路从上到下依次检查服务器系统时区、MySQL的time_zone变量、连接串里的serverTimezone参数、应用程序所在机器的时区。一条链路上任何一个环节不一致就会出现“数据库里明明是14:00查出来变成06:00”的情况。更隐蔽的是2038年问题。TIMESTAMP能存储的最大时间是2038-01-19 03:14:07 UTC如果你的业务系统要处理几十年后的日期比如保险合同期限、长期订阅到期时间TIMESTAMP会在2038年到来时直接溢出改为DATETIME是最稳妥的方案。4.5 MySQL 8.0认证插件引发的“连不上”问题这个问题虽然不是数据类型本身但热度很高值得在这里提一嘴。用客户端连接MySQL 8.0时经常会报Authentication plugin caching_sha2_password cannot be loaded也就是热词里那个firedac phys mysql client does not support authentication protocol requested的变种原因是从MySQL 8.0开始默认认证插件从mysql_native_password换成了caching_sha2_password很多老客户端不支持。解决办法有两种一是升级客户端驱动这是首选方案二是把用户改成老插件ALTER USER root% IDENTIFIED WITH mysql_native_password BY your_password;但要注意mysql_native_password在MySQL 9.0里已经被移除所以这不是长久之计最终还是要升级驱动到支持caching_sha2_password的版本。5. 类型选型的几条通用经验最后分享几条我摸爬滚打出来的通用经验既是给新手的避坑指南也是给自己以后review别人建表时的检查清单。第一条所有金额、费率、单价一律用DECIMAL不用FLOAT/DOUBLE。这个规则没有任何例外就算你只是在做一个个人项目也别偷懒否则后面对账、报表、计算均值全都可能出问题。第二条能用数值的别用字符串能用短字符串的别用长字符串。状态用TINYINT不要用VARCHAR(20)存“已支付”这种中文。短字符串这里又是另一个原则能用CHAR(32)存定长编号就不要用VARCHAR(255)存储和性能都比后者好。第三条所有表都建议带上created_at和updated_at这两个时间字段类型用DATETIME默认值用CURRENT_TIMESTAMP。这俩字段不光是审计需要很多业务排查和数据分析都离不开早设计早省事。注意时间字段的默认值和ON UPDATE用法我已经写在上面的建表语句里了直接照抄就行。第四条整型和字符串混合比较时一律加引号。这不算什么高深技巧就是在写SQL时养成好习惯隐式转换问题能从源头避免一大半。第五条谨慎使用ENUM、SET、BIT、空间类型这类特殊类型。它们各有适用场景但灵活性和通用性不如整数和字符串一旦业务变化改类型的成本远高于当初选通用类型。生产环境我用TINYINT和VARCHAR解决95%以上场景剩下5%才考虑特殊类型。第六条如果你在做数据同步或者跨库操作比如热词里那个mysql/sqlserver/postgresql数据库同步软件的场景一定要提前对齐两边的数据类型映射关系。MySQL里的TINYINT(1)在PostgreSQL里可能是BOOLEANMySQL的DATETIME在SQL Server里可能是DATETIME2映射错了数据就乱了。第七条写存储过程或者触发器时变量声明类型一定和表字段类型保持一致。别在存储过程里声明一个INT去接收表里的BIGINT值迟早会溢出。我自己的体会是MySQL数据类型看起来是入门第一课但真正吃透却是在踩了无数坑之后。每次遇到查不出来、排序不对、数据丢了的情况回头一看往往都是类型设计上的一个小失误。希望这篇内容能帮你把这块地基打牢建表的时候多想一步后面就能少加几十个班。