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

Question

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

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

Answer 2

Run SQL query/queries on database

SET GLOBAL innodb_strict_mode=OFF;