一.数据类型1-1数据类型分类1-2数值类型整型可以指定是有符号的和无符号的默认是有符号的。tinyint类型(有符号在 MySQL 表中建立属性列时我们可以发现列名称在前类型在后。插入数据进行越界测试会报错在MySQL中整型可以指定是有符号的和无符号的默认是有符号的。可以通过unsigned来说明某个字段是无符号的unsigned(无符号)插入数据进行越界测试会报错char a 123456; C/C 中编译器不会报错最多也就是警告这里会发生截断甚至发生隐式转换。 MySQL 中在特定的类型中插入不合规的数据一般会发生拦截不让我们做对应的操作。约束 反过来如果已经有数据被成功插入到 MySQL 中那么说明插入时一定是合法的。 所以在 MySQL 中一般而言数据类型本身也是一种约束 目的是能够保证数据库中的数据是可预期、完整的也就类比做约束下一篇文章我会讲补充C/C 的 char 类型通常只占 1 字节取值范围是 -128 到 127 或 0 到 255。赋值 123456 远超这个范围编译器只会警告实际运行时只保留低 8 位造成数据丢失。MySQL 的数据类型约束更严格插入非法值如将 abc 插入 INT 列会直接报错拒绝执行。这种设计让 MySQL 的数据质量更有保障应用程序写错了也能被数据库拦截避免脏数据入库注意尽量不使用unsigned对于int类型可能存放不下的数据int unsigned同样可能存放不下与其如此还不如设计时将int类型提升为bigint类型。bit类型基本语法bit[(M)] : 位字段类型。M表示每个值的位数范围从1到64。如果M被忽略默认为1。插入数据越界测试1.0x01 / 0x00 是什么这是MySQL命令行对二进制/位类型数据的默认显示方式 0x 开头表示后面的内容是十六进制数 0x01 十进制的 1 0x00 十进制的 02. hex(online) 函数的作用hex() 是MySQL提供的函数它会把字段里的内容转换成十六进制字符串输出当 online 存的是 1 hex(online) 就返回字符串 1 本质上是十六进制表示的结果当 online 存的是 0 hex(online) 就返回字符串 0online 字段的类型是单字节类型只能存储 0 和 1插入大于 1 的值比如 2会触发 Data too long 报错。修改online的bit位当 online 是 BIT 二进制位类型时select * 直接查询原始二进制数据 二进制字符串显示为0x... 0x0061hex(online) 把二进制数据转成十六进制文本 字符串十六进制表示 61online0 把十六进制数据隐式转为十进制整数 数字十进制 97mysql8.0mysql5.7结果可以看到这说明online 字段的类型是字符/字符串类型如 CHAR 、 VARCHAR 或 TINYTEXT 当你插入数字 97 时MySQL会把它当作ASCII码值处理自动转换为对应的字符 a 因为 a 的ASCII码就是97。而直接插入 a 时MySQL直接存储字符本身所以两种插入方式的结果是一样的。bit 字段在显示时是按照ASCII码对应的值显示这个旧版本存在的。如果我们有这样的值只存放 0/1这时可以定义 bit(1)可以节省空间BIT(1) 只占 1 位存 0 或 1BIT(3) 占 3 位可以存 000、001、010、011、100、101、110、111 这 8 种状态这是因为字段在创建时定义的 数据类型 不一样MySQL会根据你定义的类型来决定怎么存储和处理数据。1. 当 online 是 VARCHAR / CHAR 字符串类型时存储把输入的内容当作文本字符直接存起来比如输入 a 就存字符 a 输入 97 也会被转成字符串 97 存储特殊情况如果输入数字 97 MySQL会自动把它转成ASCII码对应的字符 a 存储。显示 SELECT * 时直接显示文本内容比如 a 、 97 。处理 HEX(online) 会把字符的ASCII码转成十六进制比如 a 的ASCII码是97十六进制就是 61 。2. 当 online 是 BIT 二进制位类型时存储把输入的内容当作二进制数值存起来比如输入 1 就存二进制 0000000000000001 输入 97 就存二进制 0000000001100001 。显示 SELECT * 时会以 0x 开头的十六进制形式显示比如 0x0001 、 0x0061 这是MySQL对二进制数据的默认显示方式。处理 HEX(online) 会直接把二进制数值转成十六进制字符串比如 1 、 61 online0 会把二进制数值转成十进制整数比如 1 、 97 。建表时写 online VARCHAR(10) → 就是字符串类型建表时写 online BIT(16) → 就是二进制位类型这是MySQL 8.0 客户端对 BIT 类型的默认显示方式直接显示为0x开头的十六进制字面量而不是像旧版本那样把二进制字节当作 ASCII 字符显示出来比如0x0061显示成a。旧版本MySQL 5.x BIT 字段查询时会把存储的二进制字节直接当作字符串输出客户端就会按 ASCII 码解析成字符。 比如0x61会直接显示成a0x0A会显示成换行符0x00显示成空白这就是你听到的 “按 ASCII 显示” 的来源。MySQL 8.0 以后 客户端默认行为改了BIT 类型不再直接解析为 ASCII 字符而是直接以0x十六进制的形式展示原始字节。 你截图里的0x0001、0x0061就是这个新行为的直接体现。小数类型float基本语法float[(m, d)] [unsigned] : M指定显示长度d指定小数位数占用空间4个字节【补充】MySQL 5.7 及之前 INT(1) 、 INT(3) 、 INT(11) 这种写法是有“显示宽度”的概念的配合 ZEROFILL 可以控制补零显示比如 INT(3) ZEROFILL 存 5 会显示成 005 。MySQL 8.0 以后这个数字就只剩个“占位”作用了完全不影响存储和显示只是个语法兼容的摆设甚至很多地方默认都不推荐写了。很多人会误以为 INT(1) 只能存 0-9 INT(3) 只能存 0-999这是个经典误区它从来都不限制存储的数值范围只影响“显示时的最小宽度”。INT 本身固定是 4 字节能存 -2,147,483,648 到 2,147,483,647 和括号里的数字一点关系都没有。8.0 以后的变化显示宽度功能被废弃了 ZEROFILL 也不推荐用了。现在写 INT 就行写 INT(1) 、 INT(11) 完全没区别MySQL 会忽略括号里的数字插入数据越界测试\G后面的分号可以省略小数float(4,2)表示的范围是-99.99 ~ 99.99MySQL在保存值时会进行四舍五入。注意在合法范围内允许五入但在边界值五入会导致整体的浮点数增多从而超过浮点数对应的范围所以不是所有情况都可以五入。unsigned(无符号会有精度损失decimal语法decimal(m, d) [unsigned] : 定点数m指定长度d表示小数点的位数decimal(5,2) 表示的范围是 -999.99 ~ 999.99decimal(5,2) unsigned 表示的范围 0 ~ 999.99decimal和float很像但是有区别:float和decimal表示的精度不一样插入数据越界测试decimal 和 float 的区别 float 和 decimal 表示的精度不一样。 float 表示的精度大约是 7 位。会发生截断或四舍五入 decimal 整数最大位数 m 为 65。支持小数最大位数 d 是 30。 d如果被省略默认为0。 m如果被省略默认为是 10。很大程度保证精度准确补充说明float是浮点数占用 4 字节约 7 位有效数字。存储的是近似值运算后可能产生微小误差如 0.1 0.2 ≠ 0.3。decimal是定点数存储的是精确的十进制数值适合金额、体重、身高等需要精确计算的场景。decimal(10,2)表示总共 10 位数字其中小数点后 2 位小数点前 8 位。在 MySQL 中如果插入的小数位数超过 d会进行四舍五入截断严格模式下会报错。性能上 float/double 比 decimal 快但 decimal 更精确。金融类项目强制使用 decimal。1-3字符串类型char语法char(L): 固定长度字符串L是可以存储的长度单位为字符最大长度值可以为255说明char(2) 表示可以存放两个字符可以是字母或汉字但是不能超过2个 最多只能是255字符编码与 MySQL 中的 char 类型 在 UTF-8 中一个汉字一般占 3 个字节在 GBK 中一个汉字一般占 2 个字节。 注意char 类型的单位为字符MySQL 中的字符和 C/C 中的概念相同。C/C 中一个 char 占 1 字节而 MySQL 中一个字符代表一个符号其实际占用的字节数取决于字符集。 MySQL 5.7 与 8.0 的区别 在 MySQL 5.7 及更早版本中 默认字符集是 latin1 使用 utf8 时实际是 utf8mb3每个字符最多 3 字节 无法存储 emoji 和部分生僻汉字 在 MySQL 8.0 中 默认字符集改为 utf8mb4 utf8mb4 每个字符最多 4 字节支持完整 Unicode包括 emoji 官方推荐新应用使用 utf8mb4 重要提醒 utf8 在 8.0 中仍是 utf8mb3 的别名但已被标记为废弃 未来版本中 utf8 将变为 utf8mb4 的别名 为避免歧义创建表时建议显式指定 CHARACTER SET utf8mb4 总结 char(10) 表示能存 10 个字符能存多少汉字取决于字符集 utf8mb3最多 10 个汉字占 30 字节 utf8mb4最多 10 个汉字占 40 字节 gbk最多 10 个汉字占 20 字节varchar语法varchar(L): 可变长度字符串L表示字符长度最大长度65535个字插入数据进行越界测试1. 为什么 VARCHAR(65536) / VARCHAR(21846) 会报错报错 Column length too big 核心原因是MySQL 的 InnoDB 表有一个单行最大数据长度限制约 65535 字节注意是字节不是字符。VARCHAR(65536) 直接超出了这个限制所以直接报错。VARCHAR(21846) 在 UTF-8 编码下每个字符占 3 字节 21846 * 3 65538 超过了 65535 字节的上限所以也报错。2. 为什么 VARCHAR(16383) 能成功VARCHAR(16383) 在 UTF-8 编码下 16383 * 3 49149 字节远小于 65535 字节的行限制所以可以正常创建没有任何警告。3. 为什么 VARCHAR(21845) 会有 1 warning 21845 * 3 65535 刚好等于行长度上限。能创建成功但 MySQL 会给你一个警告你把行空间全用完了没法再加其他字段了。于varchar(len),len到底是多大这个len值和表的编码密切相关varchar长度可以指定为0到65535之间的值但是有1 - 3 个字节用于记录数据大小所以说有效字节数是65532。当我们的表的编码是utf8时varchar(n)的参数n最大值是65532/321844[因为utf中一个字符占用3个字节]如果编码是gbkvarchar(n)的参数n最大是65532/232766因为gbk中一个字符占用2字节。varchar 的最大长度限制是 65535 字节不是 65535 个字符。在 utf8mb3 下每个汉字占 3 字节所以最多 21845 个字符。21846 × 3 65538 超出限制所以创建失败。char和varchar比较如何选择定长或变长字符串如果数据确定长度都一样就使用定长char比如身份证手机号md5如果数据长度有变化,就使用变长(varchar), 比如名字地址但是你要保证最长的能存的进去。定长的磁盘空间比较浪费但是效率高。变长的磁盘空间比较节省但是效率低。定长的意义是直接开辟好对应的空间变长的意义是在不超过自定义范围的情况下用多少开辟多少。1-4日期和时间类型用的日期有如下三个date :日期 yyyy-mm-dd 占用三字节datetime 时间日期格式 yyyy-mm-dd HH:ii:ss 表示范围从 1000 到 9999 占用八字节timestamp 时间戳从1970年开始的 yyyy-mm-dd HH:ii:ss 格式和 datetime 完全一致占用四字节1.date格式yyyy-mm-dd占用 3 字节。取值范围1000-01-01到9999-12-31只存储日期不存储时间适合存储生日、入职日期、节日等2.datetime格式yyyy-mm-dd HH:ii:ss范围 1000~9999占用 8 字节。取值范围1000-01-01 00:00:00到9999-12-31 23:59:59存储日期和时间与时区无关你存什么就是什么适合存储创建时间、活动开始时间等不需要考虑时区的场景MySQL 8.0 中支持小数秒微秒精度3. timestamp会自动更新时间戳格式和datetime一样占用 4 字节从 1970 年开始。详细补充取值范围1970-01-01 00:00:01UTC 到2038-01-19 03:14:07UTC2038 年问题因为 TIMESTAMP 底层是 4 字节有符号整数最大只能表示 2038 年存储的是从 1970-01-01 00:00:00 UTC 开始的秒数自动更新特性配合ON UPDATE CURRENT_TIMESTAMP每次 UPDATE 时自动刷新时区敏感存储时转为 UTC查询时转为当前时区适合存储最后登录时间、记录更新时间等场景你会发现什么他不会自动刷新时间MySQL 5.7 默认让第一个 TIMESTAMP 列自动获得自动刷新能力而 8.0 关闭了这个行为必须显式指定ON UPDATE CURRENT_TIMESTAMP才能实现解决方法ALTER TABLE t11 ADD COLUMN update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 最后更新时间;解决刷新问题后1-5enum 和 set语法enumenum枚举“单选”类型enum(选项1,选项2,选项3,...);该设定只是提供了若干个选项的值最终一个单元格中实际只存储了其中一个值而且出于效率考虑这些值实际存储的是“数字”因为这些选项的每个选项值依次对应如下数字1,2,3,....最多65535个当我们添加枚举值时也可以添加对应的数字编号。setset集合“多选”类型set(选项值1,选项值2,选项值3, ...);该设定只是提供了若干个选项的值最终一个单元格中设计可存储了其中任意多个值而且出于效率考虑这些值实际存储的是“数字”因为这些选项的每个选项值依次对应如下数字1,2,4,8,16,32....最多64个。说明不建议在添加枚举值集合值的时候采用数字的方式因为不利于阅读。MySQL 中 ENUM 类型的底层存储机制ENUM 实际存储的是枚举值对应的数字下标从 1 开始而不是字符串本身。因此当向 gender 列定义为 ENUM(男,女)插入1时MySQL 会将其解释为下标 1从而存入男插入2得到女插入3或-1则因为下标越界而报错而0或-0会被当作无效下标在宽松模式下转为空字符串。这就是为什么同样的数字字符串插入结果不同核心在于 ENUM 的值是以索引位置来判断的。插入语句hobby 值插入的数字对应二进制底层数值位掩码选中的爱好选项SELECT *时的显示结果hobby0强制转数字结果insert ... 110011代码代码1insert ... 220102羽毛球羽毛球2insert ... 330113代码、羽毛球代码,羽毛球3insert ... 441004足球足球4insert ... 771117代码、羽毛球、足球代码,羽毛球,足球7层原理SET类型的每个选项会按定义顺序自动分配一个 2 的幂次数值代码 12⁰羽毛球 22¹足球 42²游泳 82³为什么看不到数字SELECT *时MySQL 会自动把位掩码解析成对应的选项字符串只有用0强制转数字才能看到原始的数值。字段类型查询语句的含义底层逻辑匹配结果genderENUM(男,女)gender男字符串值匹配匹配 ENUM 定义的文本值所有性别为 “男” 的记录12 条genderENUM(男,女)gender1底层索引值匹配匹配 ENUM 内部的数字索引男对应索引 1和gender男结果完全相同12 条genderENUM(男,女)gender女字符串值匹配匹配 ENUM 定义的文本值所有性别为 “女” 的记录2 条genderENUM(男,女)gender2底层索引值匹配匹配 ENUM 内部的数字索引女对应索引 2和gender女结果完全相同2 条hobbySET(代码,羽毛球,足球,游泳)hobby羽毛球字符串组合精确匹配匹配完整的选项字符串仅匹配 “只选了羽毛球” 的记录2 条hobbySET(代码,羽毛球,足球,游泳)hobby3底层位掩码精确匹配匹配 SET 内部的数值代码羽毛球 123和hobby代码,羽毛球结果完全相同1 条hobbySET(代码,羽毛球,足球,游泳)hobby7底层位掩码精确匹配匹配 SET 内部的数值代码羽毛球足球 1247和hobby代码,羽毛球,足球结果完全相同1 条号在ENUM和SET字段上既可以匹配显示给用户的字符串值也可以匹配MySQL 内部存储的数字值两种写法效果等价都是精确匹配。1-6集合查询使用find_ in_ set函数find_in_set(sub,str_list) 如果 sub 在 str_list 中则返回下标如果不在返回0str_list 用逗号分隔的字符串列表必须用英文逗号,分隔不能用空格、顿号等其他符号列表中的每个元素是独立的只会匹配完整元素不会做部分匹配常用于WHERE条件中实现多值匹配场景比如标签、爱好、权限等逗号分隔字段的查询。MYSQL 语句执行结 果解析说明select find_in_set(a, a,b,c);1a是列表中第 1 个元素返回位置 1select find_in_set(a,b, a,b,c);0a,b不是列表中的独立元素列表里是a/b/c三个元素返回 0select find_in_set(d, a,b,c);0d不在列表中返回 0select find_in_set(b, a,b,c);2b是列表中第 2 个元素返回位置 2select find_in_set(c, a,b,c);3c是列表中第 3 个元素返回位置 3案例 1查询所有爱好包含羽毛球的用户MYSQL 语句SELECT * FROM votes WHERE FIND_IN_SET(羽毛球, hobby);案例 2查询同时包含代码和羽毛球的用户MYSQL 语句SELECT * FROM votes WHERE FIND_IN_SET(代码, hobby) AND FIND_IN_SET(羽毛球, hobby)FIND_IN_SET()vsLIKE为什么不用LIKE很多人会用LIKE %羽毛球%来实现类似需求但它有明显的缺陷对比一下方式MYSQL 语句匹配结果问题FIND_IN_SETWHERE FIND_IN_SET(羽毛球, hobby)仅匹配包含独立「羽毛球」的记录精准匹配无副作用LIKEWHERE hobby LIKE %羽毛球%会匹配「羽毛球」「羽毛球拍」「打羽毛球」等包含子串的记录可能误匹配比如爱好是「羽毛球拍」的用户也会被查出来所以当字段是逗号分隔的多值存储时FIND_IN_SET是更合适的选择能保证匹配的精准性。
【MYSQL】 数据库的常见数据类型--详解
发布时间：2026/5/22 6:23:35
一.数据类型1-1数据类型分类1-2数值类型整型可以指定是有符号的和无符号的默认是有符号的。tinyint类型(有符号在 MySQL 表中建立属性列时我们可以发现列名称在前类型在后。插入数据进行越界测试会报错在MySQL中整型可以指定是有符号的和无符号的默认是有符号的。可以通过unsigned来说明某个字段是无符号的unsigned(无符号)插入数据进行越界测试会报错char a 123456; C/C 中编译器不会报错最多也就是警告这里会发生截断甚至发生隐式转换。 MySQL 中在特定的类型中插入不合规的数据一般会发生拦截不让我们做对应的操作。约束 反过来如果已经有数据被成功插入到 MySQL 中那么说明插入时一定是合法的。 所以在 MySQL 中一般而言数据类型本身也是一种约束 目的是能够保证数据库中的数据是可预期、完整的也就类比做约束下一篇文章我会讲补充C/C 的 char 类型通常只占 1 字节取值范围是 -128 到 127 或 0 到 255。赋值 123456 远超这个范围编译器只会警告实际运行时只保留低 8 位造成数据丢失。MySQL 的数据类型约束更严格插入非法值如将 abc 插入 INT 列会直接报错拒绝执行。这种设计让 MySQL 的数据质量更有保障应用程序写错了也能被数据库拦截避免脏数据入库注意尽量不使用unsigned对于int类型可能存放不下的数据int unsigned同样可能存放不下与其如此还不如设计时将int类型提升为bigint类型。bit类型基本语法bit[(M)] : 位字段类型。M表示每个值的位数范围从1到64。如果M被忽略默认为1。插入数据越界测试1.0x01 / 0x00 是什么这是MySQL命令行对二进制/位类型数据的默认显示方式 0x 开头表示后面的内容是十六进制数 0x01 十进制的 1 0x00 十进制的 02. hex(online) 函数的作用hex() 是MySQL提供的函数它会把字段里的内容转换成十六进制字符串输出当 online 存的是 1 hex(online) 就返回字符串 1 本质上是十六进制表示的结果当 online 存的是 0 hex(online) 就返回字符串 0online 字段的类型是单字节类型只能存储 0 和 1插入大于 1 的值比如 2会触发 Data too long 报错。修改online的bit位当 online 是 BIT 二进制位类型时select * 直接查询原始二进制数据 二进制字符串显示为0x... 0x0061hex(online) 把二进制数据转成十六进制文本 字符串十六进制表示 61online0 把十六进制数据隐式转为十进制整数 数字十进制 97mysql8.0mysql5.7结果可以看到这说明online 字段的类型是字符/字符串类型如 CHAR 、 VARCHAR 或 TINYTEXT 当你插入数字 97 时MySQL会把它当作ASCII码值处理自动转换为对应的字符 a 因为 a 的ASCII码就是97。而直接插入 a 时MySQL直接存储字符本身所以两种插入方式的结果是一样的。bit 字段在显示时是按照ASCII码对应的值显示这个旧版本存在的。如果我们有这样的值只存放 0/1这时可以定义 bit(1)可以节省空间BIT(1) 只占 1 位存 0 或 1BIT(3) 占 3 位可以存 000、001、010、011、100、101、110、111 这 8 种状态这是因为字段在创建时定义的 数据类型 不一样MySQL会根据你定义的类型来决定怎么存储和处理数据。1. 当 online 是 VARCHAR / CHAR 字符串类型时存储把输入的内容当作文本字符直接存起来比如输入 a 就存字符 a 输入 97 也会被转成字符串 97 存储特殊情况如果输入数字 97 MySQL会自动把它转成ASCII码对应的字符 a 存储。显示 SELECT * 时直接显示文本内容比如 a 、 97 。处理 HEX(online) 会把字符的ASCII码转成十六进制比如 a 的ASCII码是97十六进制就是 61 。2. 当 online 是 BIT 二进制位类型时存储把输入的内容当作二进制数值存起来比如输入 1 就存二进制 0000000000000001 输入 97 就存二进制 0000000001100001 。显示 SELECT * 时会以 0x 开头的十六进制形式显示比如 0x0001 、 0x0061 这是MySQL对二进制数据的默认显示方式。处理 HEX(online) 会直接把二进制数值转成十六进制字符串比如 1 、 61 online0 会把二进制数值转成十进制整数比如 1 、 97 。建表时写 online VARCHAR(10) → 就是字符串类型建表时写 online BIT(16) → 就是二进制位类型这是MySQL 8.0 客户端对 BIT 类型的默认显示方式直接显示为0x开头的十六进制字面量而不是像旧版本那样把二进制字节当作 ASCII 字符显示出来比如0x0061显示成a。旧版本MySQL 5.x BIT 字段查询时会把存储的二进制字节直接当作字符串输出客户端就会按 ASCII 码解析成字符。 比如0x61会直接显示成a0x0A会显示成换行符0x00显示成空白这就是你听到的 “按 ASCII 显示” 的来源。MySQL 8.0 以后 客户端默认行为改了BIT 类型不再直接解析为 ASCII 字符而是直接以0x十六进制的形式展示原始字节。 你截图里的0x0001、0x0061就是这个新行为的直接体现。小数类型float基本语法float[(m, d)] [unsigned] : M指定显示长度d指定小数位数占用空间4个字节【补充】MySQL 5.7 及之前 INT(1) 、 INT(3) 、 INT(11) 这种写法是有“显示宽度”的概念的配合 ZEROFILL 可以控制补零显示比如 INT(3) ZEROFILL 存 5 会显示成 005 。MySQL 8.0 以后这个数字就只剩个“占位”作用了完全不影响存储和显示只是个语法兼容的摆设甚至很多地方默认都不推荐写了。很多人会误以为 INT(1) 只能存 0-9 INT(3) 只能存 0-999这是个经典误区它从来都不限制存储的数值范围只影响“显示时的最小宽度”。INT 本身固定是 4 字节能存 -2,147,483,648 到 2,147,483,647 和括号里的数字一点关系都没有。8.0 以后的变化显示宽度功能被废弃了 ZEROFILL 也不推荐用了。现在写 INT 就行写 INT(1) 、 INT(11) 完全没区别MySQL 会忽略括号里的数字插入数据越界测试\G后面的分号可以省略小数float(4,2)表示的范围是-99.99 ~ 99.99MySQL在保存值时会进行四舍五入。注意在合法范围内允许五入但在边界值五入会导致整体的浮点数增多从而超过浮点数对应的范围所以不是所有情况都可以五入。unsigned(无符号会有精度损失decimal语法decimal(m, d) [unsigned] : 定点数m指定长度d表示小数点的位数decimal(5,2) 表示的范围是 -999.99 ~ 999.99decimal(5,2) unsigned 表示的范围 0 ~ 999.99decimal和float很像但是有区别:float和decimal表示的精度不一样插入数据越界测试decimal 和 float 的区别 float 和 decimal 表示的精度不一样。 float 表示的精度大约是 7 位。会发生截断或四舍五入 decimal 整数最大位数 m 为 65。支持小数最大位数 d 是 30。 d如果被省略默认为0。 m如果被省略默认为是 10。很大程度保证精度准确补充说明float是浮点数占用 4 字节约 7 位有效数字。存储的是近似值运算后可能产生微小误差如 0.1 0.2 ≠ 0.3。decimal是定点数存储的是精确的十进制数值适合金额、体重、身高等需要精确计算的场景。decimal(10,2)表示总共 10 位数字其中小数点后 2 位小数点前 8 位。在 MySQL 中如果插入的小数位数超过 d会进行四舍五入截断严格模式下会报错。性能上 float/double 比 decimal 快但 decimal 更精确。金融类项目强制使用 decimal。1-3字符串类型char语法char(L): 固定长度字符串L是可以存储的长度单位为字符最大长度值可以为255说明char(2) 表示可以存放两个字符可以是字母或汉字但是不能超过2个 最多只能是255字符编码与 MySQL 中的 char 类型 在 UTF-8 中一个汉字一般占 3 个字节在 GBK 中一个汉字一般占 2 个字节。 注意char 类型的单位为字符MySQL 中的字符和 C/C 中的概念相同。C/C 中一个 char 占 1 字节而 MySQL 中一个字符代表一个符号其实际占用的字节数取决于字符集。 MySQL 5.7 与 8.0 的区别 在 MySQL 5.7 及更早版本中 默认字符集是 latin1 使用 utf8 时实际是 utf8mb3每个字符最多 3 字节 无法存储 emoji 和部分生僻汉字 在 MySQL 8.0 中 默认字符集改为 utf8mb4 utf8mb4 每个字符最多 4 字节支持完整 Unicode包括 emoji 官方推荐新应用使用 utf8mb4 重要提醒 utf8 在 8.0 中仍是 utf8mb3 的别名但已被标记为废弃 未来版本中 utf8 将变为 utf8mb4 的别名 为避免歧义创建表时建议显式指定 CHARACTER SET utf8mb4 总结 char(10) 表示能存 10 个字符能存多少汉字取决于字符集 utf8mb3最多 10 个汉字占 30 字节 utf8mb4最多 10 个汉字占 40 字节 gbk最多 10 个汉字占 20 字节varchar语法varchar(L): 可变长度字符串L表示字符长度最大长度65535个字插入数据进行越界测试1. 为什么 VARCHAR(65536) / VARCHAR(21846) 会报错报错 Column length too big 核心原因是MySQL 的 InnoDB 表有一个单行最大数据长度限制约 65535 字节注意是字节不是字符。VARCHAR(65536) 直接超出了这个限制所以直接报错。VARCHAR(21846) 在 UTF-8 编码下每个字符占 3 字节 21846 * 3 65538 超过了 65535 字节的上限所以也报错。2. 为什么 VARCHAR(16383) 能成功VARCHAR(16383) 在 UTF-8 编码下 16383 * 3 49149 字节远小于 65535 字节的行限制所以可以正常创建没有任何警告。3. 为什么 VARCHAR(21845) 会有 1 warning 21845 * 3 65535 刚好等于行长度上限。能创建成功但 MySQL 会给你一个警告你把行空间全用完了没法再加其他字段了。于varchar(len),len到底是多大这个len值和表的编码密切相关varchar长度可以指定为0到65535之间的值但是有1 - 3 个字节用于记录数据大小所以说有效字节数是65532。当我们的表的编码是utf8时varchar(n)的参数n最大值是65532/321844[因为utf中一个字符占用3个字节]如果编码是gbkvarchar(n)的参数n最大是65532/232766因为gbk中一个字符占用2字节。varchar 的最大长度限制是 65535 字节不是 65535 个字符。在 utf8mb3 下每个汉字占 3 字节所以最多 21845 个字符。21846 × 3 65538 超出限制所以创建失败。char和varchar比较如何选择定长或变长字符串如果数据确定长度都一样就使用定长char比如身份证手机号md5如果数据长度有变化,就使用变长(varchar), 比如名字地址但是你要保证最长的能存的进去。定长的磁盘空间比较浪费但是效率高。变长的磁盘空间比较节省但是效率低。定长的意义是直接开辟好对应的空间变长的意义是在不超过自定义范围的情况下用多少开辟多少。1-4日期和时间类型用的日期有如下三个date :日期 yyyy-mm-dd 占用三字节datetime 时间日期格式 yyyy-mm-dd HH:ii:ss 表示范围从 1000 到 9999 占用八字节timestamp 时间戳从1970年开始的 yyyy-mm-dd HH:ii:ss 格式和 datetime 完全一致占用四字节1.date格式yyyy-mm-dd占用 3 字节。取值范围1000-01-01到9999-12-31只存储日期不存储时间适合存储生日、入职日期、节日等2.datetime格式yyyy-mm-dd HH:ii:ss范围 1000~9999占用 8 字节。取值范围1000-01-01 00:00:00到9999-12-31 23:59:59存储日期和时间与时区无关你存什么就是什么适合存储创建时间、活动开始时间等不需要考虑时区的场景MySQL 8.0 中支持小数秒微秒精度3. timestamp会自动更新时间戳格式和datetime一样占用 4 字节从 1970 年开始。详细补充取值范围1970-01-01 00:00:01UTC 到2038-01-19 03:14:07UTC2038 年问题因为 TIMESTAMP 底层是 4 字节有符号整数最大只能表示 2038 年存储的是从 1970-01-01 00:00:00 UTC 开始的秒数自动更新特性配合ON UPDATE CURRENT_TIMESTAMP每次 UPDATE 时自动刷新时区敏感存储时转为 UTC查询时转为当前时区适合存储最后登录时间、记录更新时间等场景你会发现什么他不会自动刷新时间MySQL 5.7 默认让第一个 TIMESTAMP 列自动获得自动刷新能力而 8.0 关闭了这个行为必须显式指定ON UPDATE CURRENT_TIMESTAMP才能实现解决方法ALTER TABLE t11 ADD COLUMN update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 最后更新时间;解决刷新问题后1-5enum 和 set语法enumenum枚举“单选”类型enum(选项1,选项2,选项3,...);该设定只是提供了若干个选项的值最终一个单元格中实际只存储了其中一个值而且出于效率考虑这些值实际存储的是“数字”因为这些选项的每个选项值依次对应如下数字1,2,3,....最多65535个当我们添加枚举值时也可以添加对应的数字编号。setset集合“多选”类型set(选项值1,选项值2,选项值3, ...);该设定只是提供了若干个选项的值最终一个单元格中设计可存储了其中任意多个值而且出于效率考虑这些值实际存储的是“数字”因为这些选项的每个选项值依次对应如下数字1,2,4,8,16,32....最多64个。说明不建议在添加枚举值集合值的时候采用数字的方式因为不利于阅读。MySQL 中 ENUM 类型的底层存储机制ENUM 实际存储的是枚举值对应的数字下标从 1 开始而不是字符串本身。因此当向 gender 列定义为 ENUM(男,女)插入1时MySQL 会将其解释为下标 1从而存入男插入2得到女插入3或-1则因为下标越界而报错而0或-0会被当作无效下标在宽松模式下转为空字符串。这就是为什么同样的数字字符串插入结果不同核心在于 ENUM 的值是以索引位置来判断的。插入语句hobby 值插入的数字对应二进制底层数值位掩码选中的爱好选项SELECT *时的显示结果hobby0强制转数字结果insert ... 110011代码代码1insert ... 220102羽毛球羽毛球2insert ... 330113代码、羽毛球代码,羽毛球3insert ... 441004足球足球4insert ... 771117代码、羽毛球、足球代码,羽毛球,足球7层原理SET类型的每个选项会按定义顺序自动分配一个 2 的幂次数值代码 12⁰羽毛球 22¹足球 42²游泳 82³为什么看不到数字SELECT *时MySQL 会自动把位掩码解析成对应的选项字符串只有用0强制转数字才能看到原始的数值。字段类型查询语句的含义底层逻辑匹配结果genderENUM(男,女)gender男字符串值匹配匹配 ENUM 定义的文本值所有性别为 “男” 的记录12 条genderENUM(男,女)gender1底层索引值匹配匹配 ENUM 内部的数字索引男对应索引 1和gender男结果完全相同12 条genderENUM(男,女)gender女字符串值匹配匹配 ENUM 定义的文本值所有性别为 “女” 的记录2 条genderENUM(男,女)gender2底层索引值匹配匹配 ENUM 内部的数字索引女对应索引 2和gender女结果完全相同2 条hobbySET(代码,羽毛球,足球,游泳)hobby羽毛球字符串组合精确匹配匹配完整的选项字符串仅匹配 “只选了羽毛球” 的记录2 条hobbySET(代码,羽毛球,足球,游泳)hobby3底层位掩码精确匹配匹配 SET 内部的数值代码羽毛球 123和hobby代码,羽毛球结果完全相同1 条hobbySET(代码,羽毛球,足球,游泳)hobby7底层位掩码精确匹配匹配 SET 内部的数值代码羽毛球足球 1247和hobby代码,羽毛球,足球结果完全相同1 条号在ENUM和SET字段上既可以匹配显示给用户的字符串值也可以匹配MySQL 内部存储的数字值两种写法效果等价都是精确匹配。1-6集合查询使用find_ in_ set函数find_in_set(sub,str_list) 如果 sub 在 str_list 中则返回下标如果不在返回0str_list 用逗号分隔的字符串列表必须用英文逗号,分隔不能用空格、顿号等其他符号列表中的每个元素是独立的只会匹配完整元素不会做部分匹配常用于WHERE条件中实现多值匹配场景比如标签、爱好、权限等逗号分隔字段的查询。MYSQL 语句执行结 果解析说明select find_in_set(a, a,b,c);1a是列表中第 1 个元素返回位置 1select find_in_set(a,b, a,b,c);0a,b不是列表中的独立元素列表里是a/b/c三个元素返回 0select find_in_set(d, a,b,c);0d不在列表中返回 0select find_in_set(b, a,b,c);2b是列表中第 2 个元素返回位置 2select find_in_set(c, a,b,c);3c是列表中第 3 个元素返回位置 3案例 1查询所有爱好包含羽毛球的用户MYSQL 语句SELECT * FROM votes WHERE FIND_IN_SET(羽毛球, hobby);案例 2查询同时包含代码和羽毛球的用户MYSQL 语句SELECT * FROM votes WHERE FIND_IN_SET(代码, hobby) AND FIND_IN_SET(羽毛球, hobby)FIND_IN_SET()vsLIKE为什么不用LIKE很多人会用LIKE %羽毛球%来实现类似需求但它有明显的缺陷对比一下方式MYSQL 语句匹配结果问题FIND_IN_SETWHERE FIND_IN_SET(羽毛球, hobby)仅匹配包含独立「羽毛球」的记录精准匹配无副作用LIKEWHERE hobby LIKE %羽毛球%会匹配「羽毛球」「羽毛球拍」「打羽毛球」等包含子串的记录可能误匹配比如爱好是「羽毛球拍」的用户也会被查出来所以当字段是逗号分隔的多值存储时FIND_IN_SET是更合适的选择能保证匹配的精准性。
