解决MySQL8导入数据库库报错[ERR] 1118 - Row size too large (> 8126)问题

1天前阅读：53 类型：后端分类：Mysql 标签： Mysql

一、问题描述

从服务器导出MySQL7导出的数据库在MySQL8下报错[ERR] 1118 - Row size too large (> 8126)一下是navicat报错截图

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

二、问题原因

根据表定义和错误信息 [ERR] 1118 - Row size too large (> 8126)，可以确定表的行大小超出了 MySQL 的限制。这一限制通常适用于 InnoDB 存储引擎的 Compact 行格式。在表中，有许多 VARCHAR(255) 列以及一个 TEXT 列。虽然 TEXT 列的数据不会直接存储在行内，但其他 VARCHAR 列还是会占用行内空间。当多个较大的 VARCHAR 列组合在一起时，很容易超过最大行大小（8126 字节）。

三、问题解决

更改行格式，将表的行格式更改为 DYNAMIC 或 COMPRESSED，这将允许更灵活的空间管理，并使大字段能够存储在行外，从而减少行内存储的大小。

四、例子修改

1、比如这个积分订单表

DROP TABLE IF EXISTS `integral_order`;
CREATE TABLE `integral_order` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `integral_order_num` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '订单号',
 `out_trade_no` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '微信支付流水号',
 `refund_status` tinyint(2) NULL DEFAULT 0 COMMENT '退款状态：未退款：0，已退款：1',
 `pay_type` tinyint(2) NULL DEFAULT 0 COMMENT '支付方式',
 `integral_goods_id` int(11) NULL DEFAULT NULL COMMENT '商品id',
 `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '用户姓名',
 `mobile` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '手机号',
 `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注',
 `integral_goods_type` tinyint(2) NULL DEFAULT NULL COMMENT '商品类型：0虚拟，1实物',
 `address` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '收货地址',
 `member_id` int(11) NULL DEFAULT NULL COMMENT '会员id',
 `openid` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '会员openid',
 `nickName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '会员昵称',
 `avatar` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '会员头像',
 `integral_score` int(11) NULL DEFAULT NULL COMMENT '兑换积分',
 `integral_price` decimal(10, 2) NULL DEFAULT NULL COMMENT '+兑换金额',
 `integral_num` int(11) NULL DEFAULT NULL COMMENT '商品数量',
 `isPrice` tinyint(2) NULL DEFAULT NULL COMMENT '是否需要+额外兑换金额兑换',
 `tracking` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '快递名称',
 `tracking_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '快递类型',
 `tracking_number` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '快递单号',
 `tracking_content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '快递信息',
 `integral_order_status` tinyint(2) NULL DEFAULT NULL COMMENT '订单状态:1已兑换，2待收货，3已完成，4已取消,5未支付',
 `pay_time` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '订单支付时间',
 `integral_order_time` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '下单时间',
 `integral_order_send_time` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '发货时间',
 `integral_order_complete_time` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '完成时间',
 `integral_order_cancellation_time` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '取消时间',
 `refund_author` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '退款操作人',
 `refund_time` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '退款时间',
 PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 31 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact;

2、我们将其最后一个的ROW_FORMAT = Compact 改为 ROW_FORMAT=COMPRESSED，如下：

DROP TABLE IF EXISTS `integral_order`;
CREATE TABLE `integral_order` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `integral_order_num` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '订单号',
 `out_trade_no` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '微信支付流水号',
 `refund_status` tinyint(2) NULL DEFAULT 0 COMMENT '退款状态：未退款：0，已退款：1',
 `pay_type` tinyint(2) NULL DEFAULT 0 COMMENT '支付方式',
 `integral_goods_id` int(11) NULL DEFAULT NULL COMMENT '商品id',
 `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '用户姓名',
 `mobile` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '手机号',
 `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注',
 `integral_goods_type` tinyint(2) NULL DEFAULT NULL COMMENT '商品类型：0虚拟，1实物',
 `address` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '收货地址',
 `member_id` int(11) NULL DEFAULT NULL COMMENT '会员id',
 `openid` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '会员openid',
 `nickName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '会员昵称',
 `avatar` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '会员头像',
 `integral_score` int(11) NULL DEFAULT NULL COMMENT '兑换积分',
 `integral_price` decimal(10, 2) NULL DEFAULT NULL COMMENT '+兑换金额',
 `integral_num` int(11) NULL DEFAULT NULL COMMENT '商品数量',
 `isPrice` tinyint(2) NULL DEFAULT NULL COMMENT '是否需要+额外兑换金额兑换',
 `tracking` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '快递名称',
 `tracking_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '快递类型',
 `tracking_number` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '快递单号',
 `tracking_content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '快递信息',
 `integral_order_status` tinyint(2) NULL DEFAULT NULL COMMENT '订单状态:1已兑换，2待收货，3已完成，4已取消,5未支付',
 `pay_time` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '订单支付时间',
 `integral_order_time` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '下单时间',
 `integral_order_send_time` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '发货时间',
 `integral_order_complete_time` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '完成时间',
 `integral_order_cancellation_time` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '取消时间',
 `refund_author` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '退款操作人',
 `refund_time` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '退款时间',
 PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 31 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = COMPRESSED;

这样就成功解决了该问题。