大家好我是你们的数据库学习伙伴。今天是MySQL系列教程的第2天我们将深入探讨MySQL的数据类型。选错数据类型可能会导致数据丢失、性能下降甚至系统崩溃所以这一课非常重要文章目录一、为什么数据类型很重要1.1 一个真实的踩坑案例1.2 选错数据类型的后果二、数值类型详解2.1 整数类型2.2 浮点类型与定点类型三、字符串类型详解3.1 CHAR vs VARCHAR3.2 TEXT与BLOB类型四、日期时间类型详解4.1 五种日期时间类型对比4.2 踩坑TIMESTAMP的2038年问题五、JSON类型MySQL 5.7六、数据类型选择最佳实践6.1 经验总结表6.2 设计原则七、面试高频考点考点1INT(11)的11是什么意思考点2VARCHAR(255)和VARCHAR(100)的区别考点3CHAR和VARCHAR的区别考点4为什么金额不能用FLOAT/DOUBLE八、总结九、下一步预告十、参考资料互动话题一、为什么数据类型很重要1.1 一个真实的踩坑案例在存储商品价格时使用FLOAT类型CREATETABLEproducts(idINTPRIMARYKEYAUTO_INCREMENT,nameVARCHAR(100),priceFLOAT-- 错误的选择);当计算订单金额时出现了这样的情况INSERTINTOproducts(name,price)VALUES(iPhone 15,5999.99);SELECT*FROMproducts;-- 结果5999.9899902343750.01元的误差在财务系统中是不可接受的这就是浮点数精度问题导致的悲剧。1.2 选错数据类型的后果问题类型具体表现影响程度数据丢失TINYINT存储大数导致溢出严重精度问题FLOAT/DOUBLE存储金额严重性能下降使用过大的数据类型中等存储浪费VARCHAR(500)存短文本轻微兼容性问题TIMESTAMP的2038年限制严重二、数值类型详解2.1 整数类型MySQL提供了5种整数类型它们的区别主要在于取值范围和存储空间类型存储空间有符号范围无符号范围适用场景TINYINT1字节-128 ~ 1270 ~ 255状态、布尔值SMALLINT2字节-32768 ~ 327670 ~ 65535小范围计数MEDIUMINT3字节-8388608 ~ 83886070 ~ 16777215中等范围IDINT4字节-21亿 ~ 21亿0 ~ 43亿主键ID、计数BIGINT8字节极大范围极大范围分布式ID、大数经验之谈状态字段用TINYINT如0-禁用1-启用主键ID用INT UNSIGNED AUTO_INCREMENT用户ID如果可能超过21亿用BIGINT-- 用户表设计示例CREATETABLEusers(idINTUNSIGNEDAUTO_INCREMENTPRIMARYKEY,statusTINYINTUNSIGNEDDEFAULT1COMMENT0-禁用 1-启用,ageTINYINTUNSIGNEDCOMMENT年龄0-255足够,login_countINTUNSIGNEDDEFAULT0);2.2 浮点类型与定点类型类型存储空间精度适用场景注意事项FLOAT4字节约7位科学计算有精度损失DOUBLE8字节约15位科学计算有精度损失DECIMAL变长自定义金额计算精确计算重点DECIMAL用于金额-- 正确的金额存储方式CREATETABLEorders(idINTPRIMARYKEYAUTO_INCREMENT,total_amountDECIMAL(10,2)COMMENT总金额整数8位小数2位,discountDECIMAL(10,2)DEFAULT0.00);-- DECIMAL(M, D) 说明-- M总位数精度最大65-- D小数位数标度最大30-- DECIMAL(10, 2) 可以存储-99999999.99 到 99999999.99踩坑提醒永远不要使用FLOAT或DOUBLE存储金额银行、电商、支付系统必须使用DECIMAL。三、字符串类型详解3.1 CHAR vs VARCHAR这是面试中最常问的问题之一特性CHARVARCHAR存储方式固定长度变长存储空间始终占用定义长度实际长度1~2字节性能更快无长度计算稍慢适用场景固定长度数据变长数据最大长度255字符65535字节-- CHAR适合存储固定长度的数据CREATETABLEusers(phoneCHAR(11)COMMENT手机号固定11位,id_cardCHAR(18)COMMENT身份证号固定18位,genderCHAR(1)COMMENT性别 M/F);-- VARCHAR适合存储变长数据CREATETABLEarticles(titleVARCHAR(200)COMMENT标题长度不固定,summaryVARCHAR(500)COMMENT摘要);经验之谈手机号、身份证号、固定编码用CHAR用户名、标题、地址用VARCHAR长度限制要合理不要为了省事都设2553.2 TEXT与BLOB类型类型最大长度存储方式适用场景TINYTEXT255字节外部存储短文本TEXT64KB外部存储文章内容MEDIUMTEXT16MB外部存储长文章LONGTEXT4GB外部存储大文本TINYBLOB255字节外部存储小二进制BLOB64KB外部存储图片、文件MEDIUMBLOB16MB外部存储较大文件LONGBLOB4GB外部存储大文件踩坑提醒TEXT/BLOB类型不能设置默认值查询时不会加载到内存性能较差大文件建议存对象存储OSS/S3数据库只存URL-- 文章表设计CREATETABLEarticles(idINTPRIMARYKEYAUTO_INCREMENT,titleVARCHAR(200),contentTEXTCOMMENT正文内容,cover_imageVARCHAR(500)COMMENT封面图URL不是存图片本身);四、日期时间类型详解4.1 五种日期时间类型对比类型格式范围存储空间适用场景DATEYYYY-MM-DD1000-01-01 ~ 9999-12-313字节生日、纪念日TIMEHH:MM:SS-838:59:59 ~ 838:59:593字节持续时间YEARYYYY1901 ~ 21551字节年份DATETIMEYYYY-MM-DD HH:MM:SS1000-01-01 ~ 9999-12-318字节通用时间TIMESTAMPYYYY-MM-DD HH:MM:SS1970-01-01 ~ 2038-01-194字节记录时间戳CREATETABLEevents(idINTPRIMARYKEYAUTO_INCREMENT,event_dateDATECOMMENT活动日期,start_timeTIMECOMMENT开始时间,created_atDATETIMEDEFAULTCURRENT_TIMESTAMP,updated_atTIMESTAMPDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP);4.2 踩坑TIMESTAMP的2038年问题这是MySQL中一个著名的时间炸弹TIMESTAMP使用4字节存储最大只能表示到2038-01-19 03:14:07 UTC。-- 这个插入在2038年后会失败INSERTINTOevents(created_at)VALUES(2040-01-01 00:00:00);-- ERROR 1292 (22007): Incorrect datetime value解决方案使用DATETIME代替TIMESTAMP升级到MySQL 8.0使用DATETIME(6)支持微秒经验之谈记录创建/更新时间用TIMESTAMP自动时区转换需要存储未来时间如预约用DATETIME生日、历史日期用DATE五、JSON类型MySQL 5.7MySQL 5.7开始原生支持JSON类型可以存储和查询JSON数据。-- 存储商品规格不同商品规格不同CREATETABLEproducts(idINTPRIMARYKEYAUTO_INCREMENT,nameVARCHAR(100),specs JSONCOMMENT商品规格JSON格式);-- 插入JSON数据INSERTINTOproducts(name,specs)VALUES(iPhone 15,{color: 黑色, storage: 256GB, screen: 6.1英寸}),(MacBook Pro,{color: 银色, cpu: M3, ram: 16GB});-- 查询JSON字段SELECT*FROMproductsWHEREJSON_EXTRACT(specs,$.color)黑色;-- 或使用 - 运算符SELECTspecs-$.storageFROMproductsWHEREid1;JSON类型的优势自动验证JSON格式支持索引通过虚拟列支持JSON函数查询经验之谈适合存储不固定的属性如商品规格、用户配置不要滥用固定字段还是建议用列存储频繁查询的JSON字段建议创建虚拟列索引六、数据类型选择最佳实践6.1 经验总结表数据场景推荐类型说明主键IDINT UNSIGNED / BIGINT根据数据量选择状态/枚举TINYINT0-禁用1-启用金额DECIMAL(10,2)精确计算手机号CHAR(11)固定长度邮箱VARCHAR(100)长度可变短文本VARCHAR(255)标题、名称长文本TEXT文章内容日期DATE生日、纪念日时间戳TIMESTAMP/DATETIME记录时间JSON数据JSON灵活属性6.2 设计原则最小够用原则选择满足需求的最小类型精确优先原则金额用DECIMAL不用浮点数固定优先原则固定长度用CHAR变长用VARCHAR简单优先原则能用简单类型不用复杂类型七、面试高频考点考点1INT(11)的11是什么意思答案INT(11)中的11只是显示宽度与存储范围无关。INT始终占用4字节。-- INT(11) 和 INT(3) 存储范围完全相同-- 11只在配合 ZEROFILL 时有视觉效果CREATETABLEtest(num1INT(5)ZEROFILL,num2INT);INSERTINTOtestVALUES(123,123);-- num1显示00123-- num2显示123考点2VARCHAR(255)和VARCHAR(100)的区别答案存储长度小于255时VARCHAR需要1字节存储长度超过255需要2字节VARCHAR(100)和VARCHAR(255)存储’hello’占用空间相同但VARCHAR(255)可能让开发者放松警惕存储过长的数据建议根据实际业务需求设置合理长度考点3CHAR和VARCHAR的区别答案CHAR固定长度VARCHAR变长CHAR性能稍好VARCHAR节省空间CHAR最大255字符VARCHAR最大65535字节存储长度固定用CHAR如手机号变长用VARCHAR考点4为什么金额不能用FLOAT/DOUBLE答案浮点数使用二进制存储小数无法精确表示某些十进制小数如0.1会导致精度丢失。财务计算必须使用DECIMAL。八、总结今天我们学习了MySQL的主要数据类型数值类型TINYINT/INT/BIGINT用于整数DECIMAL用于金额字符串类型CHAR固定长度VARCHAR变长TEXT存大文本日期时间DATE/DATETIME/TIMESTAMP各有适用场景JSON类型灵活存储非结构化数据记住三个原则最小够用、精确优先、简单优先。九、下一步预告Day3MySQL基础SQL语句我们将学习DDL语句CREATE、DROP、ALTERDML语句INSERT、UPDATE、DELETEDQL语句SELECT基础查询实战创建电商订单系统相关表十、参考资料MySQL 8.0官方文档 - 数据类型MySQL技术内幕InnoDB存储引擎 - 数据存储互动话题你在工作中遇到过哪些数据类型相关的坑欢迎在评论区分享你是否曾经用FLOAT存过金额后来怎么解决的你们项目中主键ID用INT还是BIGINT为什么对于TIMESTAMP的2038年问题你们是怎么处理的点赞收藏关注学习MySQL不迷路我们Day3见
