【目录】1.掌握数据类型分类2.掌握使用常用数据类型1.常用数据类型分类在面向对象软件开发的过程中通常会先进行需求分析从而得到类和属性。类是面向对象中的概念对应到数据库中的概念就是实体类中的属性则对应实体中的属性。实体通常以表的形式存在每个实体对应一张表表中的每条记录数据行就是实体的一个实例每条记录又包含若干字段或称为列每个字段代表实体的一个属性。如下图每一行是实体的一个实例而每一列又代表了实体的属性。如果要定义实体的属性就需要为属性命名并指定合适的数据类型。与其他编程语言类似SQL 中也规定了用于描述属性的数据类型。常用的数据类型有以下几类数值类型字符串类型二进制类型日期类型。建议数据类型不用死记细节抓住“整数用 INT/BIGINT、金额用 DECIMAL、短字符串用 VARCHAR、长文本用 TEXT、时间用 DATETIME”这五个主力就能覆盖绝大多数场景剩下的知道有、用到再查即可——真正要记的是选型原则而不是每个类型的字节数。2.数值类型2.1常用类型举例1.整数类型类型大小说明BIT[(M)]默认 1 bit位值类型。M 表示每个值的位数取值范围为 1 ~ 64。如果省略 M默认为 1。TINYINT[(M)]1 byte取值范围 -2^7 ~ 2^7-1无符号取值范围 0 ~ 2^8-1。SMALLINT[(M)]2 byte取值范围 -2^15 ~ 2^15-1无符号取值范围 0 ~ 2^16-1。MEDIUMINT[(M)]3 byte取值范围 -2^23 ~ 2^23-1无符号取值范围 0 ~ 2^24-1。INT[(M)]4 byte取值范围 -2^31 ~ 2^31-1无符号取值范围 0 ~ 2^32-1。INTEGER[(M)]4 byteINT[(M)] 的同义词。BIGINT[(M)]8 byte取值范围 -2^63 ~ 2^63-1无符号取值范围 0 ~ 2^64-1。BOOL1 byteTINYINT(1) 的同义词。值为零被认为是false非零值被认为是 true。对于整数类型我们不建议用M规定每个值的位数原因是M的作用是配合ZEROFILL规定显示时最少几位纯粹是个显示格式对数据和存储毫无影响而且已经被官方废弃。接下来进行讲解了解即可。比如INT(4) 和 INT(6)都是四字节的存储的单位只是在配合ZEROFILL时进行打印会有差别。假设存储5如果配合ZEROFILL那么INT(4)打印出来是0005而INT(6)打印出来是000005如果不配合ZEROFILL那么打印出来仍是INT(4)和INT(6)打印出来都是5而如果存储数据位数大于指定位数比如INT(4)存储123456那么无论有没有ZEROFILL都会打印出123456另外对于无符号整数我们可以指定为unsigned但是不建议这么做。比如int存不下那么unsigned int 也不一定存的下与其这样还不如升级为bigint。2.浮点数类型FLOAT[(M,D)]4 byte单精度浮点型M 是总位数D 是小数点后面的位数大约可以精确到小数点后 7 位。DOUBLE[(M,D)]8 byte双精度浮点型M 是总位数D 是小数点后面的位数大约可以精确到小数点后 15 位。浮点数类型与整数类型相似它的MD也是可以省略的并且建议省略。其中M表示他可以存储浮点数的位数这个位数指的是整数部分和小数部分相加和而D则表示的是小数部分的位数比如float(5,2)最大存储就是999.99一旦超过这个数那么就会报错。这与float最大能存多少无关而是以M和D为主在日常开发中凡类型大小有符号最小值有符号最大值无符号最小值无符号最大值TINYINT1 Byte-1281270255SMALLINT2 Byte-3276832767065535MEDIUMINT3 Byte-83886088388607016777215INT4 Byte-2147483648214748364704294967295BIGINT8 Byte-2^632^63-102^64-1是涉及到精确数据都不建议用float和double而是用decimalDECIMAL[(M,D)]动态不存在精度损失M 是总位数D 是小数点后的位数。DECIMAL 的最大位数(M)为 65最大小数位数(D)为 30。如果省略 M则默认为 10如果省略 D则默认为 0。M 中不计算小数点和负数的负号如果 D 为 0则值没有小数点和小数部分。对于decimal它的M,D是强烈建议写的原因如下它同float和double并不一样如果说它存储了一个12.45默认的100意味着小数部分是0会导致存储的时候存储成12小数部分直接丢失这是非常危险的。另外decimal是精确的这得益于它的特殊存储机制它并不把数据转换成二进制而是以十进制的方式进行存储这样就避免了因为转二进制而造成的精度丢失问题。3.总结MD在常用的这些数值类型中无论是整数的M亦或是浮点数的MD都是不建议指定的因为指定了可能会出问题并且不指定的情况下其用法就同java/c的编程语言一样用法会根据真正的最大/最小数值进行判断存储。而decimal除外强烈建议decimal进行指定。2.2 常用数值类型的范围3.字符串类型3.1 char(n) 和 varchar(n)这里的n表示的是可容纳字符个数的上限例如char(10)表示最多能容纳长度为10的字符串至于具体细节见下表一、对比对比项CHAR(n)VARCHAR(n)长度性质固定长度可变长度声明含义固定 n 个字符最多 n 个字符实际字符数永远 n按输入≤ n不够 n 时自动补空格凑满 n不补没用的空间被空格占掉不分配不浪费内容字节数按补满后的 n 个字符算按实际内容算长度前缀详解见后无1~2 字节n≤255 用 1 字节n255 用 2 字节总字节数n × 每字符字节数实际内容字节 1~2 字节超长时报错或截断报错或截断存取性能稍快定长好定位稍慢变长需计算最大长度0~255 字符0~65535 字节受行上限限制n 是字符还是字节字符数字符数适用场景长度固定的短数据长度不固定的数据一句话占满 n 个字符位不够自动补空格最多 n 个字符用多少占多少不预留举例utf8mb4 字符集存入内容CHAR(10) 实际占用VARCHAR(10) 实际占用abcabc 10 字符10 字节abc3 字符 1 字节前缀 4 字节中中 10 字符39 12 字节中1 字符 1 字节前缀 4 字节abcdefghij10 字符10 字节10 字符 1 字节前缀 11 字节二、长度前缀总结对比项说明是什么VARCHAR 每条记录里额外占的 1~2 字节用来记录“这列实际占多少字节”为什么需要VARCHAR 是变长的没有它 MySQL 就不知道每列读到哪算完无法正确读取下一列占几字节n ≤ 255 → 1 字节n 255 → 2 字节存的是什么实际内容的字节长度注意不是字符数不计入字符个数CHAR 有吗没有。CHAR 定长读完 n 个字符就知道下一列在哪是浪费吗不是是“可变长度”必须付出的代价比喻CHAR 像固定大小的快递柜不用贴标签VARCHAR 像大小不一的包裹必须贴标签写明多大3.2 text系列一、TEXT系列 与 VARCHAR 对比以 TEXT 为典型对比项VARCHAR(n)TEXT长度性质可变有上限可变有上限上限最多 n 个字符最大 65,535 字节能不能写 n必须写如VARCHAR(100)不能写写TEXT(100)无效长度前缀1~2 字节1~4 字节实际占用实际内容 1~2 字节实际内容 1~4 字节默认值可以有不能有短文本用 VARCHAR长文本才用 TEXT 系列。二、TEXT 系列总结类型最大长度字节换算长度前缀约存汉字数utf8mb4典型用途TINYTEXT2552⁸ - 11 字节约 85 个极短文本、摘要TEXT65,5352¹⁶ - 12 字节约 21,845 个文章、评论、日志MEDIUMTEXT16,777,2152²⁴ - 13 字节约 559 万个长文章、大段 JSONLONGTEXT4,294,967,2952³² - 14 字节约 14 亿个书籍、原始数据3.3 blob系列一、BLOB 系列与 TEXT 系列对比以 BLOB 和 TEXT 为典型对比项TEXTBLOB存什么文本字符二进制字节字符集有无最大长度65,535 字节65,535 字节长度前缀2 字节2 字节实际字节数按真实内容算按真实内容算数据库怎么理解按字符集翻译成文字当原始字节校验校验字节是否符合字符集不校验任意字节都能存比较/排序按字符集规则按字节值大小写转换支持不支持算字符数CHAR_LENGTH()有效只能算字节数对应关系TEXT 系列BLOB 系列最大长度字节TINYTEXTTINYBLOB255TEXTBLOB65,535MEDIUMTEXTMEDIUMBLOB16,777,215LONGTEXTLONGBLOB4,294,967,295二、BLOB 系列总结类型最大长度字节换算长度前缀存什么TINYBLOB2552⁸ - 11 字节二进制数据BLOB65,5352¹⁶ - 12 字节二进制数据MEDIUMBLOB16,777,2152²⁴ - 13 字节二进制数据LONGBLOB4,294,967,2952³² - 14 字节二进制数据三、差异的意义具体例子了解即可核心意义一句话让数据库知道“这堆字节到底是不是文字”从而决定怎么处理它。差异意义有字符集 vs 无字符集TEXT 能翻译成文字BLOB 只当原始字节按字符规则比较 vs 按字节值比较TEXT 能正确排序文字BLOB 只能按字节排支持大小写转换 vs 不支持TEXT 能UPPER()BLOB 不能能算字符数 vs 只能算字节数TEXT 的CHAR_LENGTH()有效BLOB 不行校验字节合法性 vs 不校验TEXT 会拒绝非法字节BLOB 任意字节都能存如果混用会出问题用 TEXT 存图片 → 字节可能不符合字符集插入报错或图片损坏用 BLOB 存文章 → 能存但没法按字符处理显示可能乱码打个比方TEXT 贴了“文字”标签的箱子管理员会按文字方式整理BLOB 贴了“二进制”标签的箱子管理员只按大小堆放不解读内容箱子本身一样大但标签决定了管理员怎么处理它。四、一句话总结BLOB 系列和 TEXT 系列一一对应最大长度完全相同255、65535、16777215、4GB区别只在于 TEXT 有字符集、按文字处理BLOB 无字符集、按原始字节处理。这个区别的意义是让数据库知道数据是“文字”还是“二进制”从而用正确的方式存储、比较和读取。4.日期类型一、五种类型分别什么时候用类型什么时候用例子DATE只需要日期不需要时间生日、入职日期、发布日期TIME只需要时间不需要日期营业时间、课程时长、闹钟DATETIME需要日期 时间日常场景创建时间、订单时间、评论时间TIMESTAMP需要日期 时间且跨时区或自动更新跨时区系统、最后修改时间YEAR只需要年份毕业年份、车型年份三、DATETIME 和 TIMESTAMP 到底怎么选场景选哪个原因日常记录时间DATETIME范围大1000~9999不受时区影响跨时区系统TIMESTAMP自动按时区转换需要自动更新TIMESTAMP原生支持ON UPDATE CURRENT_TIMESTAMP需要范围超过 2038 年DATETIMETIMESTAMP 只到 2038 年需要存生日、很久以前DATETIMETIMESTAMP 不支持 1970 以前一句话日常用 DATETIME跨时区或自动更新用 TIMESTAMP。四、记法口诀需求用哪个只要日期DATE只要时间TIME日期 时间DATETIME跨时区、自动更新TIMESTAMP只要年份YEAR五、一句话总结只关心“哪一天”用 DATE只关心“几点”用 TIME关心“哪一天几点”用 DATETIME跨时区或自动更新用 TIMESTAMP只要年份用 YEAR。日常场景用 DATETIME跨时区用 TIMESTAMP。5.一句话总结常用类型再次建议数据类型不用死记细节抓住“整数用 INT/BIGINT、金额用 DECIMAL、短字符串用 VARCHAR、长文本用 TEXT、时间用 DATETIME”这五个主力就能覆盖绝大多数场景剩下的知道有、用到再查即可——真正要记的是选型原则而不是每个类型的字节数。