文章目录

  • 一、前言
  • 二、mysql的行大小限制
  • 1、结论如下
  • 2、行大小限制
  • 3、页大小设置以及特性
  • （1）查看当前数据页大小
  • （2）关于数据页的解释
  • 4、text类型和blob类型的特殊之处
  • 5、text和blob类型的长度限制
  • 6、总结
  • 三、引申
  • 1、批量插入text类型问题
  • 2、表空间大小限制


一、前言

      自从上次探究了mysql的一个表究竟能有多少列数据之后，博主就一直想知道每个字段的长度是否有限制，各个类型之间的限制是否相同，奈何时间不允许。今日难得空闲，就让我们来研究研究这个知识点~

      首先，这里说的mysql指的是mysql5.7版本，不过也适用于5.5-5.7版本，其次这里探究的是Innodb引擎。

二、mysql的行大小限制

1、结论如下

      行大小确实是有限制，只不过这个限制是针对于除了text或者blob以外的其他类型。其中总长度要小于65,535字节的最大行大小限制。数据方面（也就是一行记录的总数据），要小于一页的一半，一般一页是16kb的话，那么长度应该是小于8kb的。text类型的上限是65535字节，也就是64kb。

      OK，鉴于这篇文章比较长，所以结论先给出来，方便暴躁老哥们查看，下面咱们就来详细探究一下这部分。

2、行大小限制

手册链接  ：https://dev.mysql.com/doc/refman/5.5/en/column-count-limit.html#row-size-limits

      关于行大小限制，我们根据手册可知：

      MySQL表的内部表示具有65,535字节的最大行大小限制，即使存储引擎能够支持更大的行也是如此，表的最大行大小（适用于本地存储在数据库页面内的数据）略小于页面的一半，比如16kb的页，那么最大行大小应小于：8192b

      针对于变长字段，varchar(M)，我们设置字段的时候，需要注意的是M的和不应该超过65535，因为是变长，所以暂时不用考虑实际数据小于8192b的问题，但是在存储的时候，还是要考虑这点的。

      针对于定长字段char(M)，在创建表的时候，就应该注意M的和不能超过8192，因为是定长，所以行的总字节长度相当于是确定的了。

      当然，对于text和blob类型的，自然就没有小于数据页大小的一半这种设置了

3、页大小设置以及特性

（1）查看当前数据页大小

mysql> show variables like '%innodb_page_size%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
1 row in set, 1 warning (0.01 sec)

（2）关于数据页的解释

      innodb的数据页大小默认是16kb，不过目前也支持32kb和64kb。对于16kb和32kb的页，行大小限制为页大小的一半，分别是8kb和16kb。当页设置为64kb的时候，行大小限制依然为16kb。同时注意，当页大小设置比较大的时候， innodb_log_buffer_size使用32KB或64KB页面大小时，应至少设置为16M（默认值）。

根据手册我们可以简单总结下：

      当存储设备比较大，并且业务更多偏向于全表扫描，批量操作等，可以考虑设置比较大的页大小。如果我们平时批量操作比较小，更多的是单个操作的时候，使用默认的页大小是比较合适的，也能避免同一页的行太多造成的竞争问题

数据页参考：https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_page_size

4、text类型和blob类型的特殊之处

      手册上说：BLOB和 TEXT列仅有助于朝向行大小限制9〜12字节，因为它们的内容是从该行的其余部分分开存储。（存储其他页地址的部分共有20个字节，此处只是占用这么多，并没有占满）

      我们知道根据行格式，这种数据量比较大的存储相当于行溢出，如果某一列中的数据非常多的话，在本记录的真实数据处只会存储该列的前768个字节的数据和一个指向其他页的地址，然后把剩下的数据存放到其他页中，这个过程也叫做行溢出，存储超出768字节的那些页面也被称为溢出页。

innodb的文件空间管理：https://dev.mysql.com/doc/refman/5.7/en/innodb-file-space.html

      那么现在就明确了，存储text类型的时候，不用担心数据量会太大之类的，因为它是存在了其余部分，我们的行格式只记录一个地址。但是虽然说是不用担心，但是肯定是有上限的，我们继续查找手册。

5、text和blob类型的长度限制

      text类型的数据是存储在该行数据页之外的页中，那么text类型的数据存储没有上限吗？肯定是有的，我们继续看手册。

手册：https://dev.mysql.com/doc/refman/5.7/en/blob.html

      在数据类型存储章节我们可以看到，text和 blob的实际长度限制是 L < 2的16次方，也就是 L < 65536 ，单位是字节,大概是：64kb

如果按照utf8算，一个中文=3个字节，那么L< 21845中文，这么一看似乎能存储的数据也不是很多，或者说有些少，我们测试下：

参照test22.php：

$link = mysqli_connect('localhost','root1','root');
if(!$link){
    exit('mysql数据链接失败');
}
mysqli_select_db($link,'manage');
mysqli_set_charset($link,'utf-8');
$json_data = file_get_contents('./funnelConfig4.json');
$len = strlen($json_data);
$sqls = " (1,1,1,'1.0',1,0,$len,'{$json_data}')";
$sql = "insert into funnel_json(user_id,project_id,event_id,version,ab_tag,status,add_time,json_data) values {$sqls}";
$rs = mysqli_query($link,$sql);
if(! $rs )
{
    die('无法插入数据: ' .'长度：'.$len  . mysqli_error($link));
}
echo "数据插入成功\n：长度是:".$len;
mysqli_close($link);

/*
 *第一次插入成功，字符为：//var_dump(strlen($json_data,'utf8')); // 36435 个字符
 * json文件为40k
 * 数据插入成功
bool(true)

第二次换成80k的数据试试
-> /c/phpStudy/PHPTutorial/WWW
$ php test22.php

无法插入数据: 长度：长度：81023 Data too long for column 'json_data' at row 1


第三次换成极限64k左右试试
小于64k的时候是可以插入的，
数据插入成功：长度是:64501

但是大于64k就无法插入了
无法插入数据: 长度：：69739 Data too long for column 'json_data' at row 1
 *
 * */

      根据测试，text字段的上限就是存储64kb的数据，当插入大于64kb的时候报错：xxxx Data too long for column 'json_data' at row 1 。 这个怎么说呢，是有点小的，所以咱们可以考虑选用更大类型的text！

如果要存储的字符串比较长的话，建议选用：

MEDIUMBLOB， MEDIUMTEXT		长度限制是： L<2 的 24次方，大概是：16M
LONGBLOB， LONGTEXT		长度限制是： L<2 的 32次方，大概是：4GB

      选用 MEDIUMTEXT 能存储的字符已经很多了，如果这个还不能满足需求，那么就该考虑是否要存储成文件了，毕竟mysql还是存一些关键字符比较好，查询也方便。博主要存储的json字符串大概是100k左右，因此选用MEDIUMTEXT类型，大家可以参考一下。

6、总结

      根据上面的分析，我们发现不管是单独的text，blob，还是针对表的一行记录，都有个65,535字节的限制。这块可以理解为，针对不是text或者blob类型的列，其一行记录的上限是65535，其中text和blob类型只占用9-12字节。而针对于text或者blob类型的数据，这部分数据是存储在其他数据页的，这条记录的数据页只会记录它的一个地址，同时虽然text和blob放到其他数据页了，但是还是有65535的限制。甚至可以说，对于一条数据来说，它的行大小限制是65535,也就是：

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

这个是单行的所有列的长度声明加起来大于65535的，会创建表报错。

同时他的单列长度也是有65535限制的：

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的，照样会报错。（这里选用latin1编码来测试，一个字节就是一个字符，更直观一些）

三、引申

1、批量插入text类型问题

      如果要批量插入含有text列的数据，那么需要注意 max_allowed_packet的设置，也就是sql长度的问题

2、表空间大小限制

      既然数据表的行和列都有大小限制，那么整个表的限制又有多大呢，既然手册都翻了，来都来了，不研究下实在可惜。

      表空间的大小限制和数据页的大小设置是有关的，根据手册，最小表空间大小略大于10MB。最大表空间大小取决于 InnoDB页面大小。查看表空间sql:

use information_schema
select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES where table_schema='数据库名' and table_name='数据表名';

最大表空间大小没法测试，但是最小表空间大小还是可以测试的，博主有点不相信最小的表空间有10MB，测试如下：

mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES where
 table_schema='test' and table_name='test1';
+--------+
| data   |
+--------+
| 0.02MB |
+--------+
1 row in set (0.00 sec)

      这个test库下的test1表只有几条数据，这。。。行叭，可能是博主查看的只是表大小，手册上说的是表空间吧。。

InnoDB页面大小 最大表空间大小：

数据页大小

最大表空间

4KB

16TB

8KB

32TB

16KB

64TB

32KB

128TB

64KB

256TB

      这下好了，不管是列限制还是行限制，咱们基本都测试了一个遍，虽然业务上用处不大，不过也算是内功了，积攒积攒还是很有威力的哈哈。

参考链接：

https://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-limits.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-file-space.html

end