The reason why Mysql reports Row size too large 65535 and its solution are as follows: row65535

The reason why Mysql reports Row size too large 65535 and its solution are as follows: row65535

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

When a field type of mysql table plug-in is text or a field type of text is modified, the above error is reported. In fact, I do not have a deep understanding of the cause of this error. Please give me some explanations.

The general idea is that there is a field index with a set length of 64 K in the data table. When the field (not sure whether it is a field name or something) in the table cannot exceed this length, 65,535 indicates that the bytes sum of non-big field fields of the entire table is used. (Found online)

My own understanding (not very clear, let's point it out ):

My understanding is similar to the last sentence, that is, the total bytes of fields of the non-large field type is greater than 64 K. What is the total bytes of this field? I should understand it as the total length of the field name. Non-large fields refer to those fields. I think they are varchar fields. Their names are more than 64 kB in total, and an error is reported when the storage space exceeds the retrieval size of mysql.

To put it simply, before the varchar version 5, the maximum number of characters stored in the varchar version is 255 (this is not a scientific query). After the version 5, the varchar is of a variable length, that is, the storage space occupied is divided according to the actual amount of data stored, for example, if there are 50 data in the varchar type, the storage space it occupies may be 51. One is the placeholder used to record how many characters are stored. (Maybe the field name occupies 64 k, but the length of the placeholder record in this field is too long, exceeding the 64 k value) That is to say, check whether the field type of varchar in your data table is too large.

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

Why is there so many varchar in my data table? Because it is actually long, it can save some storage space, for example, to varchar (300) however, I actually saved 10 characters in this field, which actually only occupies 11 character storage space, up to 300 characters.

My solution:

In the varchar (N) Table, set the field where N is set to less than 255 and change the attribute to tinytext. Tinytext always occupies 255 characters during storage. Is fixed, and the maximum number of characters that can be saved is only 255. Therefore, if the n value of varchar (N) is very small, it can be replaced by tinytext. If char is used, consider whether it can be used.

The above is the reason why Mysql reports Row size too large 65535 and the solution. I hope it will help you. If you have any questions, please leave a message, the editor will reply to you in a timely manner. Thank you very much for your support for the help House website!