ERROR 1118 (42000): Row size too large. The maximum row size for the used table type

楠奕

于 2024-08-09 18:10:37 发布

阅读量765

点赞数 14

分类专栏：数据库 Linux 文章标签：前端 javascript

本文链接：https://blog.csdn.net/red_sky_blue/article/details/141068034

版权

Linux 同时被 2 个专栏收录

34 篇文章 0 订阅

订阅专栏

数据库

7 篇文章 0 订阅

订阅专栏

报错sql

CREATE TABLE `modo_notify` (
    ->   `notify_id` varchar(32) NOT NULL,
    ->   `audience_type` varchar(128) DEFAULT NULL,
    ->   `notify_audiences` varchar(1024) DEFAULT NULL,
    ->   `notify_content` varchar(4000) DEFAULT NULL,
    ->   `create_date` varchar(64) DEFAULT NULL,
    ->   `create_user` varchar(128) DEFAULT NULL,
    ->   `end_date` varchar(64) DEFAULT NULL,
    ->   `notify_files` varchar(4000) DEFAULT NULL,
    ->   `notify_images` varchar(4000) DEFAULT NULL,
    ->   `publish_date` varchar(64) DEFAULT NULL,
    ->   `recieve_users` varchar(1024) DEFAULT NULL,
    ->   `notify_title` varchar(128) DEFAULT NULL,
    ->   `notify_type` varchar(1024) DEFAULT NULL,
    ->   `valid` varchar(128) DEFAULT NULL,
    ->   `file_name` varchar(512) DEFAULT NULL,
    ->   `file_path` varchar(512) DEFAULT NULL,
    ->   PRIMARY KEY (`notify_id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


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

意不意外，惊不惊喜。

分析

chatGpt一问

MySQL中的错误ERROR 1118 (42000)表示单行的数据长度超过了允许的最大值。这通常发生在InnoDB表中，因为InnoDB有一个限制，即单行的最大长度不能超过65535字节。这个限制主要来自于InnoDB的页大小，通常是16KB。

解决方法：

减少列的数量或者减少每个列的数据类型大小。

使用VARCHAR、TEXT、BLOB数据类型代替CHAR和ENUM类型，因为VARCHAR类型能够更高效地存储大数据。

如果是因为单个列的长度过长（如VARCHAR(255)或TEXT列在存储较大数据时），考虑减少这些列的最大长度。

如果是因为索引太多，考虑减少不必要的索引，或者重新设计数据库模式，使用更合适的数据类型。

如果确实需要存储大量数据，可以考虑将大字段拆分到其他表中，通过JOIN查询来使用这些数据。

看其他大神的解答

报错原因：

MySQL 建表时有一个单行最大限制长度限定：一张表中所有字段设置的字节数总和不大于 65535 字节。

注意点一：单个字段大小超过 65535 转换为 TEXT。
注意点二：其余字段总和不超过 65535 字节（不包括 BLOB/TEXT）。
注意点三：数据库使用 UTF-8 编码，一个字符 = 三个字节大小（使用编码不同，字节数大小略有不同）。
报错举例：数据库存在 10 个 varchar 字段，每个大小为 3000 则数据库单行目前计算长度为 3000 * 10 * 3 = 90000 > 65535 ，则建表时就会报错。

解决办法：

将数据库表大字段类型设置为 TEXT，或者将部分可以减小长度的长度调小至总和小于 65535。

调整完之后，执行建表语句，又报如下错误：

1. Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.（低版本报错）

2. 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. （新版本报错）

上面两个错误信息，前者基本出现在 ROW_FORMAT <> DYNAMIC 或者较早版本的情况，在其中 BLOB 需要存储 768 字节在行内部。对整个行 size 贡献较大。

后者基本对于较新的版本，因为默认的 ROW_FORMAT = DYNAMIC，在其中，一个 TEXT 或者 BLOB 字段对行 size 的贡献在 9-12 个字节之间。

对于第一种情况，可以设置 innodb_file_per_table = 1，innodb_default_row_format = dynamic，又因为 dynamic 要求 innodb_file_format 必须为 Barracuda，所以一般还要加上 innodb_file_format = Barracuda 设置。innodb_default_row_format = dynamic 可以在创建表的时候动态指定。当然也可以按照提示那样的，设置 ROW_FORMAT =COMPRESSED，这个对于只读场景用处比较大，如果用于读写负载，那比较不好。

在数据库执行如下语句：

mysql> SHOW GLOBAL VARIABLES LIKE '%innodb_file%';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_file_format       | Barracuda |
| innodb_file_format_check | ON        |
| innodb_file_format_max   | Barracuda |
| innodb_file_per_table    | ON        |
+--------------------------+-----------+
4 rows in set (0.01 sec)

mysql>

确保 innodb_file_format 使用的是 Barracuda，innodb_file_per_table 使用的是 ON，如果不是执行以下语句（不用重启 MySQL）：

SET GLOBAL innodb_file_format = barracuda;
SET GLOBAL innodb_file_per_table = ON;

或者，在配置文件中添加使用独立表空间的配置：innodb_file_per_table=1
修改配置文件 my.cnf（需要重启 MySQL）：

innodb_file_per_table = ON;
innodb_file_format = barracuda;

或者，建表语句设置 ROW_FORMAT =COMPRESSED：

create table_name (
...
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FROMAT=COMPRESSED COMMENT='表注释';

如果上面的方法仍然解决不了问题，那还有其它办法：

1、关掉 innodb_strict_mode，这个选项是在创建表的时候检查行大小，如果确定实际存储的字段没有这么多，可以关掉。但是问题是如果确实有这么多内容，插入的时候会报错。
查看：

mysql> SHOW VARIABLES LIKE '%innodb_strict_mode%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| innodb_strict_mode | ON    |
+--------------------+-------+
1 row in set (0.00 sec)

mysql>

修改：

SET SESSION innodb_strict_mode = OFF
或者
SET innodb_strict_mode = OFF

以上 OFF 也可以用 0 代替，ON 也可以用 1 代替。

2、将 innodb_page_size 调整成 64K，这样，64K 的 page 即使需要容纳2行数据的话，每行也可以最大达到 32K(实际达不到，因为 header 和 footer 需要空间)。但是这个最好把现有的 MySQL 备份出来，然后按照新的 page size 重新初始化，再导入备份，保证整个库都使用统一的 page size 大小，以免出现稀奇古怪的问题。

set global innodb_page_size = 65536

3、可以看到上面的提示 In current row format, BLOB prefix of 0 bytes is stored inline，把较长的字段都转成 TEXT 或者 BLOB 存储。

以上步骤可以一个一个试，基本就可以解决自己的问题了。

详细解释：

ERROR1118 的报错信息分为两种：

1. 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（定义到这个长度也会报错，行本身维护也会占用字节），建议使用 TEXT 或 BLOBs 类型。

2. 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.

一条记录太长，超过了 8126 字节，建议部分列使用 TEXT 或 BLOB 类型。

看到这两个报错信息，有些人可能就会有疑问，感觉描述的有些冲突，一个说一条记录最大长度不超过 65535 字节，一个说长度不能超过 8126 字节。那么到底哪个是正确的的呢？
先看下官方文档的描述：
https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html#row-size-limits

【The MySQL maximum row size limit of 65,535 bytes is demonstrated in the following InnoDB and MyISAM examples. The limit is enforced regardless of storage engine, even though the storage engine may be capable of supporting larger rows.】

在 MySQL 数据库中一条记录的最大长度是 65535 字节，在下面的 InnoDB 和 MyISAM 示例中做了演示。尽管存储引擎可能能够支持更大的行，但无论使用何种存储引擎，都会强制执行该限制。

【InnoDB restricts row size (for data stored locally within the database page) to slightly less than half a database page for 4KB, 8KB, 16KB, and 32KB innodb_page_size settings, and to slightly less than 16KB for 64KB pages.】

InnoDB 限制一条记录的大小(对于本地存储在数据库页面中的数据)，对于 4KB、8KB、16KB 和 32KB 的 innodb_page_size 设置为略小于数据库页面的一半，对于 64KB 的页面则略小于 16KB。

小结：
一条记录最大长度 65535 字节是 MySQL 数据库 Server 层面的限制，默认情况下，InnoDB 页面大小是 16KB，所以一条记录在页面中的存储长度不能超过 8126 字节，这是 InnoDB 存储引擎的限制。

这里可能会有些疑问，平常创建 varchar(10000) 类型字段，已经超过 8126 了，但也没报这个错误，这个和 InnoDB 的存储一条记录的格式有关系，官方文档对存储格式的说明：
https://dev.mysql.com/doc/refman/5.7/en/innodb-row-format.html#innodb-row-format-compact

【Fixed-length columns greater than or equal to 768 bytes are encoded as variable-length columns, which can be stored off-page】

当列的长度超过 768 字节时，多余的内容会存储到一个溢出页上，compact/dynamic 格式在这方面是一样的。
也就是说创建了 varhcar(10000) 类型字段，同时写入到 10000 字节的数据，其实只有 768 个字节存储在数据页面上，其余的字节存储在溢出页面上。

总结：
创建表和写入数据时有两个限制，一个是 Server 层面的限制，一条记录最大长度不能超过 65535（真实创建的记录长度到不了 65535，因为记录本身也需要一些字节去维护），另一个是 InnoDB 层面的限制，一条记录存储在页面中的长度不能够超过 8126 字节。

行大小限制示例：

1、在 MySQL 数据库中一条记录的最大长度是 65535 字节，在下面的 InnoDB 和 MyISAM 示例中做了演示。尽管存储引擎可能能够支持更大的行，但无论使用何种存储引擎，都会强制执行该限制。

创建一个表 t，记录长度之合超过 65535。默认字符集是 latin1，一个字符占一个字节，如果用的 utf8，则一个字符占用 3 个字节。要在定义的 varchar 字段类型上乘以 3 才是占用字节数（不同字符集所占字节数可能不同）。

InnoDB 示例：

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 示例：

 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

在下面的 MyISAM 示例中，将列更改为 TEXT 避免了 65535 字节的行大小限制，所以就操作成功了，因为 BLOB 和 TEXT 列只贡献了 9-12 个字节的行大小（6 * 10000 + 9 ~ 6 * 10000 + 12）。

CREATE TABLE t2 (
       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中 65535 字节的行大小限制，而 InnoDB 变长列的分页存储可避免 InnoDB 行大小限制（< 8126 的限制）。

看 t2 表，varchar 类型是 10000，记录最长是 5 * 10000 = 50000 字节，再加上TEXT 字段对行 size 贡献的 9-12 个字节，没有达到 server 层面的限制。
对于可变长字段，数值超过 768 个字节的，字段的前 768 字节存储在 InnoDB 页面上，其余的数据存储在溢出页面上。t2 表一共 7 个字段，每个字段只有前边的 768 字节存储在 InnoDB 页面上，7 * 768 = 5376 字节，没有达到 InnoDB 存储引擎 8126 的限制，不会报错，所以创建成功。

CREATE TABLE t2 (
       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)

可变长度列的存储包括长度字节（值的长度），它被计入行大小。例如，VARCHAR（255）CHARACTER SET utf8mb3 列需要两个字节来存储值的长度，因此每个值最多占用 767 个字节。如下示例：

CREATE TABLE t3 (
       c1 VARCHAR(32765) NOT NULL, 
       c2 VARCHAR(32766) NOT NULL
       ) ENGINE = InnoDB CHARACTER SET latin1;
       
Query OK, 0 rows affected (0.02 sec)

上面创建表 t3 的语句成功，因为列需要 32765 + 2 字节和 32766 + 2 字节，这在 65535 字节的最大行大小范围内。

CREATE TABLE t3 (
       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

上面创建表 t3 的语句失败，因为尽管列长度在 65535 字节的最大长度内，但需要另外两个字节来记录长度，这会导致行大小超过 65535 个字节。

2、InnoDB 限制一条记录的大小(对于本地存储在数据库页面中的数据)，对于 4KB、8KB、16KB 和 32KB 的 innodb_page_size 设置为略小于数据库页面的一半，对于 64KB 的页面则略小于 16KB。

示例：

创建表 t4，使用 char(255) 定长字符串类型，char 类型无论写入的内容多少（当然，一定要小于等于 255），在实际存储时都会占用 255 个字节。一共33字段，每个字段定长 255 字节，33 * 255 = 8415，每个记录最大长度是 8145 字节，是 Server 层的限制之内，所以没报 65535 的错误，但一条记录在 InnoDB 页面存储时超过了 8126 限制，所以 InnoDB 存储引擎报错了

原文链接：https://blog.csdn.net/qq_45124566/article/details/127507577

我的解决方案

有没有可能，你换个编码格式创建表，它就好了呢

mysql> CREATE TABLE `modo_notify` (
    ->   `notify_id` varchar(32) NOT NULL,
    ->   `audience_type` varchar(128) DEFAULT NULL,
    ->   `notify_audiences` varchar(1024) DEFAULT NULL,
    ->   `notify_content` varchar(4000) DEFAULT NULL,
    ->   `create_date` varchar(64) DEFAULT NULL,
    ->   `create_user` varchar(128) DEFAULT NULL,
    ->   `end_date` varchar(64) DEFAULT NULL,
    ->   `notify_files` varchar(4000) DEFAULT NULL,
    ->   `notify_images` varchar(4000) DEFAULT NULL,
    ->   `publish_date` varchar(64) DEFAULT NULL,
    ->   `recieve_users` varchar(1024) DEFAULT NULL,
    ->   `notify_title` varchar(128) DEFAULT NULL,
    ->   `notify_type` varchar(1024) DEFAULT NULL,
    ->   `valid` varchar(128) DEFAULT NULL,
    ->   `file_name` varchar(512) DEFAULT NULL,
    ->   `file_path` varchar(512) DEFAULT NULL,
    ->   PRIMARY KEY (`notify_id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)