Reasons and solutions for MySQL reporting Row size too large 65535_MySQL

Error message: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535

When inserting a field into a MySQL table with a field type of text, or modifying a field with a field type of text, the above error will be reported. Actually, I don’t have a deep understanding of the cause of this error. Let me give some explanations that I found

The general idea is that there is a field index with a set length of 64K in the data table. When the fields in the table (I don’t know whether it is a field name or something) cannot exceed this length, 65,535 indicates that the entire table is not large. The total bytes of the fields of the field type. (found online)

My own understanding (it’s not very clear, please point it out):

My understanding is similar to what I found in the last sentence, that is, the total bytes of non-large field types is greater than 64K. What is the total bytes of this field? I'd better think of it as the sum of the field name lengths. Non-large fields refer to those fields. I think they are fields of varchar field type. The sum of their names is greater than 64K. If the storage space of mysql is exceeded, an error will be reported.

Let me briefly explain varchar. Before the old version 5, the maximum number of characters varchar could store was 255 (it’s not scientific, check it out yourself if you’re interested). After version 5, varchar has variable length, which means it stores data according to actual conditions. Divide the occupied storage space by the number. For example, if there are 50 data in the varchar type, then the storage space it occupies may be 51, and 1 is a placeholder to record how many characters are stored. (Perhaps it’s not that the field name occupies 64k, but that the record length in this field is too much, exceeding the value of 64k) In other words, you can check for yourself whether the varchar field type in your data table is used too much. Much more.

It is true that varchar is used too much. What should I do? In fact, the error statement above explains that the attributes can be converted to text or blobs. If it works, you can try it yourself.

Why are there so many varchars in my data table? Because it is the actual length, which can save some storage space. For example, if I give varchar(300) but I actually store 10 words in this field, it is actually just It occupies 11 characters of storage space, with a maximum of 300 characters.

My solution:

Set the attribute fields in the varchar(N) table where N is less than 255, and change the attribute to tinytext. tinytext always takes up 255 characters when stored. It is a fixed occupation, and the maximum number of characters that can be saved is only 255. So if the N value of varchar(N) is particularly small, tinytext can be used instead. Please consider whether you can use char.

The above is the reason and solution for Mysql reporting Row size too large 65535 introduced by the editor. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply to you in time. . Thank you very much for your support of the website!