MySQL库、表、字段的一些限制

最新推荐文章于 2022-11-07 21:45:22 发布

smith789

最新推荐文章于 2022-11-07 21:45:22 发布

阅读量1.7k

点赞数

分类专栏： MySQL 文章标签：数据库 mysql

本文链接：https://blog.csdn.net/smith789/article/details/103788097

版权

MySQL 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

库和表

MySQL本身不限制数据库的数量，但是底层操作系统可能限制文件夹（库以文件夹的形式表示）的数量。
MySQL本身不限制表的数量，但是底层操作系统可能限制文件的数量（表以文件的形式表示）。某些存储引擎比如InnoDB限制最多40亿张表。

表的大小

表的大小，即文件的大小，受限制于操作系统对文件大小的限制，MySQL不限制。

列的数量

1张表最多4096个列，其中有效的存储数据的列会<4096。

行记录的大小

一行最多65535个字节（不区分存储引擎）。其中BLOB和TEXT的只算9~12字节，他们的内容单独存放在其他地方。

varchar

1、MySQL存储引擎在记录varchar数据的时候，需要用最多2个字节表示实际内容的长度，2个字节最多表示的数字即65535，varchar的长度≤65535。
2、另外MySQL中的varchar(n)中的n指的是字符（数字、字母、或者汉字）的长度，varchar(n)最多存储65535个字节长度的字符。结合第1条，如果是gbk编码，最大长度≤ 65535 /2 = 32767,如果是utf8编码，最大长度≤ 65535 /3 = 21845。如果超出上述限制，varchar被自动强行转为text，并产生警告。

char(n)中的每个值都占n个字节

测试(基于MySQL 5.7)

测试1：varchar

CREATE TABLE `a` (
  `c1` varchar(65534) not null
) ENGINE=InnoDB DEFAULT CHARSET=latin1
> 1118 - 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
> 时间: 0.001s

CREATE TABLE `a` (
  `c1` varchar(65533) not null
) ENGINE=InnoDB DEFAULT CHARSET=latin1
> OK
> 时间: 0.037s

CREATE TABLE `a` (
  `c1` varchar(65532) default null
) ENGINE=InnoDB DEFAULT CHARSET=latin1
> OK
> 时间: 0.041s

CREATE TABLE `a` (
  `c1` varchar(65533) default null
) ENGINE=InnoDB DEFAULT CHARSET=latin1
> 1118 - 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
> 时间: 0.001s

说明：对于存储varchar，首先要存储varchar的实际长度（varchar的个数 * 1~2个字节）
对于可以为null的字段，还要存储null标志位。（(varchar的个数+7)/8），上例表中只有1个varchar，只需要1个字节存储null标志位。
对于不能为null的字段，就不需要存储null标志位了。对上面几个例子的解释：
1、如果c1字段不为空，那么最长为65535 - 2 = 65534
2、如果c1字段可以为空，那么最长65535 - 2- 1 = 65535

测试2：行记录大小极限测试

latin1字符集：

CREATE TABLE `a` (
  `c1` varchar(15000) not null,
	`c2` varchar(15000) not null,
	`c3` varchar(15000) not null,
	`c4` varchar(15000) not null,
	`c5` varchar(5535) not null
) ENGINE=InnoDB DEFAULT CHARSET=latin1
> 1118 - 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
> 时间: 0.001s

CREATE TABLE `a` (
  `c1` varchar(15000) not null,
	`c2` varchar(15000) not null,
	`c3` varchar(15000) not null,
	`c4` varchar(15000) not null,
	`c5` varchar(5525) not null
) ENGINE=InnoDB DEFAULT CHARSET=latin1
> OK
> 时间: 0.042s

虽然latin1编码1个字符1个字节，但是如执行结果所示：MySQL还要存储overhead内容，65535不可能全部存储数据内容。
经测试，c5列最长5525，即5个字段最长65525长度。
65535 - 5 * 2 = 65525

CREATE TABLE `a` (
  `c1` varchar(20000) not null,
	`c2` varchar(20000) not null,
	`c3` varchar(20000) not null,
	`c5` varchar(5528) not null
) ENGINE=InnoDB DEFAULT CHARSET=latin1
> 1118 - 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
> 时间: 0.001s

drop table if exists a;
CREATE TABLE `a` (
  `c1` varchar(20000) not null,
	`c2` varchar(20000) not null,
	`c3` varchar(20000) not null,
	`c5` varchar(5526) not null
) ENGINE=InnoDB DEFAULT CHARSET=latin1

65535 - (4 * 2) = 65526


CREATE TABLE `a` (
  `c1` varchar(20000) default null,
	`c2` varchar(20000) default null,
	`c3` varchar(20000) default null,
	`c5` varchar(5526) default null
) ENGINE=InnoDB DEFAULT CHARSET=latin1
> OK
> 时间: 0.038s


 CREATE TABLE `a` (
  `c1` varchar(20000) default null,
	`c2` varchar(20000) default null,
	`c3` varchar(20000) default null,
	`c5` varchar(5527) default null
) ENGINE=InnoDB DEFAULT CHARSET=latin1
> 1118 - 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
> 时间: 0.001s

65535 - (4 +7)/8 - (4 * 2) = 65526

utf8字符集（最长3个字节一个字符）：

CREATE TABLE `a` (
  `c1` varchar(5000) not null,
	`c2` varchar(5000) not null,
	`c3` varchar(5000) not null,
	`c4` varchar(5000) not null,
	`c5` varchar(1841) not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8
> OK
> 时间: 0.039s

CREATE TABLE `a` (
  `c1` varchar(5000) not null,
	`c2` varchar(5000) not null,
	`c3` varchar(5000) not null,
	`c4` varchar(5000) not null,
	`c5` varchar(1842) not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8
> 1118 - 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
> 时间: 0.001s

5525 / 3 = 1841.66，所以c5列最长1841

utf8mb4字符集（最多4个字节表示1个字符）

CREATE TABLE `a` (
  `c1` varchar(3750) not null,
	`c2` varchar(3750) not null,
	`c3` varchar(3750) not null,
	`c4` varchar(3750) not null,
	`c5` varchar(1381) not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
> OK
> 时间: 0.042s


CREATE TABLE `a` (
  `c1` varchar(3750) not null,
	`c2` varchar(3750) not null,
	`c3` varchar(3750) not null,
	`c4` varchar(3750) not null,
	`c5` varchar(1382) not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
> 1118 - 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
> 时间: 0.001s

5525 / 4 = 1381.25，所以c5列最长1381

测试3

MySQL中的varchar(n)中的n指的是字符（数字、字母、或者汉字）的长度，varchar(n)最多存储65535个字节长度的字符。
如果是gbk编码，最大长度≤ 65535 /2 = 32767,
如果是utf8编码，最大长度≤ 65535 /3 = 21845。

CREATE TABLE `a` (
  `c1` varchar(21844) not null,
		`c2` varchar(1) not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8
> 1118 - 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
> 时间: 0.001s

CREATE TABLE `a` (
  `c1` varchar(21844) not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8
> OK
> 时间: 0.05s

21844 * 3 = 65532
再加2 + 1 = 65535