MySQL里选数据类型是很多人建表时最随意的决定但往往也是后来出问题的根源。你随便找个int(5)填进去以为最多只能存5位数字你用float存金额到月底对账时莫名少了一分钱你拿varchar去跟bigint比较查询计划直接放弃索引开始全表扫描。这些事每天都在生产环境里发生归根结底就是对MySQL常见数据类型的底层逻辑没吃透。这篇文章我把MySQL里最常用的几类数据类型完整过一遍包括整数、小数、字符串、日期时间、类型转换几个核心板块每个细节都会讲清楚底层原理、适用场景和实际踩坑点适合刚入门的同学建立完整知识框架也适合写过一段时间SQL但想搞清楚“为什么”的开发者和运维同学。1. 整数类型不止是“存数字”那么简单整数类型是建表时最常用的数据类型但也是误解最多的一个。很多人只知道int能存数字却分不清tinyint、smallint、mediumint、int、bigint之间的边界更搞不懂int(n)里的n到底是什么直到面试被问到“mysql中int(5)是什么意思”才反应过来自己从来没真正理解过。1.1 整数类型家族从tinyint到bigintMySQL的整数类型一共有5种区别在于占用的字节数和能表示的数值范围。这里我直接给出一张对照表建表前先对着看一眼很多容量规划问题能提前避开。类型存储字节有符号范围无符号范围TINYINT1-128 ~ 1270 ~ 255SMALLINT2-32768 ~ 327670 ~ 65535MEDIUMINT3-8388608 ~ 83886070 ~ 16777215INT4-2147483648 ~ 21474836470 ~ 4294967295BIGINT8-9223372036854775808 ~ 92233720368547758070 ~ 18446744073709551615选型的时候有个很实用的参考标准状态值、布尔值用TINYINT小规模的年龄、数量用SMALLINT常规业务主键和一般数值用INT需要存大量数据、或者用分布式ID这种超大数字时用BIGINT。MEDIUMINT实际用得比较少但你要知道它的存在。大多数场景下INT就够用了别什么字段都上BIGINT一张表数据量大了之后多出来的字节会被放大到索引体积和内存消耗上。1.2 int(n)到底是什么显示宽度不是存储上限这是高频考点。“mysql中int5”这个搜索词说明很多人会在面试题里碰到类似“int(5)能存多少位”的问题。答案可能会让不少人意外int(5)不止能存5位数字你存12345678也完全没问题。括号里的数字是显示宽度不是存储上限存储上限由int本身决定永远是4个字节的范围。那显示宽度有什么用它只在配合ZEROFILL属性时才真正体现作用。比如int(5)加上ZEROFILL存一个123查询出来会显示00123不足5位左边补0。需要注意ZEROFILL会同时把该字段标记为UNSIGNED这是MySQL的一个隐含规则很多人不知道在文档里也容易被忽略。实际开发中我一般不建议用ZEROFILL补零需求用LPAD函数去做反而更灵活可读性也更好。另外一个和“mysql中int5”紧密相关的知识点是整数在表达式中的类型升级。int和int做加法结果还是int但如果两个int相加结果溢出比如2147483647 1MySQL会报错或者在严格模式下报OUT OF RANGE。如果是int和bigint相加MySQL会自动把结果提升为bigint避免溢出。更隐蔽的是无符号数参与的算术运算如果一个int unsigned和int做减法MySQL会把结果提升为unsigned类型导致计算出现负数时变成一个巨大的正数。这个问题在真实业务里特别容易踩尤其是做统计、做库存扣减的时候务必先把类型统一转成有符号或者大范围类型再运算。1.3 有符号和无符号的选择默认情况下整数类型是有符号的可以存负数。如果你的业务字段天然不可能为负比如金额、年龄、库存数量可以把字段定义为UNSIGNED这样正数范围扩大一倍。但这里有个权衡无符号字段不能存负数一旦将来业务扩展需要负数表示alter table改字段类型会锁表大表上代价很高。主键自增字段我建议直接用BIGINT UNSIGNED。很多人可能觉得INT足够了但按互联网业务常见的写入速度INT上限21亿左右哪天促销活动一冲主键耗尽的事情在真实场景里并不罕见。一旦主键耗尽数据迁移、分表、改表结构都是伤筋动骨的操作。所以新业务建表时主键直接BIGINT UNSIGNED起步这是低成本但非常有效的保险。2. 小数与精度为什么金额字段不能用float整数类型讲清楚了接着聊小数。这是MySQL里最容易埋雷的领域几乎每个用float存金额或者百分比的项目后面都会遇到精度对账问题。MySQL提供的小数类型主要有三种FLOAT、DOUBLE和DECIMAL它们之间的区别不是“精度高低”而是“存储逻辑完全不同”。2.1 FLOAT与DOUBLE的精度陷阱FLOAT是单精度浮点占用4字节DOUBLE是双精度浮点占用8字节。它们在MySQL内部用二进制近似方式表示十进制小数这意味着很多十进制小数无法被精确表示只能无限逼近。比如0.1在二进制浮点里就是个无限循环小数存储时会被截断累加多次以后误差就会显现出来。我举一个真实场景。购物车连加三件商品单价分别是0.1元、0.2元、0.3元如果用FLOAT存储并累加结果大概率不是0.6而是0.5999999999999999。前端显示的时候一看金额不对用户投诉。更严重的是如果金额参与对账、结算、退款等流程误差会不断累积。所以行业里有一条铁律金额、费率、单价这类对精度敏感的字段一律不用FLOAT和DOUBLE用DECIMAL。FLOAT和DOUBLE适合什么场景呢科学计算、统计指标、指标看板里的平均值和比率这些对误差容忍度较高的分析型数据可以用DOUBLE提高性能。但凡是面向用户、面向钱的一律DECIMAL。2.2 DECIMAL定点数真正精确的小数DECIMAL是定点数它不是用二进制近似而是把数字以字符串形式按位存储所以能精确表示十进制小数。语法是DECIMAL(M, D)M表示总位数D表示小数点后位数。比如DECIMAL(10, 2)表示总长10位小数点后占2位整数部分最多8位。这个范围要提前规划好M最大为65D最大为30并且D必须小于等于M。DECIMAL的存储空间是变长的MySQL官方文档给出的估算规则每9个十进制位需要4字节剩余不足9位按下面规则计算。剩余1到2位占1字节3到4位占2字节5到6位占3字节7到9位占4字节。比如DECIMAL(20, 6)整数部分14位小数部分6位整数部分9位占4字节剩余5位占3字节小数部分6位占3字节总共10字节。理解这一点可以帮你估算大表在DECIMAL字段上的存储开销比如一亿行就有1GB左右的占用设计时能压缩位数就尽量压缩。实际项目中我建议金额字段统一使用DECIMAL(10, 2)常规业务足够不会出现科学计数法也不会在报表导出时出现一堆小数尾巴。如果涉及跨境或者汇率可能需要DECIMAL(10, 4)甚至更多小数位这种情况要把精度设计提前跟业务方对齐避免后期改字段类型。2.3 decimal(10,2)到底能存多大数网上经常有人在问“decimal(10,2)能存多大的数”。简单算一下总位数10位小数点占2位整数部分最多8位也就是最大99999999.99。如果你拿它来存一个亿1亿的整数部分有9位直接超出范围MySQL会报错或者按照严格模式截断。所以规划DECIMAL的M和D时一定要结合业务未来增长量来计算别只拍脑袋写一个(10, 2)。比如订单金额贵一点的场景建议DECIMAL(12, 2)整数部分10位能覆盖百亿级别的金额基本够用。2.4 数据类型强制转换与小数计算热词里出现了“数据类型强制转换”和“mysql数据类型强制转换”这和小数计算有很大关系。在SQL里使用CAST函数可以显式转换类型比如CAST(price AS DECIMAL(10, 2))可以把一个字符串或者浮点数转成指定精度的定点数。但要注意如果字符串不能合法转换成目标类型MySQL会返回0或者报错具体取决于SQL_MODE是否开启严格模式。我在做报表开发时经常需要做除法比如计算折扣率、占比默认情况下两个整数相除会得到DECIMAL结果但两个整数相除结果位数可能很长。比如1/3会得到0.333333333333333316位小数如果不希望显示这么多位用ROUND函数配合CAST控制精度。这里要特别留意先用ROUND和先CAST再ROUND结果可能有差异。建议在源头上用CAST把分子或分母先转成DECIMAL再计算避免中间结果产生意外精度。3. 字符串类型CHAR、VARCHAR、TEXT、BLOB的全面对比字符串是业务表里占比最多的字段类型也是索引设计里最容易出问题的地方。一个VARCHAR到底该定义多长CHAR和VARCHAR相比哪个更省空间TEXT能不能建索引字符集选utf8mb4会带来多少额外开销这些问题如果不搞清楚上线后很容易遇到行大小超限或者索引失效的报错。3.1 CHAR与VARCHAR的本质区别CHAR是定长字符串长度固定最大255个字符。如果定义CHAR(10)存了abcMySQL会在后面用空格补齐到10个字符串查询出来的时候再把末尾空格去掉。这个特性有坑如果你存的数据本身末尾带空格比如用户输入了“admin ”带一个空格CHAR会把末尾空格吞掉导致数据不一致。VARCHAR是变长字符串长度可变最大可以到65535字节对应的字符数受字符集和行大小影响存储时会额外记录长度信息它不会去掉末尾空格并且末尾空格也会被保留下来。从性能上看CHAR在长度固定的场景下更快因为它不需要读取长度前缀存储位置天然对齐。而VARCHAR需要读取额外长度字节更新时如果长度发生变化InnoDB在页内的处理会更复杂。所以定长字段比如手机号、身份证号、固定格式编号可以考虑CHAR长度不确定的比如用户名、地址、备注一律VARCHAR。3.2 VARCHAR的长度选择不只是填个数字VARCHAR定义的长度单位是字符不是字节。在utf8mb4字符集下一个中文字符占4个字节一个英文或数字占1个字节。所以VARCHAR(255)在utf8mb4下最多能存255个字符但最多占用1020字节左右的数据空间再加上长度前缀和行内其他字段的字节数很容易接近InnoDB的行大小限制默认约65535字节。一张表如果有很多VARCHAR(255)字段加起来可能直接报“Row size too large”。另外一个隐藏问题是索引长度限制。InnoDB在utf8mb4下单列索引最大767字节旧版本或者3072字节新版本如果给VARCHAR(255)建立索引在旧版本下会直接失败因为255个字符在utf8mb4下需要1020字节超过767字节限制。解决办法是用前缀索引比如INDEX(username(50))把索引建在字段的前50个字符上。所以VARCHAR(255)不是万能选择建索引的字段长度要单独规划。一般经验是普通名称字段VARCHAR(50)到VARCHAR(100)长文本用VARCHAR(255)或者直接上TEXT不要统一写255。3.3 TEXT与BLOB能存大内容但不适合直接排序TEXT和BLOB是两种大对象类型TEXT存字符串BLOB存二进制数据。TEXT又分为TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT对应的最大长度分别为255字节、64KB、16MB、4GBBLOB对应关系一样。它们最大的问题是不能直接给整列建立普通索引除了加前缀长度也不能在GROUP BY、ORDER BY时被完整使用MySQL只能使用前面一部分内容进行排序这会导致排序性能下降甚至报错。比如你对一个MEDIUMTEXT字段做ORDER BYMySQL会使用前缀排序结果可能与预期不一致。更常见的坑是SELECT时如果不小心把大字段全部查出来网络传输和内存消耗会非常大。所以大文本字段要设计成独立表查询列表页时不select大字段点击详情时才按ID去取这是非常基础的性能优化思路。BLOB字段我这里多说一句不要把图片、文件直接塞进MySQL。数据库存文件路径或者对象存储的URL就行BLOB会让备份变得很慢binlog体积膨胀主从同步延迟飙升。我在生产环境见过有人把几百KB的PDF存进BLOB一个月后主从延迟到了十几秒最后痛下决心迁移到文件存储才解决。这个案例不是个例希望你不要再踩一遍。3.4 ENUM和SET少用但要知道的枚举类型ENUM是枚举类型定义时指定一组合法值比如ENUM(pending, paid, refunded)字段只能存这些值之一。SET是集合类型可以存定义值中的多个比如SET(read, write, execute)底层用位运算存储。ENUM的排序和比较是按定义顺序而不是字母顺序这点容易让人困惑。另外ENUM换值需要ALTER TABLE扩展性差业务状态新增时反而麻烦。所以我通常建议状态字段用TINYINT配代码表或者直接用VARCHAR固定值都比ENUM灵活。如果你只是想约束合法值还可以用CHECK约束MySQL 8.0.16之后CHECK约束才真正生效这也是一个值得关注的时间点。4. 日期时间类型DATETIME与TIMESTAMP的选择题日期字段几乎每张业务表都有创建时间、更新时间、订单时间、活动开始结束时间。但很多人的选择是瞎蒙的建表时随手写一个DATETIME或者TIMESTAMP直到出现2038年问题或者时区错乱才开始回头研究这些类型的区别。其实搞清楚底层逻辑之后选型并不难。4.1 五种时间类型范围对照MySQL里跟日期时间相关的类型有DATE、TIME、DATETIME、TIMESTAMP和YEAR五种。日常最常用的是DATETIME和TIMESTAMPDATE用于存生日、节日这种只有日期的场景TIME用于存时间段YEAR很少用到。类型存储字节范围说明DATE31000-01-01 ~ 9999-12-31只存日期不存时间TIME3-838:59:59 ~ 838:59:59可以用来表示时间段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值YEAR11901 ~ 2155可以存2位或4位上面的时间范围是个关键信息。TIMESTAMP只到2038年这就是所谓的“2038年问题”。如果你的系统需要处理未来几十年甚至上百年的日期比如保险、教育、合同领域用TIMESTAMP就埋下了隐患。DATETIME的存储范围大得多这也是我默认推荐DATETIME的原因之一。4.2 DATETIME与TIMESTAMP的深层区别DATETIME和TIMESTAMP有四个主要区别存储字节不同、范围不同、时区处理逻辑不同、默认值规则在不同版本下也不同。先说时区。TIMESTAMP本质是把时间转换成UTC存储查询的时候根据会话时区转回本地时间。如果你改了数据库时区或者应用服务器时区同一个TIMESTAMP字段读出来会不同。DATETIME则是一个死值存进去是什么查出来就是什么与时区无关。对于出海业务、全球化业务需要按用户时区展示时间的场景TIMESTAMP配合会话时区设置反而更方便。但对大多数纯国内业务DATETIME会减少很多时区相关的困惑。再谈默认值和自动更新。MySQL 5.6之后DATETIME支持DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP所以“TIMESTAMP可以自动更新而DATETIME不行”这个老说法只对老版本成立。新版本两者都能做自动更新。建表时如果需要记录创建时间和更新时间最常见写法是CREATE TABLE order_info ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;ON UPDATE CURRENT_TIMESTAMP的作用是一行数据只要有任何字段被UPDATE该字段会自动刷新成当前时间。要注意就算你更新前后的值一样只要执行了UPDATE语句它也会变。如果不想让update_time变动可以使用UPDATE语句里显式把该字段写成原值。这个特性在自动记录数据更新场景下非常方便但它不是银弹有些业务并不希望任何改动静默改动更新时间比如定期批量任务触发的空更新这时就应该去掉ON UPDATE CURRENT_TIMESTAMP改为代码里显式维护。4.3 字符串日期乱象为什么不要用VARCHAR存日期网上搜“数据库存日期用什么类型”总有人回答用VARCHAR理由是方便比较和存储。这个建议绝对不要采纳。VARCHAR存日期至少有四个问题无法利用日期函数的索引优化范围查询效率极差格式不统一的后患2024-1-1和2024-01-01都是合法字符串但没法统一比较无法直接进行日期加减运算排序结果不符合直觉。正确的做法是使用DATE或DATETIME存储用DATE_FORMAT、STR_TO_DATE等函数做格式化转换。如果需要按日期分组统计直接用DATE(order_time)就行配合适当的索引仍然可以走范围扫描效率远高于字符串方案。5. 类型转换机制隐式转换与显式CAST的正确姿势类型转换是MySQL里最“阴”的一个话题。你写SQL时可能根本没意识到某个字段和某个常量的类型不匹配MySQL已经悄悄做了隐式转换。转换对了没影响转换错了索引报废、结果不对、数据错乱全都有可能发生。这一章我把隐式转换和显式转换彻底讲透。5.1 隐式转换MySQL在背后做了什么MySQL中当你把一个字符串和一个数字做比较时它会尝试把字符串转成数字。例如SELECT * FROM user WHERE mobile 13800138000如果mobile是VARCHAR类型MySQL会把每一行的mobile转成数字再跟13800138000比较。问题在于如果mobile字段存在非数字字符比如带区号的“010-88888888”转换后变成0或者部分数字查询结果就会出问题。更麻烦的是索引失效。对VARCHAR字段使用数字常量比较时MySQL的优化器可能会放弃这个字段上的索引走全表扫描。因为索引结构里保存的是字符串而查询条件是数值两者无法直接匹配前缀只能先转换再比较。这个问题的典型表现是表里数据不多还好数据一多一条本来走索引几十毫秒的查询变成全表扫描几秒钟。解决办法是SQL里保持类型一致比如WHERE mobile 13800138000把常量写成字符串。反过来如果是整型字段与字符串常量比较比如WHERE age 25MySQL可以把25转成数字25这个过程通常对索引影响较小但仍不建议依赖这种写法。写SQL时做到字段类型和常量类型严格一致能省掉很多排查时间。5.2 显式转换CAST和CONVERT怎么用显式转换可以通过CAST函数和CONVERT函数实现。基本语法SELECT CAST(123 AS SIGNED); SELECT CONVERT(2024-01-01, DATE);CAST可以转换的类型包括SIGNED、UNSIGNED、DECIMAL、CHAR、DATE、DATETIME、TIME、BINARY等。CONVERT与CAST功能类似但在转换字符集时还有特殊用法CONVERT(string USING utf8mb4)。这两个函数在数据清洗、报表处理时非常实用。举几个实际例子-- 字符串转日期 SELECT STR_TO_DATE(2024/05/01, %Y/%m/%d); -- 日期转字符串 SELECT DATE_FORMAT(NOW(), %Y-%m-%d %H:%i:%s); -- 浮点转定点并四舍五入 SELECT CAST(12.345 AS DECIMAL(10, 2));第三个语句结果是12.35CAST本身就是四舍五入而不是截断。如果你想要截断效果用TRUNCATE函数。这一点在金额计算中很容易踩到比如算单价时12.345元显示成12.35还是12.34财务那边是很敏感的必须提前确认规则是四舍五入还是直接抹掉。5.3 数值提升和溢出MySQL在做运算时有一套隐式类型提升规则。INT和BIGINT相加时INT会提升为BIGINTDOUBLE和DECIMAL运算时DECIMAL会转为DOUBLE导致精度丢失有符号和无符号整数混合运算时结果向无符号提升这正是前面提到的int unsigned引发负数问题的根源。所以在写聚合SQL、存储过程、或者在应用代码里组装动态SQL时要先对字段类型有清楚的认知必要时用CAST统一转换以后再计算。热词里还有“mysql的or能去重吗”这其实是个SQL优化问题跟类型转换、索引也有关系。OR条件本身对结果集不做去重去重是DISTINCT或GROUP BY的职责。但如果OR连接的条件里有一个字段存在隐式类型转换导致索引失效整个查询都可能全表扫描。优化手段是把OR改写成UNION ALL或者拆成多个查询后再合并。这个问题在MySQL面试里出现频率很高面试官其实是想看你是否理解OR对索引的破坏原理。换到数据类型这个主题下核心就是一个OR条件等于把所有分支访问一遍再合并每个分支是否能走索引完全取决于字段和条件的类型匹配情况。6. 常见问题排查与面试高频考点实录最后这块非常重要我把多年实战和面试中被反复问到的数据类型问题做一个集中汇总。这些问题都来自真实业务场景很多是搜索引擎里高频出现的词大家可以直接对照自查。6.1 建表阶段最容易犯的错误很多人建表时对类型选择完全凭感觉。常见错误包括手机号用INT存结果超过INT上限直接报错性别用VARCHAR(50)存一个“男”字浪费空间状态用VARCHAR存“成功/失败”每次判断都要走字符串比较金额用FLOAT对账时出现各种细碎误差百度ID、微信openid这类长字符串直接用VARCHAR(64)结果因为字符集占用超过索引限制导致建索引失败。这些都是低水平但高频出现的错误建议新项目开工前把表和字段类型设计单独列为一个评审环节避免上线后返工。6.2 操作中常见问题速查表问题原因解决办法int(5)为什么不能限制输入5位数字括号内是显示宽度不是存储限制用INT本身的数值范围判断或加ZEROFILLdecimal(10,2)存1亿报错整数部分只有8位1亿需要9位扩大M如DECIMAL(12,2)float存金额累加后出现0.5999...浮点类型二进制近似导致精度丢失改成DECIMALvarchar字段和数字比较导致索引失效MySQL隐式类型转换SQL里常量和字段类型保持一致timestamp字段出现2038年问题TIMESTAMP范围有限改用DATETIME大文本字段排序结果异常TEXT/BLOB只取前缀参与排序改用VARCHAR或拆表数据库时区变了时间全变TIMESTAMP按UTC存储按业务需要选DATETIME或统一时区varchar(255)在utf8mb4下索引超长255字符×4字节超索引长度限制用前缀索引或缩小长度6.3 老生常谈但必须记住的面试题第一题MySQL里int(5)和int(8)有什么区别正确答案存储字节和范围完全相同区别只在配合ZEROFILL后的显示宽度。第二题金额字段用什么类型答案DECIMAL绝对不能是FLOAT或DOUBLE。第三题为什么使用UNSIGNED会有隐患答案无符号类型参与运算时负数会变成极大正数需要考虑业务结合显式转换。第四题DATETIME和TIMESTAMP的区别至少要说清存储字节、范围、时区处理三个点。第五题CHAR和VARCHAR比较时的区别重点说末尾空格处理和存储方式。第六题隐式类型转换为什么会导致索引失效这个要深入到优化器对索引列应用函数或转换后无法使用索引匹配的层面。这些题目看似基础但能完全答全的人不多。很多人只背了结论说不清原理面试官追问一句“为什么”就卡住了。核心还是要把“存储字节、取值范围、字符集字节占用、隐式转换规则”这四条主线吃透。数据类型的核心不是背几个名词而是理解每个类型背后的存储模型、边界条件和代价。我在实际项目里有一条很朴素的经验建表之前先把每个字段未来5年会存成什么样、数据量级大概多大、要不要参与运算、要不要建索引、要不要排序这五个问题过一遍类型自然就选对了。MySQL在类型上给你的“自由”其实很小把边界搞清楚反而让你后续省下大量兜底的时间和精力。