MySQL error 1118, the data type is too long
MySQL is the most popular open source relational database management system in the world.
MySQL provides many features, from simple data queries to complex data operations and management.
During the use of MySQL, we often encounter various problems, one of the more common problems is the error 1118.
Next, we will explain the reasons and solutions for MySQL reporting 1118 error.
1118 errors usually appear when creating a table.
The error message is
“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”。
This error means that when creating a table, the lengths of some columns are added up to 65535 that MySQL allows. When calculating the row length, removing the length of the BLOB type column also requires adding the overhead of row storage.
The reason for this error is that the table structure we created contains too many long text types (such as VARCHAR, TEXT) and does not have enough space to store other columns.
For columns of type VARCHAR, MySQL needs to allocate additional space to it to suit its varying string length. So, when we use a large number of long text of VARCHAR type in the table, it causes a 1118 error.
Solve this problem
We can use two methods:
One is to replace columns of type VARCHAR with columns of type TEXT or BLOB.
Since columns of TEXT and BLOB types do not require additional storage space, the 1118 error can be avoided.
However, it should be noted that these columns do not support indexing, sorting, or wildcard queries.
/** *When creating the table, there are about 170+ fields. Most of the fields varchar(255) lengths. I used to think that the field name was too long, and then I searched online to replace the varchar with the text type, but I didn't try it. I directly deleted the general fields and the table was created successfully. Because the maximum length allowed by MYSQL is 65535, which is just a virtual length, but the actual length is not that long at all. I guess it's only 50,000 in length Because the fields are important, I changed all 255 lengths to 100 lengths and finally created the table successfully! ! ! */
Summarize
The above is personal experience. I hope you can give you a reference and I hope you can support me more.