mysql的一个字段最多能插入多少数据？我们存入text类型的值上限是多大？

亦凉 2023-06-23 11:56 24阅读 0赞

### 文章目录 ###

*   *  一、前言
     *  二、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）查看当前数据页大小 ####

#### （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`长度的问题，关于`sql`长度可以参考博主之前的博文：[mysql批量插入数据，一次插入多少行数据效率最高？][mysql]

### 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`页面大小 最大表空间大小：

<table> 
 <thead> 
  <tr> 
   <th>数据页大小</th> 
   <th>最大表空间</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>4KB</td> 
   <td>16TB</td> 
  </tr> 
  <tr> 
   <td>8KB</td> 
   <td>32TB</td> 
  </tr> 
  <tr> 
   <td>16KB</td> 
   <td>64TB</td> 
  </tr> 
  <tr> 
   <td>32KB</td> 
   <td>128TB</td> 
  </tr> 
  <tr> 
   <td>64KB</td> 
   <td>256TB</td> 
  </tr> 
 </tbody> 
</table>

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

**参考链接：**

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**

[mysql]: https://blog.csdn.net/LJFPHP/article/details/99708888