1. 老生常谈却值得重新梳理的MySQL数据类型我从刚入行时就被前辈反复叮嘱建表之前先把数据类型想清楚后面会少踩很多坑。当时觉得不就是选个int、varchar嘛能有多大差别。直到我维护过一个因为字段类型选错而被迫重构的业务系统才真正意识到数据类型不是一个随便选选的细节而是数据库设计的骨架。骨架歪了后面所有基于这张表的查询、索引、统计、接口返回值都会跟着别扭。这篇内容的定位很简单把MySQL常见的数据类型从原理到使用边界系统梳理一遍重点回答三个问题——这个类型底层是怎么存的、实际业务里应该怎么选、选错了会出现什么问题。适合刚接触MySQL的初学者也适合那些已经写过不少SQL但主要是别人建表我查询的同学。看完之后你至少能对自己项目里的建表语句多一分判断力而不是拿到什么类型就用什么。先声明一下本文基于MySQL 8.0的官方语义来写部分内容会顺带提一下5.7及更早版本的差异。版本不同某些类型的表现确实不一样这点后文会具体说明。MySQL的数据类型可以粗分为这么几大类数值类型整数、定点数、浮点数、字符串类型二进制、非二进制、大文本、日期时间类型、JSON类型以及一些空间数据类型和枚举集合类型。日常业务我们碰得最多的是数值、字符串、日期时间这三类所以这篇重点展开这三类JSON和空间类型会在对应章节里单独讲不会一笔带过。2. 整数类型不只是够用就行2.1 整数类型的底层存储到底差多少先看一张表把MySQL里所有整数类型的基本参数列出来。类型字节数有符号范围无符号范围常见用途TINYINT1-128 ~ 1270 ~ 255状态码、开关值、小枚举SMALLINT2-32768 ~ 327670 ~ 65535较小的计数器、端口号MEDIUMINT3-8388608 ~ 83886070 ~ 16777215中等规模的自增IDINT4-2147483648 ~ 21474836470 ~ 4294967295常规主键、外键BIGINT8-9223372036854775808 ~ 92233720368547758070 ~ 18446744073709551615雪花ID、超大数值、时间戳很多新手容易忽视一个关键点存储字节数直接决定了能表达的范围而不是看那个显示宽度。老版本的MySQL里INT(11)这种写法很常见括号里的数字给人一种这个字段最多存11位的错觉实际上它只是影响某些客户端工具展示时的补零行为并不限制存储范围和精度。8.0版本里INT的显示宽度已经被标记为废弃新代码里没必要再写INT(11)这种东西。选择整数类型的核心依据只有一个你的数据上界和下界是多少未来五年会不会超出这个范围。自增主键、订单号这类字段直接上BIGINT别纠结。很多人觉得订单量不可能上亿所以用INT结果遇到数据迁移或分表合并时突然发现INT不够用修改主键类型是整个系统最痛苦的操作之一因为所有外键和关联表都得跟着改。2.2 UNSIGNED和ZEROFILL的误用UNSIGNED表示无符号也就是只能存非负数。看起来很适合不会出现负数的字段对吧但这里有坑。第一UNSIGNED字段参与运算时如果表达式中出现负数MySQL会报错或者产生意外结果。比如一个UNSIGNED INT字段减1当值为0时执行UPDATE table SET num num - 1在严格模式下会直接报错BIGINT UNSIGNED value is out of range。同样两个UNSIGNED字段相减结果依然会被当成UNSIGNED如果结果为负数会发生溢出回绕变成一个巨大的正数。第二UNSIGNED并不会让存储变省它和普通INT占的字节数完全一样只是把负数那一半空间挪到了正数那一侧。所以除非你的字段确实只存非负数而且数据范围刚好需要用到超过有符号上限的那部分空间否则没必要加UNSIGNED。MySQL官方甚至建议尽量少用UNSIGNED因为容易引发边界问题和隐式转换的困扰。ZEROFILL就更冷门了。它会把数值左侧补零到指定宽度比如INT(5) ZEROFILL存12查询出来是00012。看着挺酷但8.0里ZEROFILL已经和显示宽度一起被废弃了。我见过有人用ZEROFILL让主键ID编号更整齐等数据量大了之后才发现这个字段在JOIN和索引优化时会引发各种怪问题。老老实实把显示格式化的事情交给应用层去处理别让数据库做这些花活。2.3 BOOLEAN其实是TINYINTMySQL没有原生的BOOL类型BOOLEAN和BOOL都是TINYINT(1)的别名。这意味着你可以往布尔字段里塞进2、3、100这些值只要你没加CHECK约束。如果你依赖这个字段做条件判断WHERE is_deleted 1和WHERE is_deleted在语义上会有差异因为后者会把任何非0值都当作真。更稳妥的做法是建表时加CHECK约束或者用ENUM(0,1)。不过从性能角度考虑TINYINT配合应用层强制赋值反而更高效。我在实际项目中的习惯是状态值用TINYINT并且永远只用0和1两种值命名上叫flag或is_xxx。如果字段未来可能扩展为多状态就会直接用TINYINT存状态码比如0待处理、1进行中、2已完成、3失败而不是用布尔命名去硬撑。3. 小数类型FLOAT、DOUBLE、DECIMAL选错等于埋雷3.1 从精确性角度理解三种类型先记住一句话FLOAT和DOUBLE是浮点数DECIMAL是定点数。浮点数用二进制近似存储所以很多十进制小数无法精确表示定点数以字符串或压缩十进制方式存储能精确表示指定精度内的十进制小数。FLOAT占4字节约7位有效数字。DOUBLE占8字节约15~16位有效数字。DECIMAL(M,D)M是总位数D是小数点后位数存储长度随精度变化。做金融、计费、结算这类业务钱和相关金额字段必须用DECIMAL。这几乎是行业铁律没有什么商量余地。浮点数在加减乘除时产生的误差虽然单个看很小但累积起来可能对账不平最后查半天查不出原因其实根子就在类型选错。3.2 DECIMAL的精度设计与存储开销DECIMAL(M,D)里M最大65D最大30且D不能大于M。举个例子DECIMAL(10,2)表示总位数10位其中小数位2位也就是说整数部分最多8位能表达的最大值是99999999.99。实际应用中DECIMAL的精度选择不能拍脑袋。M和D设得过大存储空间会膨胀设得过小数据插入时超出范围会被截断或报错。比如DECIMAL(5,2)存999.99没问题存1000.00就超了因为整数部分1000有4位加上小数2位总共需要4位整数2位小数而DECIMAL(5,2)最多容纳3位整数2位小数。存储字节数的估算规则是每9位十进制数占4字节剩余位数按1~2位占1字节、3~4位占2字节、5~6位占3字节、7~9位占4字节计算。比如DECIMAL(10,2)整数部分8位小数部分2位总共10位存储时按9位1位分需要415字节。这个细节在数据量极大的表里会影响行大小和索引效率值得算一下。3.3 浮点数的坑与应用场景虽然金融必须用DECIMAL但科学计算、地理坐标、统计分析这类场景FLOAT和DOUBLE也是合理选择因为它们的范围和性能在特定条件下更好。MySQL里FLOAT的默认精度是单精度DOUBLE是双精度。如果你不指定M和DMySQL会按类型本身的精度来存储。浮点数最大的坑在于比较。两个看起来一样的浮点数用WHERE price 19.9可能查不到记录因为19.9在二进制里是个无限循环小数存储时被截断成了近似值。正确的做法是使用范围比较比如ABS(price - 19.9) 0.0001或者把浮点数值转换为DECIMAL后再比较。另外不要把时间戳当整数存也不要把金额当浮点存这两个是我见过最多的两类类型错配。时间戳在MySQL里直接用BIGINT或INT存秒数/毫秒数金额一律DECIMAL。跨过这两条线你的系统就稳了一大半。4. 字符串类型CHAR、VARCHAR、TEXT长度只是其中一个维度4.1 CHAR与VARCHAR的存储机制和性能差异CHAR(N)和VARCHAR(N)里的N是字符数不是字节数。这一点的意义在utf8mb4字符集下尤其明显一个中文字符占3或4字节因此VARCHAR(255)最多能存255个字符而不是255字节。CHAR是定长字符串。MySQL存入CHAR(N)时会用空格填充到N个字符取出时会自动去掉末尾空格。这意味着CHAR适合存储长度基本固定的值比如手机号在有些国家是固定长度、身份证号、订单号、MD5哈希值。定长的好处是存储和比较时不需要额外记录长度信息某些场景下存取效率略高。VARCHAR是变长字符串。它会在数据前用1~2字节记录实际长度255个字符以内用1字节超过用2字节所以存储上会更紧凑。VARCHAR需要额外的长度前缀并且在更新时可能引发行迁移——即行被更新导致变长后原位置放不下整行需要移动到新的页影响性能和页分裂概率。但这通常只在频繁更新且长度变动剧烈的场景下才明显普通业务不需要过度担心。选型逻辑其实很朴素长度固定CHAR。长度会变且上限明确VARCHAR。长度上限极其不明确或超大TEXT但这个要谨慎用。4.2 VARCHAR(255)为什么会成为默认选择很多框架自动生成的建表语句里字符串字段默认都是varchar(255)。这个数字的来源其实和MySQL的索引限制有关——老版本InnoDB索引键最长767字节为什么是767因为早期MyISAM对索引前缀字节数限制为255个字符3字节后续受单列索引长度限制而utf8字符集下一个字符最多3字节2553765刚好接近但不能超过767所以varchar(255)成了最安全的大字符串选择。但在utf8mb4下一个字符最多4字节varchar(255)意味着最坏情况需要255*41020字节已经超过单列索引的3072字节限制的1/3。如果你需要给一个varchar(255)字段建前缀索引你会发现前缀长度必须控制在某个值以内。这不是不能用而是说255不是万能安全值。正确的姿势是把字符串字段的实际最大长度量出来再加缓冲。比如用户名最大20字符那用varchar(50)就足够宽裕了没必要255。用户备注可能写到几百字那就varchar(500)或varchar(1000)同时不要对这种长字段随意建索引。4.3 TEXT类型使用时的注意点MySQL的TEXT族包括TINYTEXT255字节、TEXT64KB、MEDIUMTEXT16MB、LONGTEXT4GB。这里说的都是字节上限不是字符数。TEXT类型的几个硬伤第一TEXT字段不能有默认值在MySQL 8.0之前8.0.13之后可以指定默认值但有一定限制如果你一定要默认值就得在应用层处理。第二TEXT字段不能直接建完整索引只能建前缀索引。即使你把索引指定为INDEX(content(100))这种索引也只能用于前缀匹配查询无法像普通字段那样做精确查找排序。第三TEXT字段存储在行外还是行内InnoDB对变长字段有一个页外存储机制当记录长度超过页大小阈值时会把长的字段放到单独的页。这会导致对这些字段的查询需要额外的IO性能远低于普通短字段。所以不要把大段文章、日志、JSON直接放在业务核心表的TEXT字段里还频繁查询。如果只是存文章内容、评论正文这类写多读少且很少参与条件过滤的数据MEDIUMTEXT或LONGTEXT是合适的。如果还需要对内容做全文搜索那应该考虑全文索引或者外接搜索引擎而不是在TEXT字段上硬扛。4.4 BINARY、VARBINARY与BLOBBINARY和VARBINARY类似CHAR和VARCHAR但存的是二进制字节串。BLOB族存二进制大对象。日常Web开发中BLOB多用于存图片、文件等原始数据但我不建议在关系库里直接存大文件因为这样会让数据库体积飞速膨胀备份、迁移都会变得很痛苦。文件应该放对象存储数据库里只存URL或对象键。BINARY还有个冷门用途存储需要精确比较且大小写敏感的字符串。CHAR和VARCHAR在默认排序规则下通常不区分大小写而BINARY比较时按字节值比较天然区分大小写。比如存用户密码的校验盐时用BINARY可以避免大小写折叠问题但这种场景不常见了解即可。5. 日期时间类型TIMESTAMP、DATETIME、DATE、TIME还有时区问题5.1 四个基础类型各自的边界MySQL日期时间类型有DATE、TIME、DATETIME、TIMESTAMP、YEAR。日常主要用前四个。DATE年月日范围1000-01-01到9999-12-313字节。TIME时分秒可以带小数秒范围-838:59:59到838:59:593字节加小数秒存储。DATETIME年月日时分秒范围1000-01-01 00:00:00到9999-12-31 23:59:598字节8.0中支持小数秒需要额外字节。TIMESTAMP范围从1970-01-01 00:00:01 UTC到2038-01-19 03:14:07 UTC4字节8.0中支持小数秒会额外占用存储时会从当前时区转换到UTC存储查询时再转回当前时区。这里面最容易被忽视的是TIMESTAMP的上限2038年问题。很多系统现在还活得好好的但如果你设计一个需要长期运行且会存几十年后日期的系统TIMESTAMP就不够用了。DATETIME能存到9999年显然更稳妥。5.2 时区问题怎么选TIMESTAMP和DATETIME最重要的差异不是存储范围而是时区处理。TIMESTAMP存的是UTC时间戳。也就是说无论数据库服务器的时区设置是什么存进去的是绝对时刻查询出来时MySQL会根据time_zone变量转成对应时区的本地时间。DATETIME则纯粹是个日历时间不带时区语义你存什么就是什么查询结果也是原样。这两者的取舍直接决定业务在不同时区下怎么表现。对于面向全球用户的应用我建议用TIMESTAMP存事件发生的绝对时刻比如订单创建时间、消息发布时间这样不同时区的用户看到的是各自本地化后的时间。对于用户自己输入的本地时间比如用户设定的生日、闹钟、会议时间用DATETIME更合适因为这种时间本来就与具体时区绑定不应该被全局时区转换影响。还要注意TIMESTAMP字段在插入时如果不显式赋值MySQL会默认填当前时间CURRENT_TIMESTAMP并且在更新时也可能自动更新。这个行为可以通过DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP控制特别适合做updated_at字段。DATETIME在8.0里也可以设置默认值为CURRENT_TIMESTAMP和ON UPDATE语法的但有些老版本不支持需要确认版本。5.3 日期的存储优化与查询规范如果只是存年月用DATE类型就够了不要用VARCHAR(7)拼成2026-03否则排序、范围查询、日期函数全废。如果只需要年份甚至可以直接用YEAR类型1字节。日常查询中对日期字段做比较时建议直接使用标准格式字符串WHERE create_time 2026-01-01 AND create_time 2026-02-01。尽量避免在字段上套函数比如WHERE DATE_FORMAT(create_time, %Y-%m-%d) 2026-01-01这样会让索引失效。替代方案是用范围查询。关于小数秒MySQL 8.0支持TIMESTAMP(6)、DATETIME(6)这样的精度定义括号里的数字表示小数秒位数。需要精确到毫秒或微秒的业务直接用这种带精度的类型别用VARCHAR存毫秒数。这里有个反直觉的点在8.0之前DATETIME不支持小数秒很多系统用BIGINT存毫秒时间戳。8.0之后用DATETIME(3)存毫秒并配上索引查询可读性和范围扫描效率都比BIGINT更好。5.4 实际案例时区切换后数据错乱我之前遇到过一个问题系统原本部署在国内所有时间都用DATETIME存北京时间。后来业务扩展服务器迁到海外机房连接串上的时区参数被设置成UTC。结果所有新增的DATETIME字段在插入时没有经过任何转换应用层却按本地时间生成数据最后库里出现了一批对不上号的记录——比如本地时间22点写入数据库显示14点前后查询结果不一致。这个问题的根源是应用层和数据库层的时区假设不一致而DATETIME类型又不会自动做任何换算。解决方法是统一约定应用层一律用UTC时间戳进行逻辑处理需要展示时再转本地数据库层面统一time_zone 00:00并把绝对时刻字段用TIMESTAMP存储。经过这次踩坑我对时区问题特别敏感也建议大家在新项目设计阶段就明确时区口径别等到线上故障才追悔。6. 枚举、集合、JSON与其他少有人用却很好用的类型6.1 ENUM和SET能用但要用得克制MySQL的ENUM类型在表面上是一个下拉选择框底层其实是用整数存储的。ENUM(small,medium,large)内部会把三个值分别映射成1、2、3。好处是存储紧凑1~2字节查询时能直接按字符串比较坏处也非常明显值变更麻烦要增加一个新的枚举值需要ALTER TABLE修改字段定义而ALTER TABLE在数据量大时可能锁表代价很高。排序规则会按照内部索引顺序而不是字符串的字母顺序。比如上面的ENUM按size排序会返回small、medium、large而不是alphabetical。如果插入不在枚举列表里的值在严格模式下会报错非严格模式下会被截断为空字符串并告警很容易产生脏数据。所以我现在的建议是如果枚举值非常稳定且数量很少比如性别、布尔状态ENUM勉强可用否则还是用TINYINT或VARCHAR配合应用层字典表灵活度更高。SET的定位是多选值底层按位存储如果业务需要一个字段表达多个标签SET在空间上确实省但同样有变更和查询可读性的问题非必要不用。6.2 JSON类型灵活与反范式MySQL从5.7开始支持JSON类型8.0大幅增强了相关函数。JSON列在存储时会自动校验格式非法JSON会被拒绝这比用VARCHAR存JSON然后依靠应用层解析要可靠得多。JSON类型带来的诱惑是灵活今天加个字段明天改个结构不用改表结构。这让很多团队在业务早期选择了JSON字段来快速迭代。但它也带来几个代价第一JSON字段无法直接按内部某个键值做索引虽然8.0支持CREATE INDEX通过生成的列来间接索引但写起来绕。第二JSON列的更新是整体覆盖的。你用JSON_SET修改其中一个键底层会把整个JSON文档重新写入新位置行变大、写放大大文档的更新代价很高。第三JSON字段让逻辑变得不透明。数据库里到底是什么结构只有应用代码知道换人来维护时容易出问题。我的建议是JSON适合存查询频率低、结构变化快的附属信息比如第三方接口返回的原始数据、不同渠道的扩展属性。核心业务字段一定要规范化成独立列不要为了省事把所有配置塞进一个JSON里然后天天去取某几个键。6.3 空间类型GIS业务的标配MySQL 8.0提供GEOMETRY、POINT、LINESTRING、POLYGON等空间数据类型配合ST_Distance_Sphere、ST_Within等函数可以做一些基础的地理计算。如果你只是需要在坐标上做简单的距离筛选用DOUBLE存经纬度配合Haversine公式也能工作但精度和性能都不理想。需要开发地理位置相关功能时把经纬度用POINT类型存再建SPATIAL索引才是比较正规的做法。空间索引的底层用的是R树在范围和包含查询上的表现优于传统B树。不过说实话MySQL的空间功能远不如专业GIS引擎或Elasticsearch等方案强大如果业务复杂度高还是应该考虑专用服务。7. 从热门搜索词看大家最想解决的问题整理这篇内容的时候我顺手翻了一圈热门搜索词发现大家搜索频率最高的几个问题非常集中mysql安装教程、mysql主从复制、mysql锁原理及面试题、mysql事务处理以及一个特别具体的需求——把远程库的这张表同步到本地。这些问题看起来和数据类型无关但仔细想想其实都和数据类型有关。比如远程库的这张表同步到本地。当你用mysqldump导数据再导入本地时如果两端MySQL版本不一致或者源表的字段类型用了新特性如CHECK约束、JSON默认值导入就可能报错。我自己就遇过源端是5.7、本地是8.0row_format和字符集规则不一样导致导入后中文乱码的问题。解决方法是先用SHOW CREATE TABLE查看源表的完整定义重点核对字符集utf8mb4还是utf8、表引擎、小数精度和时间类型。再比如事务处理和锁机制它们和数据类型的关系也密不可分。一个DECIMAL字段在高并发下做加减法时因为行锁的存在更新会串行化。如果你把金额存成DOUBLE可能出现两个事务各自读到近似值再写回最终数据不一致的概率会显著上升。数据类型不仅影响存储还影响事务的一致性和并发安全。还有一个高频词是数据类型强制转换。MySQL有一个非常智能但也非常坑的隐式转换机制比如SELECT * FROM user WHERE id 123abc在非严格模式下会把字符串转换成数字123查出来的结果可能出乎意料。经验法则永远不要让数据库在WHERE条件里帮你做类型转换查询参数和字段类型要严格一致。如果应用层传参的类型不可控在SQL里显式CAST一下都比让它隐式转换靠谱。关于mysql设置默认值为0这往往是指DEFAULT 0的用法。但这个看起来简单的操作在字符串类型上有个陷阱VARCHAR字段设置DEFAULT 没问题设置DEFAULT NULL和DEFAULT 语义完全不同。NULL会影响索引优化的判断COUNT(字段)不会统计NULL值应用层读出来也容易遇到空指针。所以我在建表时都会问一遍这个字段真的需要允许NULL吗如果业务上逻辑上不可能为空那就NOT NULL DEFAULT 或NOT NULL DEFAULT 0省一大串麻烦。8. 关于数据类型我最后的几点实践体会写到这里主干内容基本讲完了。最后分享一些我在多个项目里沉淀下来的实操习惯不算什么高深理论但很管用。第一字符集统一为utf8mb4。MySQL 8.0默认就是utf8mb4老项目迁移时也要尽量统一。很多人纠结utf8mb4是不是比utf8更占空间确实占但换来的是能存Emoji和四字节扩展字符以及不出现问号乱码。一个字符可能出现4字节没错但实际业务里绝大多数内容还是1~3字节为主这点空间成本几乎可以忽略。第二建表时给每个字段写上COMMENT。这个习惯太重要了。一个团队协作的项目里字段没有注释三个月后连写的人都可能记不清当时为什么这么设计。数据类型本身能传递一部分信息但业务语义还是要靠注释。我见过一个字段叫status注释空白后来排查问题才发现它肚子里装了七八种状态这种代码能把你逼疯。第三用SHOW CREATE TABLE校验你的表定义。不管是用可视化工具建的还是ORM自动生成的我都建议上线前DBA或者有经验的人过一遍SHOW CREATE TABLE。这一步能看到隐藏的字符集继承、排序规则、行格式、索引冗余。比如VARCHAR(255)在utf8mb4下能建前缀索引的长度是多少、有没有额外生成了奇怪的全表索引都能在这条命令里发现。第四不要迷信空间越省越好。有些新手为了让表小一点把所有数值字段都精简到最极限的TINYINT或SMALLINT。实际上让字段类型偏小未来业务增长一点就会撞天花板稍微留一档空间比如本来可以用SMALLINT的用INT换来的是几年之内不必做结构变更。结构变更在数据量大的时候代价极大不只是ALTER TABLE本身耗时一堆依赖它的查询计划、缓存、ORM映射都得重新调整。第五警惕ORM框架的隐式类型映射。MyBatis、Hibernate等框架在读取数据库结果集时可能会把DECIMAL转成BigDecimal或Double把DATETIME转成本地日期类型。如果你在Java里用一个double字段接收DECIMAL(10,2)精度会在转换时丢失。这种问题不体现在SQL层而是完全藏在应用代码里排查起来最耗时间。任何时候做类型透传都要在接口层写个测试断言确保值域和精度没有变化。最后想多说一句数据类型是数据库设计的最小决策单位也是所有上层优化的基石。索引好不好用查询快不快数据一致不稳定很多问题追根溯源都能落到类型选择上。与其在出问题后疯狂补丁不如在建表第一步就多花五分钟认真想清楚。希望大家看完这一篇之后再次打开建表语句时能多问自己几个问题这个字段会不会出现负数这个字符串最长会有多长日期时间需要跨时区吗值会不会超过当前类型的范围这些问题的答案会引导你做出更稳妥的判断。 MySQL数据类型选型与底层存储原理：从建表设计到性能优化的完整指南
1. 老生常谈却值得重新梳理的MySQL数据类型我从刚入行时就被前辈反复叮嘱建表之前先把数据类型想清楚后面会少踩很多坑。当时觉得不就是选个int、varchar嘛能有多大差别。直到我维护过一个因为字段类型选错而被迫重构的业务系统才真正意识到数据类型不是一个随便选选的细节而是数据库设计的骨架。骨架歪了后面所有基于这张表的查询、索引、统计、接口返回值都会跟着别扭。这篇内容的定位很简单把MySQL常见的数据类型从原理到使用边界系统梳理一遍重点回答三个问题——这个类型底层是怎么存的、实际业务里应该怎么选、选错了会出现什么问题。适合刚接触MySQL的初学者也适合那些已经写过不少SQL但主要是别人建表我查询的同学。看完之后你至少能对自己项目里的建表语句多一分判断力而不是拿到什么类型就用什么。先声明一下本文基于MySQL 8.0的官方语义来写部分内容会顺带提一下5.7及更早版本的差异。版本不同某些类型的表现确实不一样这点后文会具体说明。MySQL的数据类型可以粗分为这么几大类数值类型整数、定点数、浮点数、字符串类型二进制、非二进制、大文本、日期时间类型、JSON类型以及一些空间数据类型和枚举集合类型。日常业务我们碰得最多的是数值、字符串、日期时间这三类所以这篇重点展开这三类JSON和空间类型会在对应章节里单独讲不会一笔带过。2. 整数类型不只是够用就行2.1 整数类型的底层存储到底差多少先看一张表把MySQL里所有整数类型的基本参数列出来。类型字节数有符号范围无符号范围常见用途TINYINT1-128 ~ 1270 ~ 255状态码、开关值、小枚举SMALLINT2-32768 ~ 327670 ~ 65535较小的计数器、端口号MEDIUMINT3-8388608 ~ 83886070 ~ 16777215中等规模的自增IDINT4-2147483648 ~ 21474836470 ~ 4294967295常规主键、外键BIGINT8-9223372036854775808 ~ 92233720368547758070 ~ 18446744073709551615雪花ID、超大数值、时间戳很多新手容易忽视一个关键点存储字节数直接决定了能表达的范围而不是看那个显示宽度。老版本的MySQL里INT(11)这种写法很常见括号里的数字给人一种这个字段最多存11位的错觉实际上它只是影响某些客户端工具展示时的补零行为并不限制存储范围和精度。8.0版本里INT的显示宽度已经被标记为废弃新代码里没必要再写INT(11)这种东西。选择整数类型的核心依据只有一个你的数据上界和下界是多少未来五年会不会超出这个范围。自增主键、订单号这类字段直接上BIGINT别纠结。很多人觉得订单量不可能上亿所以用INT结果遇到数据迁移或分表合并时突然发现INT不够用修改主键类型是整个系统最痛苦的操作之一因为所有外键和关联表都得跟着改。2.2 UNSIGNED和ZEROFILL的误用UNSIGNED表示无符号也就是只能存非负数。看起来很适合不会出现负数的字段对吧但这里有坑。第一UNSIGNED字段参与运算时如果表达式中出现负数MySQL会报错或者产生意外结果。比如一个UNSIGNED INT字段减1当值为0时执行UPDATE table SET num num - 1在严格模式下会直接报错BIGINT UNSIGNED value is out of range。同样两个UNSIGNED字段相减结果依然会被当成UNSIGNED如果结果为负数会发生溢出回绕变成一个巨大的正数。第二UNSIGNED并不会让存储变省它和普通INT占的字节数完全一样只是把负数那一半空间挪到了正数那一侧。所以除非你的字段确实只存非负数而且数据范围刚好需要用到超过有符号上限的那部分空间否则没必要加UNSIGNED。MySQL官方甚至建议尽量少用UNSIGNED因为容易引发边界问题和隐式转换的困扰。ZEROFILL就更冷门了。它会把数值左侧补零到指定宽度比如INT(5) ZEROFILL存12查询出来是00012。看着挺酷但8.0里ZEROFILL已经和显示宽度一起被废弃了。我见过有人用ZEROFILL让主键ID编号更整齐等数据量大了之后才发现这个字段在JOIN和索引优化时会引发各种怪问题。老老实实把显示格式化的事情交给应用层去处理别让数据库做这些花活。2.3 BOOLEAN其实是TINYINTMySQL没有原生的BOOL类型BOOLEAN和BOOL都是TINYINT(1)的别名。这意味着你可以往布尔字段里塞进2、3、100这些值只要你没加CHECK约束。如果你依赖这个字段做条件判断WHERE is_deleted 1和WHERE is_deleted在语义上会有差异因为后者会把任何非0值都当作真。更稳妥的做法是建表时加CHECK约束或者用ENUM(0,1)。不过从性能角度考虑TINYINT配合应用层强制赋值反而更高效。我在实际项目中的习惯是状态值用TINYINT并且永远只用0和1两种值命名上叫flag或is_xxx。如果字段未来可能扩展为多状态就会直接用TINYINT存状态码比如0待处理、1进行中、2已完成、3失败而不是用布尔命名去硬撑。3. 小数类型FLOAT、DOUBLE、DECIMAL选错等于埋雷3.1 从精确性角度理解三种类型先记住一句话FLOAT和DOUBLE是浮点数DECIMAL是定点数。浮点数用二进制近似存储所以很多十进制小数无法精确表示定点数以字符串或压缩十进制方式存储能精确表示指定精度内的十进制小数。FLOAT占4字节约7位有效数字。DOUBLE占8字节约15~16位有效数字。DECIMAL(M,D)M是总位数D是小数点后位数存储长度随精度变化。做金融、计费、结算这类业务钱和相关金额字段必须用DECIMAL。这几乎是行业铁律没有什么商量余地。浮点数在加减乘除时产生的误差虽然单个看很小但累积起来可能对账不平最后查半天查不出原因其实根子就在类型选错。3.2 DECIMAL的精度设计与存储开销DECIMAL(M,D)里M最大65D最大30且D不能大于M。举个例子DECIMAL(10,2)表示总位数10位其中小数位2位也就是说整数部分最多8位能表达的最大值是99999999.99。实际应用中DECIMAL的精度选择不能拍脑袋。M和D设得过大存储空间会膨胀设得过小数据插入时超出范围会被截断或报错。比如DECIMAL(5,2)存999.99没问题存1000.00就超了因为整数部分1000有4位加上小数2位总共需要4位整数2位小数而DECIMAL(5,2)最多容纳3位整数2位小数。存储字节数的估算规则是每9位十进制数占4字节剩余位数按1~2位占1字节、3~4位占2字节、5~6位占3字节、7~9位占4字节计算。比如DECIMAL(10,2)整数部分8位小数部分2位总共10位存储时按9位1位分需要415字节。这个细节在数据量极大的表里会影响行大小和索引效率值得算一下。3.3 浮点数的坑与应用场景虽然金融必须用DECIMAL但科学计算、地理坐标、统计分析这类场景FLOAT和DOUBLE也是合理选择因为它们的范围和性能在特定条件下更好。MySQL里FLOAT的默认精度是单精度DOUBLE是双精度。如果你不指定M和DMySQL会按类型本身的精度来存储。浮点数最大的坑在于比较。两个看起来一样的浮点数用WHERE price 19.9可能查不到记录因为19.9在二进制里是个无限循环小数存储时被截断成了近似值。正确的做法是使用范围比较比如ABS(price - 19.9) 0.0001或者把浮点数值转换为DECIMAL后再比较。另外不要把时间戳当整数存也不要把金额当浮点存这两个是我见过最多的两类类型错配。时间戳在MySQL里直接用BIGINT或INT存秒数/毫秒数金额一律DECIMAL。跨过这两条线你的系统就稳了一大半。4. 字符串类型CHAR、VARCHAR、TEXT长度只是其中一个维度4.1 CHAR与VARCHAR的存储机制和性能差异CHAR(N)和VARCHAR(N)里的N是字符数不是字节数。这一点的意义在utf8mb4字符集下尤其明显一个中文字符占3或4字节因此VARCHAR(255)最多能存255个字符而不是255字节。CHAR是定长字符串。MySQL存入CHAR(N)时会用空格填充到N个字符取出时会自动去掉末尾空格。这意味着CHAR适合存储长度基本固定的值比如手机号在有些国家是固定长度、身份证号、订单号、MD5哈希值。定长的好处是存储和比较时不需要额外记录长度信息某些场景下存取效率略高。VARCHAR是变长字符串。它会在数据前用1~2字节记录实际长度255个字符以内用1字节超过用2字节所以存储上会更紧凑。VARCHAR需要额外的长度前缀并且在更新时可能引发行迁移——即行被更新导致变长后原位置放不下整行需要移动到新的页影响性能和页分裂概率。但这通常只在频繁更新且长度变动剧烈的场景下才明显普通业务不需要过度担心。选型逻辑其实很朴素长度固定CHAR。长度会变且上限明确VARCHAR。长度上限极其不明确或超大TEXT但这个要谨慎用。4.2 VARCHAR(255)为什么会成为默认选择很多框架自动生成的建表语句里字符串字段默认都是varchar(255)。这个数字的来源其实和MySQL的索引限制有关——老版本InnoDB索引键最长767字节为什么是767因为早期MyISAM对索引前缀字节数限制为255个字符3字节后续受单列索引长度限制而utf8字符集下一个字符最多3字节2553765刚好接近但不能超过767所以varchar(255)成了最安全的大字符串选择。但在utf8mb4下一个字符最多4字节varchar(255)意味着最坏情况需要255*41020字节已经超过单列索引的3072字节限制的1/3。如果你需要给一个varchar(255)字段建前缀索引你会发现前缀长度必须控制在某个值以内。这不是不能用而是说255不是万能安全值。正确的姿势是把字符串字段的实际最大长度量出来再加缓冲。比如用户名最大20字符那用varchar(50)就足够宽裕了没必要255。用户备注可能写到几百字那就varchar(500)或varchar(1000)同时不要对这种长字段随意建索引。4.3 TEXT类型使用时的注意点MySQL的TEXT族包括TINYTEXT255字节、TEXT64KB、MEDIUMTEXT16MB、LONGTEXT4GB。这里说的都是字节上限不是字符数。TEXT类型的几个硬伤第一TEXT字段不能有默认值在MySQL 8.0之前8.0.13之后可以指定默认值但有一定限制如果你一定要默认值就得在应用层处理。第二TEXT字段不能直接建完整索引只能建前缀索引。即使你把索引指定为INDEX(content(100))这种索引也只能用于前缀匹配查询无法像普通字段那样做精确查找排序。第三TEXT字段存储在行外还是行内InnoDB对变长字段有一个页外存储机制当记录长度超过页大小阈值时会把长的字段放到单独的页。这会导致对这些字段的查询需要额外的IO性能远低于普通短字段。所以不要把大段文章、日志、JSON直接放在业务核心表的TEXT字段里还频繁查询。如果只是存文章内容、评论正文这类写多读少且很少参与条件过滤的数据MEDIUMTEXT或LONGTEXT是合适的。如果还需要对内容做全文搜索那应该考虑全文索引或者外接搜索引擎而不是在TEXT字段上硬扛。4.4 BINARY、VARBINARY与BLOBBINARY和VARBINARY类似CHAR和VARCHAR但存的是二进制字节串。BLOB族存二进制大对象。日常Web开发中BLOB多用于存图片、文件等原始数据但我不建议在关系库里直接存大文件因为这样会让数据库体积飞速膨胀备份、迁移都会变得很痛苦。文件应该放对象存储数据库里只存URL或对象键。BINARY还有个冷门用途存储需要精确比较且大小写敏感的字符串。CHAR和VARCHAR在默认排序规则下通常不区分大小写而BINARY比较时按字节值比较天然区分大小写。比如存用户密码的校验盐时用BINARY可以避免大小写折叠问题但这种场景不常见了解即可。5. 日期时间类型TIMESTAMP、DATETIME、DATE、TIME还有时区问题5.1 四个基础类型各自的边界MySQL日期时间类型有DATE、TIME、DATETIME、TIMESTAMP、YEAR。日常主要用前四个。DATE年月日范围1000-01-01到9999-12-313字节。TIME时分秒可以带小数秒范围-838:59:59到838:59:593字节加小数秒存储。DATETIME年月日时分秒范围1000-01-01 00:00:00到9999-12-31 23:59:598字节8.0中支持小数秒需要额外字节。TIMESTAMP范围从1970-01-01 00:00:01 UTC到2038-01-19 03:14:07 UTC4字节8.0中支持小数秒会额外占用存储时会从当前时区转换到UTC存储查询时再转回当前时区。这里面最容易被忽视的是TIMESTAMP的上限2038年问题。很多系统现在还活得好好的但如果你设计一个需要长期运行且会存几十年后日期的系统TIMESTAMP就不够用了。DATETIME能存到9999年显然更稳妥。5.2 时区问题怎么选TIMESTAMP和DATETIME最重要的差异不是存储范围而是时区处理。TIMESTAMP存的是UTC时间戳。也就是说无论数据库服务器的时区设置是什么存进去的是绝对时刻查询出来时MySQL会根据time_zone变量转成对应时区的本地时间。DATETIME则纯粹是个日历时间不带时区语义你存什么就是什么查询结果也是原样。这两者的取舍直接决定业务在不同时区下怎么表现。对于面向全球用户的应用我建议用TIMESTAMP存事件发生的绝对时刻比如订单创建时间、消息发布时间这样不同时区的用户看到的是各自本地化后的时间。对于用户自己输入的本地时间比如用户设定的生日、闹钟、会议时间用DATETIME更合适因为这种时间本来就与具体时区绑定不应该被全局时区转换影响。还要注意TIMESTAMP字段在插入时如果不显式赋值MySQL会默认填当前时间CURRENT_TIMESTAMP并且在更新时也可能自动更新。这个行为可以通过DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP控制特别适合做updated_at字段。DATETIME在8.0里也可以设置默认值为CURRENT_TIMESTAMP和ON UPDATE语法的但有些老版本不支持需要确认版本。5.3 日期的存储优化与查询规范如果只是存年月用DATE类型就够了不要用VARCHAR(7)拼成2026-03否则排序、范围查询、日期函数全废。如果只需要年份甚至可以直接用YEAR类型1字节。日常查询中对日期字段做比较时建议直接使用标准格式字符串WHERE create_time 2026-01-01 AND create_time 2026-02-01。尽量避免在字段上套函数比如WHERE DATE_FORMAT(create_time, %Y-%m-%d) 2026-01-01这样会让索引失效。替代方案是用范围查询。关于小数秒MySQL 8.0支持TIMESTAMP(6)、DATETIME(6)这样的精度定义括号里的数字表示小数秒位数。需要精确到毫秒或微秒的业务直接用这种带精度的类型别用VARCHAR存毫秒数。这里有个反直觉的点在8.0之前DATETIME不支持小数秒很多系统用BIGINT存毫秒时间戳。8.0之后用DATETIME(3)存毫秒并配上索引查询可读性和范围扫描效率都比BIGINT更好。5.4 实际案例时区切换后数据错乱我之前遇到过一个问题系统原本部署在国内所有时间都用DATETIME存北京时间。后来业务扩展服务器迁到海外机房连接串上的时区参数被设置成UTC。结果所有新增的DATETIME字段在插入时没有经过任何转换应用层却按本地时间生成数据最后库里出现了一批对不上号的记录——比如本地时间22点写入数据库显示14点前后查询结果不一致。这个问题的根源是应用层和数据库层的时区假设不一致而DATETIME类型又不会自动做任何换算。解决方法是统一约定应用层一律用UTC时间戳进行逻辑处理需要展示时再转本地数据库层面统一time_zone 00:00并把绝对时刻字段用TIMESTAMP存储。经过这次踩坑我对时区问题特别敏感也建议大家在新项目设计阶段就明确时区口径别等到线上故障才追悔。6. 枚举、集合、JSON与其他少有人用却很好用的类型6.1 ENUM和SET能用但要用得克制MySQL的ENUM类型在表面上是一个下拉选择框底层其实是用整数存储的。ENUM(small,medium,large)内部会把三个值分别映射成1、2、3。好处是存储紧凑1~2字节查询时能直接按字符串比较坏处也非常明显值变更麻烦要增加一个新的枚举值需要ALTER TABLE修改字段定义而ALTER TABLE在数据量大时可能锁表代价很高。排序规则会按照内部索引顺序而不是字符串的字母顺序。比如上面的ENUM按size排序会返回small、medium、large而不是alphabetical。如果插入不在枚举列表里的值在严格模式下会报错非严格模式下会被截断为空字符串并告警很容易产生脏数据。所以我现在的建议是如果枚举值非常稳定且数量很少比如性别、布尔状态ENUM勉强可用否则还是用TINYINT或VARCHAR配合应用层字典表灵活度更高。SET的定位是多选值底层按位存储如果业务需要一个字段表达多个标签SET在空间上确实省但同样有变更和查询可读性的问题非必要不用。6.2 JSON类型灵活与反范式MySQL从5.7开始支持JSON类型8.0大幅增强了相关函数。JSON列在存储时会自动校验格式非法JSON会被拒绝这比用VARCHAR存JSON然后依靠应用层解析要可靠得多。JSON类型带来的诱惑是灵活今天加个字段明天改个结构不用改表结构。这让很多团队在业务早期选择了JSON字段来快速迭代。但它也带来几个代价第一JSON字段无法直接按内部某个键值做索引虽然8.0支持CREATE INDEX通过生成的列来间接索引但写起来绕。第二JSON列的更新是整体覆盖的。你用JSON_SET修改其中一个键底层会把整个JSON文档重新写入新位置行变大、写放大大文档的更新代价很高。第三JSON字段让逻辑变得不透明。数据库里到底是什么结构只有应用代码知道换人来维护时容易出问题。我的建议是JSON适合存查询频率低、结构变化快的附属信息比如第三方接口返回的原始数据、不同渠道的扩展属性。核心业务字段一定要规范化成独立列不要为了省事把所有配置塞进一个JSON里然后天天去取某几个键。6.3 空间类型GIS业务的标配MySQL 8.0提供GEOMETRY、POINT、LINESTRING、POLYGON等空间数据类型配合ST_Distance_Sphere、ST_Within等函数可以做一些基础的地理计算。如果你只是需要在坐标上做简单的距离筛选用DOUBLE存经纬度配合Haversine公式也能工作但精度和性能都不理想。需要开发地理位置相关功能时把经纬度用POINT类型存再建SPATIAL索引才是比较正规的做法。空间索引的底层用的是R树在范围和包含查询上的表现优于传统B树。不过说实话MySQL的空间功能远不如专业GIS引擎或Elasticsearch等方案强大如果业务复杂度高还是应该考虑专用服务。7. 从热门搜索词看大家最想解决的问题整理这篇内容的时候我顺手翻了一圈热门搜索词发现大家搜索频率最高的几个问题非常集中mysql安装教程、mysql主从复制、mysql锁原理及面试题、mysql事务处理以及一个特别具体的需求——把远程库的这张表同步到本地。这些问题看起来和数据类型无关但仔细想想其实都和数据类型有关。比如远程库的这张表同步到本地。当你用mysqldump导数据再导入本地时如果两端MySQL版本不一致或者源表的字段类型用了新特性如CHECK约束、JSON默认值导入就可能报错。我自己就遇过源端是5.7、本地是8.0row_format和字符集规则不一样导致导入后中文乱码的问题。解决方法是先用SHOW CREATE TABLE查看源表的完整定义重点核对字符集utf8mb4还是utf8、表引擎、小数精度和时间类型。再比如事务处理和锁机制它们和数据类型的关系也密不可分。一个DECIMAL字段在高并发下做加减法时因为行锁的存在更新会串行化。如果你把金额存成DOUBLE可能出现两个事务各自读到近似值再写回最终数据不一致的概率会显著上升。数据类型不仅影响存储还影响事务的一致性和并发安全。还有一个高频词是数据类型强制转换。MySQL有一个非常智能但也非常坑的隐式转换机制比如SELECT * FROM user WHERE id 123abc在非严格模式下会把字符串转换成数字123查出来的结果可能出乎意料。经验法则永远不要让数据库在WHERE条件里帮你做类型转换查询参数和字段类型要严格一致。如果应用层传参的类型不可控在SQL里显式CAST一下都比让它隐式转换靠谱。关于mysql设置默认值为0这往往是指DEFAULT 0的用法。但这个看起来简单的操作在字符串类型上有个陷阱VARCHAR字段设置DEFAULT 没问题设置DEFAULT NULL和DEFAULT 语义完全不同。NULL会影响索引优化的判断COUNT(字段)不会统计NULL值应用层读出来也容易遇到空指针。所以我在建表时都会问一遍这个字段真的需要允许NULL吗如果业务上逻辑上不可能为空那就NOT NULL DEFAULT 或NOT NULL DEFAULT 0省一大串麻烦。8. 关于数据类型我最后的几点实践体会写到这里主干内容基本讲完了。最后分享一些我在多个项目里沉淀下来的实操习惯不算什么高深理论但很管用。第一字符集统一为utf8mb4。MySQL 8.0默认就是utf8mb4老项目迁移时也要尽量统一。很多人纠结utf8mb4是不是比utf8更占空间确实占但换来的是能存Emoji和四字节扩展字符以及不出现问号乱码。一个字符可能出现4字节没错但实际业务里绝大多数内容还是1~3字节为主这点空间成本几乎可以忽略。第二建表时给每个字段写上COMMENT。这个习惯太重要了。一个团队协作的项目里字段没有注释三个月后连写的人都可能记不清当时为什么这么设计。数据类型本身能传递一部分信息但业务语义还是要靠注释。我见过一个字段叫status注释空白后来排查问题才发现它肚子里装了七八种状态这种代码能把你逼疯。第三用SHOW CREATE TABLE校验你的表定义。不管是用可视化工具建的还是ORM自动生成的我都建议上线前DBA或者有经验的人过一遍SHOW CREATE TABLE。这一步能看到隐藏的字符集继承、排序规则、行格式、索引冗余。比如VARCHAR(255)在utf8mb4下能建前缀索引的长度是多少、有没有额外生成了奇怪的全表索引都能在这条命令里发现。第四不要迷信空间越省越好。有些新手为了让表小一点把所有数值字段都精简到最极限的TINYINT或SMALLINT。实际上让字段类型偏小未来业务增长一点就会撞天花板稍微留一档空间比如本来可以用SMALLINT的用INT换来的是几年之内不必做结构变更。结构变更在数据量大的时候代价极大不只是ALTER TABLE本身耗时一堆依赖它的查询计划、缓存、ORM映射都得重新调整。第五警惕ORM框架的隐式类型映射。MyBatis、Hibernate等框架在读取数据库结果集时可能会把DECIMAL转成BigDecimal或Double把DATETIME转成本地日期类型。如果你在Java里用一个double字段接收DECIMAL(10,2)精度会在转换时丢失。这种问题不体现在SQL层而是完全藏在应用代码里排查起来最耗时间。任何时候做类型透传都要在接口层写个测试断言确保值域和精度没有变化。最后想多说一句数据类型是数据库设计的最小决策单位也是所有上层优化的基石。索引好不好用查询快不快数据一致不稳定很多问题追根溯源都能落到类型选择上。与其在出问题后疯狂补丁不如在建表第一步就多花五分钟认真想清楚。希望大家看完这一篇之后再次打开建表语句时能多问自己几个问题这个字段会不会出现负数这个字符串最长会有多长日期时间需要跨时区吗值会不会超过当前类型的范围这些问题的答案会引导你做出更稳妥的判断。