mysql 创建表查询中的行大小过大错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13283381/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Row size too large error in mysql create table query
提问by Shiva Krishna Bavandla
I am trying to create a table with the below query
我正在尝试使用以下查询创建一个表
Create Table PerformaceReport
(
campaignID int,
keywordID bigint,
keyword varchar(8000),
avgPosition decimal(18,6),
cost int,
clicks int,
conv1PerClick int,
impressions int,
day datetime,
currency varchar(8000),
account varchar(8000),
timeZone varchar(8000),
adGroup varchar(8000),
adGroupState varchar(8000),
approvalStatus varchar(8000),
lowestPosition varchar(8000),
campaign varchar(8000),
campaignState varchar(8000),
convManyPerClick int,
totalConvValue decimal(18,6),
maxCPCSource varchar(8000),
clientName varchar(8000),
destinationURL varchar(8000),
device varchar(8000),
firstPageCPC int,
isNegative bit,
matchType varchar(8000),
maxCPC varchar(8000),
maxCPM varchar(8000),
highestPosition varchar(8000),
qualityScore int,
keywordState varchar(8000),
viewThroughConv int)
And i am getting the below error
我收到以下错误
#1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs
Can anyone please let me know how to avoid this error and make the query work to create a table.
任何人都可以让我知道如何避免此错误并使查询工作以创建表。
回答by Aziz
The total size of all fields in the table is more than the limit, 65535, that's why you are getting this error.
表中所有字段的总大小超过限制 65535，这就是您收到此错误的原因。
You should use texttype instead of varcharfor long strings. Replace all varchar(8000)with text, and it should work.
您应该使用texttype 而不是varchar长字符串。全部替换varchar(8000)为text，它应该可以工作。
Or, even better, use appropriate data types instead of the "too large" ones. You don't really need 8000 characters to store currency, do you?
或者，更好的是，使用适当的数据类型而不是“太大”的数据类型。你真的不需要 8000 个字符来存储currency，是吗？
回答by Akhil
Use TEXTinstead of VARCHAR.
Because you're exceeding the maximum row size.
if you use TEXT,it is intended for large text columns.
Max size of varcharis 65535.
create a column with varchar(65535)but it would have to be the only column in the table.
使用TEXT代替VARCHAR。因为您超出了最大行大小。如果您使用TEXT，它适用于大文本列。最大大小varchar为 65535。创建一个列，varchar(65535)但它必须是表中唯一的列。
or
或者
problem is the row size limit for innodb tables, belowlinks you can find some approaches to solve this:
问题是 innodb 表的行大小限制，下面的链接您可以找到一些解决此问题的方法：
http://www.mysqlperformanceblog.com/2011/04/07/innodb-row-size-limitation/https://dba.stackexchange.com/questions/6598/innodb-create-table-error-row-size-too-large
http://www.mysqlperformanceblog.com/2011/04/07/innodb-row-size-limitation/ https://dba.stackexchange.com/questions/6598/innodb-create-table-error-row-size-太大了
回答by Peter
I agree with the answer on using appropriately sized columns and TEXT over VARCHAR as the first step, but if you still hit limits you may want to change your collation settings for that table if you are using UTF-8 or another character set with more than one byte per character and do not need it (only storing English text for example). I did this to get around the limit you are hitting for a very wide table. Some more detail here.
我同意在 VARCHAR 上使用适当大小的列和 TEXT 作为第一步的答案，但如果您仍然达到限制，如果您使用 UTF-8 或其他字符集，您可能需要更改该表的排序规则设置每个字符一个字节，不需要它（例如只存储英文文本）。我这样做是为了绕过您对一张非常宽的桌子所达到的限制。这里有更多细节。
回答by g10guang
65535 bytes is the max row size for mysql.
65535 字节是 mysql 的最大行大小。
With utf8mb4 charset, varchar(255) means this column at most uses 255 * 4 + 1bytes. So it depends on what charset table use.
使用 utf8mb4 字符集，varchar(255) 意味着此列最多使用255 * 4 + 1字节。所以这取决于使用什么字符集表。
回答by ufuk
This problem came out in Laravel and I used text instead of varchar.
这个问题出现在 Laravel 中，我使用文本而不是 varchar。
回答by R.Lee
- Every table (regardless of storage engine) has a maximum row size of 65,535 bytes. Storage engines may place additional constraints on this limit, reducing the effective maximum row size.
- BLOB and TEXT columns count from one to four plus eight bytes each toward the row-size limit because their contents are stored separately from the rest of the row.
- detailed information - Limits on Table Column Count and Row Size
- 每个表（无论存储引擎如何）的最大行大小为 65,535 字节。存储引擎可能会对此限制设置额外的约束，从而减少有效的最大行大小。
- BLOB 和 TEXT 列从 1 到 4 加 8 个字节计数，达到行大小限制，因为它们的内容与行的其余部分分开存储。
- 详细信息 - 表列数和行大小的限制
回答by Sunit
Try changing the storage engine to CSV.
尝试将存储引擎更改为 CSV。
