MySQL的字段数量以及长度限制

这篇具有很好参考价值的文章主要介绍了MySQL的字段数量以及长度限制。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

一、InnoDB行格式

行格式 紧凑的存储特性 增强的可变长度列存储 大型索引键前缀支持 压缩支持 支持的表空间类型
REDUNDANT N N N N system, file-per-table, general
COMPACT Y N N N system, file-per-table, general
DYNAMIC Y Y N N system, file-per-table, general
COMPRESSED Y Y Y Y file-per-table, general

开发中常用的是DYNAMIC行格式,这里着重对它进行介绍,其他格式说明请查阅官网。

1.1 COMPACT存储特性

行DYNAMIC格式提供与行格式相同的存储特性COMPACT,但增加了对长可变长度列的增强存储功能,并支持大索引键前缀

  • 每个索引记录包含一个 5 字节的标头,并且可能会有一部分的可变长度,用于将连续的记录链接在一起,并用于行级锁定;
  • 记录头的可变长度部分包含一个用于指向NULL列的位向量。若索引中可以为 NULL的列数为N,则位向量占用N/8个字节数。(例如,如果有 9 到 16 列可以是,则位向量使用两个字节。)除了此向量中的位之外,别的为NULL的列并不占用空间。标头的可变长度部分还包含可变长度列的长度。每个长度占用一个或两个字节,具体取决于列的最大长度。如果索引中的所有列都是NOT NULL并且具有固定长度,那么记录头就没有可变长度部分;
  • 对于每个非NULL可变长度字段,记录头包含一或两个字节的列长度。仅当部分列存储在溢出页外部或最大长度超过 255 字节且实际长度超过 127 字节时,才需要两个字节。对于外部存储的列,2字节长度表示内部存储部分的长度加上指向外部存储部分的20字节指针。内部部分是768字节,所以长度是768+20。20 字节的指针存储列的真实长度;
  • 记录头之后是非NULL列的数据内容;
  • 聚集索引中的记录包含所有用户定义列的字段。此外,还有一个 6 字节的事务 ID 字段和一个 7 字节的滚动指针字段;
  • 如果没有为表定义主键,则每个聚簇索引记录还包含一个 6 字节的行 ID 字段;
  • 每个二级索引记录包含了由聚集索引键定义的所有主键列,这些主键列不在二级索引中。如果任何主键列是可变长度的,则每个二级索引的记录头都有一个可变长度部分来记录它们的长度,即使二级索引是在固定长度列上定义的;
  • 在内部,对于非可变长度字符集,固定长度字符列如CHAR(10),是以固定长度格式存储的。并且不会从 VARCHAR类型的列中截断尾随空格;
  • 在内部,对于诸如utf8mb3和utf8mb4之类的可变长度字符集 , InnoDB尝试通过修剪尾随空格来将CHAR(N)存储为N字节。 如果CHAR(N)列的字节长度超过N字节,则将尾随空格修剪为该列字节长度的最小值。CHAR(N)列的最大长度是 最大字符字节长度 × N;
  • 至少为 CHAR(N) 保留 N 个字节。 在许多情况下,保留最小空间 N 可以使列就地更新,不会导致索引页碎片。 相比之下,当使用 REDUNDANT 行格式时,CHAR(N) 列占用的长度是 最大字符字节长度 × N;
  • 大于或等于 768 字节的固定长度列被编码为可变长度字段,可以跨页存储。 例如,如果字符集的最大字节长度大于 3,则 CHAR(255) 列可以超过 768 个字节,如utf8mb4;

1.2 DYNAMIC存储特性

  • DYNAMIC 行格式提供与 COMPACT 行格式相同的存储特性,但增强了可变长度的存储功能,并支持大索引键前缀;
  • 当使用 ROW_FORMAT=DYNAMIC 创建表时,InnoDB 可以存储长的可变长度列值( VARCHAR、VARBINARY、BLOB 、 TEXT )完全离页,聚簇索引记录仅包含一个 20 字节的指针指向溢出页。 大于或等于 768 字节的固定长度字段被编码为可变长度字段;
  • 列是否存储在页外取决于页大小和行的总大小。 当一行太长时,选择最长的列进行页外存储,直到聚集索引记录适合 B 树页。 小于或等于 40 字节的 TEXT 和 BLOB 列按行存储;
  • DYNAMIC 行格式保持了将整行存储在索引节点中的效率(如果适合)(COMPACT 和 REDUNDANT 格式也是如此),但是 DYNAMIC 行格式避免了用大量数据字节填充 B 树节点的问题 的长列。 DYNAMIC 行格式基于这样的想法,即如果长数据值的一部分存储在页外,则通常将整个值存储在页外是最有效的。 使用 DYNAMIC 格式,较短的列可能会保留在 B 树节点中,从而最大限度地减少给定行所需的溢出页数;
  • DYNAMIC 行格式支持最多 3072 字节的索引键前缀;
  • 使用 DYNAMIC 行格式的表可以存储在system tablespace、file-per-table tablespace和general tablespace中。 要在system tablespace中存储动态表,请禁用 innodb_file_per_table 并使用常规 CREATE TABLE 或 ALTER TABLE 语句,也可以搭配 TABLESPACE [=] innodb_system 。 innodb_file_per_table 变量不适用于一般表空间,在使用 TABLESPACE [=] innodb_system 表选项在system tablespace中存储 DYNAMIC 表时也不适用;

二、数据类型存储要求

https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html

2.1 数据类型存储要求

MySQL的字段数量以及长度限制
DECIMAL(和 NUMERIC)列的值使用二进制格式表示,该格式将九个十进制(基数 10)数字打包成四个字节。 每个值的整数和小数部分的分开存储。 九个十进制数字的每个倍数需要四个字节,“剩余”数字需要四个字节的一部分。 下表给出了多余数字所需的存储空间:
MySQL的字段数量以及长度限制

2.2 日期时间类型存储要求

MySQL5.6.4 之后创建的表允许TIME、DATETIME 和 TIMESTAMP这些类型具有小数部分,这额外需要 0 到 3 个字节,具体取决于存储值的小数秒精度:
MySQL的字段数量以及长度限制

2.3 String类型存储要求

M:非二进制字符串类型的声明列长度(以字符为单位)和二进制字符串类型的字节数;
N:给定字符串值的实际长度(以字节为单位);
MySQL的字段数量以及长度限制

  1. 可变长度字符串类型使用一个带长度的前缀+数据存储。 前缀的长度根据数据类型以及前缀的值 L(字符串的字节长度)来确定需要多少字节(1-4),。 例如,MEDIUMTEXT 值的存储需要 L 个字节,加上三个字节来存储该值的长度;
  2. 要计算用于存储特定 CHAR、VARCHAR 或 TEXT 列的字节数,必须考虑该列的字符集以及是否包含多字节字符。 特别是,在使用 UTF-8 Unicode 字符集时,必须记住并非所有字符都使用相同的字节数。 utf8mb3 和 utf8mb4 字符集可能分别要求每个字符需要三个和四个字节;
  3. VARCHAR、VARBINARY 、 BLOB、 TEXT是可变长度类型,存储要求取决于以下因素:
  • 列值的实际长度
  • 列的最大可能长度
  • 列使用的字符集,因为有些字符集包含多字节字符
  1. 例如,VARCHAR(255) 列可以容纳最大长度为 255 个字符的字符串。 假设该列使用latin1字符集(每个字符一个字节),实际需要存储的是字符串的长度(L),加上一个字节来记录字符串的长度。 对于字符串 'abcd',L 为 4,存储要求为 5 个字节。 如果同一列改为声明使用 ucs2 双字节字符集,则存储要求为 10 个字节:'abcd' 的长度为八个字节,该列需要两个字节来存储长度,因为最大长度大于 255 (最多 510 个字节);
  2. 可以存储在 VARCHAR 或 VARBINARY 列中的最大有效字节数受行最大为 65535 字节的限制,该行大小在所有列之间共享。 对于存储多字节字符的 VARCHAR 列,有效的最大字符数更少。 例如,utf8mb4 字符每个字符最多可以使用四个字节,因此使用 utf8mb4 字符集的 VARCHAR 列最多可声明为 16,383 个字符;
  3. ENUM 对象的大小由不同枚举值的数量决定。 一个字节用于具有最多 255 个可能值的枚举。 两个字节用于具有 256 到 65,535 个可能值的枚举;
  4. SET 对象的大小由不同集合成员的数量决定。 如果设置大小为 N,则对象占用 (N+7)/8 个字节,四舍五入为 1、2、3、4 或 8 个字节。 一个 SET 最多可以有 64 个成员;

三、表列数和行大小的限制

https://dev.mysql.com/doc/refman/8.0/en/column-count-limit.html

3.1 列数限制

MySQL 规定每个表只能有 4096 列,但对于给定的表,有效最大值可能会更少。 确切的列限制取决于几个因素:

  • 表的最大行大小限制了列的数量(可能还有列大小),因为所有列的总长度不能超过此大小;
  • 各个列的存储要求限制了行最大固定了的列数。 某些数据类型的存储要求取决于存储引擎、存储格式和字符集等因素;
  • 存储引擎可能会施加额外的限制来限制表的列数。 例如,InnoDB 的每个表有 1017 列的限制;
  • 功能键部分被实现为隐藏的虚拟生成存储列,因此表索引中的每个功能键部分都计入表总列限制

3.2 行大小限制

表的最大行大小由几个因素决定:

  • MySQL 表内部有 65535 字节的最大行大小限制,即使存储引擎能够支持更大的行也是65535。 BLOB 和 TEXT 虽然是大文本,但是由于它们的内容与行的其余部分分开存储,因此它们的列仅占9 到 12 个字节;
  • InnoDB 表的最大行大小适用于本地存储在数据库页面中的数据,在对 4KB、8KB、16KB 和 32KB innodb_page_size 设置中略小于半页。 例如,对于默认的 16KB InnoDB 页面大小,最大行大小略小于 8KB。 对于 64KB 页面,最大行大小略小于 16KB;
  • 如果包含可变长度列的行超过 InnoDB 最大行大小,则 InnoDB 选择外部页外存储的可变长度列,直到该行符合 InnoDB 行大小限制。 对于页外存储的可变长度列,本地存储的数据量因行格式而异;
  • 不同的存储格式使用不同数量的页眉和页尾数据,会影响可用于行的存储量;

3.3 行大小限制案例

1)在以下 InnoDB 和 MyISAM 示例中演示了 65,535 字节的 MySQL 最大行大小限制。 无论存储引擎如何,都会强制执行该限制,即使存储引擎可能能够支持更大的行:

-- InnoDB引擎
mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
                       c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
                       f VARCHAR(10000), g VARCHAR(6000)) ENGINE=InnoDB CHARACTER SET latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used
table type, not counting BLOBs, is 65535. This includes storage overhead,
check the manual. You have to change some columns to TEXT or BLOBs
-- MyISAM 引擎
mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
       c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
       f VARCHAR(10000), g VARCHAR(6000)) ENGINE=MyISAM CHARACTER SET latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used
table type, not counting BLOBs, is 65535. This includes storage overhead,
check the manual. You have to change some columns to TEXT or BLOBs

列更改为 TEXT 可避免 65535 字节的行大小限制,因为 BLOB 和 TEXT 列仅占 9 到 12 字节,并且 InnoDB 变长列的页外存储避免了 InnoDB 行大小限制:

-- MyISAM中TEXT的使用
mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
       c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
       f VARCHAR(10000), g TEXT(6000)) ENGINE=MyISAM CHARACTER SET latin1;
Query OK, 0 rows affected (0.02 sec)
-- InnoDB中TEXT的使用
mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
       c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
       f VARCHAR(10000), g TEXT(6000)) ENGINE=InnoDB CHARACTER SET latin1;
Query OK, 0 rows affected (0.02 sec)

2) 可变长度列的存储包括计入行大小的长度字节。 例如,VARCHAR(255) CHARACTER SET utf8mb3 列需要两个字节来存储值的长度,因此每个值最多可以占用 767 个字节:

latin1+InnaDB:
mysql> CREATE TABLE t1
       (c1 VARCHAR(32765) NOT NULL, c2 VARCHAR(32766) NOT NULL)
       ENGINE = InnoDB CHARACTER SET latin1;
Query OK, 0 rows affected (0.02 sec)

使用的是Latin1,一个字符一个字节,因此列需要 32765+2 + 32766+2 字节 <  65535;
mysql> CREATE TABLE t2
       (c1 VARCHAR(65535) NOT NULL)
       ENGINE = InnoDB CHARACTER SET latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used
table type, not counting BLOBs, is 65535. This includes storage overhead,
check the manual. You have to change some columns to TEXT or BLOBs

65535 + 2 > 65535,因此创建失败;
mysql> CREATE TABLE t2
       (c1 VARCHAR(65533) NOT NULL)
       ENGINE = InnoDB CHARACTER SET latin1;
Query OK, 0 rows affected (0.01 sec)

65533 + 2 = 65535,创建成功

3)对于 MyISAM 表,NULL 列需要在行中额外的空间来记录它们的值是否为 NULL。 每个 NULL 列多占用一位,四舍五入到最接近的字节

mysql> CREATE TABLE t3
       (c1 VARCHAR(32765) NULL, c2 VARCHAR(32766) NULL)
       ENGINE = MyISAM CHARACTER SET latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used
table type, not counting BLOBs, is 65535. This includes storage overhead,
check the manual. You have to change some columns to TEXT or BLOBs

32765 + 2 + 32766 + 2 = 65535,但是对于MyISAM还需要额外的空间来存储null值,因此创建失败

4)对于 4KB、8KB、16KB 和 32KB innodb_page_size 设置,InnoDB 将行大小(对于本地存储在数据库页面中的数据)限制为略小于数据库页面的一半,而对于 64KB 页面限制为略小于 16KB

tip: 对于64KB的一半为什么是16,目前也没在官方文档找到较为详细的解释,只有下面的解释:
MySQL的字段数量以及长度限制

mysql> CREATE TABLE t4 (
       c1 CHAR(255),c2 CHAR(255),c3 CHAR(255),
       c4 CHAR(255),c5 CHAR(255),c6 CHAR(255),
       c7 CHAR(255),c8 CHAR(255),c9 CHAR(255),
       c10 CHAR(255),c11 CHAR(255),c12 CHAR(255),
       c13 CHAR(255),c14 CHAR(255),c15 CHAR(255),
       c16 CHAR(255),c17 CHAR(255),c18 CHAR(255),
       c19 CHAR(255),c20 CHAR(255),c21 CHAR(255),
       c22 CHAR(255),c23 CHAR(255),c24 CHAR(255),
       c25 CHAR(255),c26 CHAR(255),c27 CHAR(255),
       c28 CHAR(255),c29 CHAR(255),c30 CHAR(255),
       c31 CHAR(255),c32 CHAR(255),c33 CHAR(255)
       ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET latin1;
ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB may help.
In current row format, BLOB prefix of 0 bytes is stored inline.

以上例子使用InnoDB的默认页大小16KB,因此最大行大小会限制在8KB以下,即 8 * 1024 = 8192,由于是略小于,从报错信息中也可以发现,真正的行大小是8126
而 33 * 255 = 8415 > 8126,因此创建失败。

结语

这块内容是我在MySQL官网上翻译总结而来,内容比较枯燥,并且实际开发中也并不会注意这么多,因为大多数业务表并不会离谱道到超出字段数量及行大小限制。不过在认真阅读的时候也确实对MySQL有了更深的认识,在以后创建表的时候也会有意识无意识的想到相关的规约,这对于之后的开发或纠错或多或少都会有帮助,等真正出现该类问题时至少不会手足无措。如果你不想看官网的全英文文档,就简单看看这里的介绍,当然,更建议仔细阅读官网,其内容会更加详尽。文章来源地址https://www.toymoban.com/news/detail-709952.html

到了这里,关于MySQL的字段数量以及长度限制的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • MySQL不同数据类型的最大长度与范围限制

    MySQL中不同的数据类型有不同的最大长度限制,主要如下: char(n):最大长度为255个字符 varchar(n):最大长度为65535个字符(受字符编码影响) tinytext:最大长度为255个字符 text:最大长度为65535个字符(5.5.3之前),或21844字符(5.5.3之后,utf8mb4编码) mediumtext:最大长度为16777215个字符 longtext:最大长

    2024年02月16日
    浏览(49)
  • MySQL字段长度详解 附实操分析

    搜了下有关方面的内容发现良莠不齐。大部分人对这方面的内容也比较模糊,先附上MYSQL常用类型图。 首先需要达成共识的是: 1个Byte字节等于8个bit位 。 bit 是最小一级的信息单位,可以表示一个0或1(即二进制); 那么由此我们可以计算,一个字节其实可以表示256种取值。

    2024年02月10日
    浏览(33)
  • mysql 全文索引查询所用关键词最小长度限制

    这个是要分引擎的,InnoDB 引擎默认最小 3 个长度,MyISAM 引擎默认最小 4 个长度。 之前从网上搜索,修改ft_min_word_len配置项,并不起作用,仔细查了一下相关资料,发现原因如下: 对,我的数据库引擎是InnoDB,所以要修改 innodb_ft_min_token_size 这个配置项。 这个配置项,需要在

    2024年02月11日
    浏览(60)
  • MySql中group_concat字符长度限制

    group_concat长度限制默认是1024,有两个解决方法: 1. 彻底修改,找到MYSQL的配置文件my.ini或者my.cnf,在里面添加以下信息 group_concat_max_len = -1  # -1为最大值或填入你要的最大长度 或者自己设置group_concat_max_len = 1024000。 注意:需要重启MySQL才能生效。 2. 在客户端连上mysql,执行语

    2024年04月28日
    浏览(39)
  • 面试官:说一说mysql的varchar字段最大长度?

    在mysql建表sql里,我们经常会有定义 字符串 类型的需求。 比方说user表里的名字,就是个字符串。mysql里有两个 类型 比较适合这个场景。 char和varchar。 声明它们都需要在字段边上加个数组,比如 char(100) 和 varchar(100) ,这个100是指当前字段能放的 最大字符数 。 char和varchar的

    2023年04月16日
    浏览(40)
  • 查看SQL Server的表字段类型、长度、描述以及是否可为null

    本文参考:https://blog.csdn.net/josjiang1/article/details/80558068。 也可以直接点击这里文章链接: sql server查询表结构(字段名,数据类型,长度,描述,是否允许为空,是否为主键)。 小步测试 先查询表的ID 2. 查询表的信息 对于我来说足够了。 组合一下 将上面两句组合一下: 参

    2024年01月22日
    浏览(42)
  • 34亿的mysql表如何优雅的扩字段长度兵并归档重建

    业务背景: 该系统有一张表数据量已达到34亿,并且有个字段长度不够,导致很多数据无法插入。因为业务只要保留近2个月数据即可,所以需要接下来需要做2点:1,扩字段长度 2,只保留近2个月的数据。 于是有了接下来的方案: 1,delete  2个月 之前的数据 ,然后扩字段长

    2024年02月10日
    浏览(29)
  • 【含面试】解锁MySQL group_concat的无限可能性:解决长度限制并实现高效查询

    AI绘画关于SD,MJ,GPT,SDXL百科全书 面试题分享点我直达 2023Python面试题 2023最新面试合集链接 2023大厂面试题PDF 面试题PDF版本 java、python面试题 项目实战:AI文本 OCR识别最佳实践 AI Gamma一键生成PPT工具直达链接 玩转cloud Studio 在线编码神器 玩转 GPU AI绘画、AI讲话、翻译,GPU点亮AI想象

    2024年02月04日
    浏览(72)
  • Vue+elementUI中的el-upload实现上传文件给后端,限制上传文件的格式、文件的大小、文件的数量,将表单和Excel文件一起提交给后端

    需求:1.表单输入信息 2.上传Excel附件 3.下载附件模板 4.限制上传文件的格式、文件的大小、文件的数量 5.将表单和Excel文件一起提交给后端 效果图: 样式部分 data部分代码 method中的代码

    2024年02月12日
    浏览(60)
  • 【MySQL进阶-08】深入理解innodb存储格式,双写机制,buffer pool底层结构和淘汰策略

    MySql系列整体栏目 内容 链接地址 【一】深入理解mysql索引本质 https://blog.csdn.net/zhenghuishengq/article/details/121027025 【二】深入理解mysql索引优化以及explain https://blog.csdn.net/zhenghuishengq/article/details/124552080 【三】深入理解mysql的索引分类,覆盖索引(失效),回表,MRR https://bl

    2024年02月05日
    浏览(50)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包