行大小太大(> 8126)我可以将InnoDB更改为MyISAM [英] Row size too large (> 8126) can i just change InnoDB to MyISAM

问题描述

我有此错误:

行大小太大(> 8126).将某些列更改为 TEXT 或 BLOB 或使用 ROW_FORMAT = DYNAMIC 或 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.

要解决此问题，我可以将InnoDB更改为MyISAM吗?

To solve this can i just change InnoDB to MyISAM?

推荐答案

是的，您可以切换到MyISAM.但这不一定是个好主意:

Yes, you could switch to MyISAM. But that is not necessarily a good idea:

• MyISAM不支持交易
• MyISAM表在崩溃后通常需要 REPAIR

InnoDB表可以处理每行8KB以上的数据.显然，您有一打或更多的TEXT/BLOB列遇到了这个问题?该行的主要部分最多存储一列的767个字节.其余的放在单独的块中.

An InnoDB table can handle more than 8KB per row. Apparently you ran into the problem by having a dozen or more TEXT/BLOB columns? At most 767 bytes of a column is stored in the main part of the row; the rest is put in a separate block.

我认为一个 ROW_FORMAT 会将所有大列放在一个单独的块中，仅留下20个字节来指向它.

I think one ROW_FORMAT will put all of a big columns in a separate block, leaving behind only 20 bytes to point at it.

另一种处理宽行的方法是进行垂直分区".也就是说，用匹配的 PRIMARY KEY 和一些大列构建另一个表.将稀疏填充的列移动到这样的表中，然后在该表中减少行数并使用 LEFT JOIN 来获取数据尤其方便.另外，如果您有一些不需要进行 SELECT 的列，那么这些列是很好的选择-当您不需要这些列时就不需要 JOIN 列.

Another approach to wide rows is to do "vertical partitioning". That is, build another table (or tables) with a matching PRIMARY KEY and some of the big columns. It is especially handy to move sparsely populated column(s) to such a table, then have fewer rows in that table, and use LEFT JOIN to fetch the data. Also, if you have some column(s) that you rarely need to SELECT, then those are good candidates to move -- no JOIN needed when you don't need those columns.

这篇关于行大小太大(> 8126)我可以将InnoDB更改为MyISAM的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！