MySQL常用数据类型详解，开发选对类型少踩坑

MySQL常用数据类型详解开发选对类型少踩坑摘要写SQL建表的时候数据类型选得好不好直接影响存储空间、查询性能、数据准确性。很多同学开发时随便写个varchar(255)、int一通乱用后期出现溢出、精度丢失、存储浪费等各种坑。今天梳理MySQL四大类常用数据类型数值类型、字符串类型、二进制类型、日期时间类型同时讲清选型思路与避坑要点。一、MySQL数据类型整体分类MySQL字段常用分为四大类数值类型整数、浮点数、定点小数用来存数字、金额、数量等字符串类型字符文本存储姓名、地址、描述、枚举选项二进制类型存储原始二进制字节图片文件字节流等日期时间类型存储年月日、时分秒、时间戳面向对象开发映射关系Java/其他语言的类 → MySQL表类属性 → 表字段对象实例 → 表的一行记录。定义字段必须指定合适的数据类型。二、数值类型整数、浮点、定点数数值分为整数、浮点数、DECIMAL定点小数。注意区分有符号、无符号无符号只能存非负数存储上限扩大一倍。1. 整数类型对照表类型字节有符号范围无符号范围说明TINYINT1-128 ~ 1270 ~ 255BOOL本质就是TINYINT(1)0代表假非0代表真SMALLINT2-32768 ~ 327670 ~ 65535小整数适合状态、少量计数MEDIUMINT3-8388608 ~ 83886070 ~ 16777215较少使用INT /INTEGER4-2147483648 ~ 21474836470 ~ 4294967295最常用整型BIGINT8-9223372036854775808 ~ 92233720368547758070 ~ 18446744073709551615主键ID、大数量务必用BIGINT防止溢出小提示INT(M)里的M不是存储长度只是显示宽度不改变取值范围很多新手容易踩这个坑。2. 浮点与定点DECIMAL类型字节特点FLOAT(M,D)4单精度浮点数约7位有效精度会有精度丢失不要存金额DOUBLE(M,D)8双精度浮点约15位有效精度依然存在精度损失DECIMAL(M,D)动态定点小数无精度丢失金额、财务计算必须用它。M总位数最大65D小数位最大30。✅开发经验钱不要用float/double浮点数二进制存储会出现0.10.2≠0.3的经典问题财务场景一律选择DECIMAL。三、字符串类型CHAR、VARCHAR、TEXT怎么选字符串是日常开发最高频使用的类型CHAR固定长度VARCHAR可变长度TEXT大文本三者特性差异巨大。1. CHAR 和 VARCHARCHAR(M) 固定长度M单位是字符0‑255。不足长度时数据库会右侧补空格填满读取时自动去掉尾部空格。适用长度固定数据MD5、身份证号、邮编、学号。优点读取效率高缺点数据短也占满全部空间浪费磁盘。VARCHAR(M)可变长度M代表最大字符数最大65535字节。真实占用空间等于实际数据额外1‑2字节记录长度。适用长短不一的数据姓名、邮箱、地址。优点节省存储空间缺点性能略低于CHAR。示例理解CREATETABLEvc(vVARCHAR(4),cCHAR(4));INSERTINTOvcVALUES(ab ,ab );SELECTCONCAT((,v,)),CONCAT((,c,))FROMvc;CHAR存入ab 取出时末尾空格被删除VARCHAR保留尾部空格超出定义长度会截断告警。✅选型口诀长度固定选CHAR长度变化选VARCHAR。2. TEXT系列大文本TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT存储大段文本。类型最大字节TINYTEXT255TEXT65535MEDIUMTEXT16777215LONGTEXT4GBVARCHAR vs TEXT核心区别存储位置VARCHAR短数据在行内存储TEXT全部存在溢出页行只存地址。索引VARCHAR可以正常建普通索引TEXT不支持普通索引仅支持全文索引。使用场景短文本需要索引检索用VARCHAR文章内容、长备注这种极少作为查询条件的大文本用TEXT。3. 枚举ENUM与集合SETENUM(a,b,c)单选只能选列表其中一个值内部存整数节省空间。SET(a,b,c)多选可以同时选中0个或者多个选项最多64个元素。业务中谨慎使用ENUM/SET业务选项变更时需要修改表结构维护成本高。四、二进制类型BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB。和字符串区分字符串存字符二进制类型直接存原始字节。业务尽量避免把图片、文件直接存入BLOB推荐存文件路径文件放磁盘/对象存储。排序小知识点字符串按字符集排序二进制列使用_bin排序规则直接按字节码对比。五、日期时间类型类型字节时间范围格式DATE31000‑01‑01 ~9999‑12‑31YYYY‑MM‑DD只存日期TIME3-838:59:59 ~ 838:59:59hh:mm:ss可以存跨天时长YEAR11901‑21554位年份DATETIME81000‑01‑01 00:00:00 ~9999‑12‑31 23:59:59日期时间开发最常用TIMESTAMP41970‑01‑01 ~2038‑01‑19时间戳4字节2038年时间溢出问题⚠️重点避坑TIMESTAMP受2038时间炸弹限制新项目优先使用DATETIME不要用TIMESTAMP。fsp参数DATETIME(3)代表毫秒精度0‑6位小数秒。获取时间函数NOW()、CURDATE()、CURTIME()分别获取当前时间、当前日期、当前时刻。六、开发实战总结选型最佳实践整型状态少量数字用TINYINT普通业务id用BIGINT防止int溢出。金额拒绝float/double强制DECIMAL(总位数,小数位)。字符串固定长度选CHAR长短不定选VARCHAR大文章内容选TEXT。不要无脑写VARCHAR(255)。时间优先DATETIME避开TIMESTAMP的2038问题需要毫秒就设置fsp精度。文件图片不要BLOB存储保存文件路径。ENUM/SET少用业务枚举值建议放到业务代码不要锁死在数据库字段。数据库字段类型设计是表设计的根基类型选不对后期数据溢出、索引失效、存储膨胀重构代价巨大。建表多花两分钟思考字段类型后面少改无数bug。