更改"Mysql行大小太大"的限制 [英] Change limit for "Mysql Row size too large"
问题描述
如何更改限额
行大小太大(> 8126).将某些列更改为TEXT或BLOB或使用ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED
可能会有所帮助.在当前行格式中,内联存储768个字节的BLOB
前缀.
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.
表格:
id int(11) No
name text No
date date No
time time No
schedule int(11) No
category int(11) No
top_a varchar(255) No
top_b varchar(255) No
top_c varchar(255) No
top_d varchar(255) No
top_e varchar(255) No
top_f varchar(255) No
top_g varchar(255) No
top_h varchar(255) No
top_i varchar(255) No
top_j varchar(255) No
top_title_a varchar(255) No
top_title_b varchar(255) No
top_title_c varchar(255) No
top_title_d varchar(255) No
top_title_e varchar(255) No
top_title_f varchar(255) No
top_title_g varchar(255) No
top_title_h varchar(255) No
top_title_i varchar(255) No
top_title_j varchar(255) No
top_desc_a text No
top_desc_b text No
top_desc_c text No
top_desc_d text No
top_desc_e text No
top_desc_f text No
top_desc_g text No
top_desc_h text No
top_desc_i text No
top_desc_j text No
status int(11) No
admin_id int(11) No
推荐答案
也已在 serverfault 上提出了该问题.>
The question has been asked on serverfault too.
您可能想看看此文章,其中有很多解释 关于MySQL行大小.重要的是要注意,即使您使用 TEXT或BLOB字段,您的行大小仍可能超过8K(限制为 InnoDB),因为它为内联的每个字段存储前768个字节 页面.
You may want to take a look at this article which explains a lot about MySQL row sizes. It's important to note that even if you use TEXT or BLOB fields, your row size could still be over 8K (limit for InnoDB) because it stores the first 768 bytes for each field inline in the page.
解决此问题的最简单方法是使用梭子鱼文件格式 与InnoDB.这基本上完全摆脱了这个问题 仅存储指向文本数据的20字节指针,而不是存储 前768个字节.
The simplest way to fix this is to use the Barracuda file format with InnoDB. This basically gets rid of the problem altogether by only storing the 20 byte pointer to the text data instead of storing the first 768 bytes.
适用于OP的方法是:
The method that worked for the OP there was:
-
在
[mysqld]
部分下的my.cnf
文件中添加以下内容.
Add the following to the
my.cnf
file under[mysqld]
section.
innodb_file_per_table=1
innodb_file_format = Barracuda
ALTER
该表以使用ROW_FORMAT=COMPRESSED
.
ALTER TABLE nombre_tabla
ENGINE=InnoDB
ROW_FORMAT=COMPRESSED
KEY_BLOCK_SIZE=8;
以上内容仍然可能无法解决您的问题.它是 InnoDB 的已知(已验证)错误. strong>引擎,目前的临时解决方案是回退到 MyISAM 引擎作为临时存储.因此,在您的my.cnf
文件中:
There is a possibility that the above still does not resolve your issues. It is a known (and verified) bug with the InnoDB engine, and a temporary fix for now is to fallback to MyISAM engine as temporary storage. So, in your my.cnf
file:
internal_tmp_disk_storage_engine=MyISAM
这篇关于更改"Mysql行大小太大"的限制的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!