找到你要的答案

Q:SQL Error (1118):Row size too large. The maximum row size for the used table type, not counting BLOBs

Q:SQL错误(1118):行尺寸太大。所使用的表式的行大小的最大值,不包括斑点

I have create one MySQL database table. I have to create 195 columns in one table. I have to give the data types of 190 columns is VARCHAR and 5 columns data type are text. Each columns have length 500.

Actually I want to create 1000 columns in one database table and I want to use data type is VARCHAR in each table columns. Now when I am creating new columns in database table. I am getting this error:

SQL Error (1118):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

我已创建一个MySQL数据库表。我必须在一个表中创建195列。我要给190列的数据类型为VARCHAR列和5列的数据类型是文本。每个列长度为500。

其实我想在一个数据库表的创建1000列,我想使用的数据类型是在每个表列varchar。现在在数据库表中创建新列时。我得到这个错误:

SQL错误(1118):行尺寸太大。所使用的表式的行大小的最大值,不包括斑点, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

answer1: 回答1:

You database design is flawed.

Maximum row size with MySQL = 65,535 bytes.

Assuming 1 byte per character 500 X 1000 = 50,000 bytes per row which is approaching 65,535.

For VARCHAR(L) using latin1 1 byte per column is required to hold length L so now we are at 501,000.

For a VARCHAR column that stores multibyte characters, the effective maximum number of characters is less. For example, utf8 characters can require up to three bytes per character.

500 X 4 X 1000 = 2,000,000 bytes (3 per char + 1 for length)

For more information MySQL Documentation MySQL Maximum Number of Columns AND Data Type Storage Requirements.

I suggest you learn more about database design before proceeding.

你的数据库设计有缺陷。

MySQL = 65535字节的行大小的最大值。

假设1字节每字符500×1000 = 50000字节每行接近65535。

对于varchar(L)使用latin1 1字节每列要求长度L,所以我们现在在501000。

对于一个VARCHAR列中存储多字节字符,字符的有效最大数量较少。例如,UTF8字符需要三字节,每个字符。

500×4×2000000字节(每字符3 + 1的长度)

更多信息,MySQL的文档MySQL最大数量的列和数据类型的存储要求。

我建议你在开始之前了解更多数据库设计。

mysql