MySQL newspaper row size too large 65535 reasons and solutions

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

When a table plugin to MySQL is a field type of text, or when a field type of text is modified, the above error is reported. In fact, I understand the reason for this mistake is not very deep, give some of the explanations I found

The main idea is that the data table has a set length of 64K field index, when the table field (do not know the name of the field or what) can not exceed this length, 65,535 is for the whole table of non-large fields of the type of the bytes summation. (Online Search)

My own understanding (not very clear, everyone points out):

I understand that the last sentence to follow the meaning of the same, that is, the number of non-large field type of the bytes sum of more than 64K, the sum of the bytes of this field is what? I still think of the total sum of the names of the fields as he understood them. Non-large field refers to those fields, I think is the field of the varchar field type, their name sum greater than 64K, beyond the MySQL retrieval size storage space will be error.

varchar Let me just say that, in the old version 5 before the maximum number of varchar storage characters is 255 (the argument is not scientific interested in self-check), in 5 after the varchar is variable length, that is, according to the actual storage of data to partition the amount of storage space occupied, For example, there are 50 data in the varchar type, so it may occupy 51 of the storage space, and there is a placeholder for the number of characters that are stored in the record. (Perhaps not the field name occupies 64k, but the record placeholder length in this field is too much, more than the value of 64k) that is to say, you see whether your data table with varchar field type too much.

It is indeed varchar used too much, how to do it, in fact, the above error statement has a description, you can convert the property to text or blobs. You can try it yourself.

Why do I have so many varchar on my data sheet, because he is the actual length of the feeling can save a bit of storage space, such as to varchar (300) but I actually stored in this field 10 words, it is actually only occupy 11 characters storage space, up to 300 characters.

My workaround:

Set the field in the table in varchar (n) to a property that is less than 255, and modify the property to Tinytext. The tinytext always occupies 255 characters when stored. Is fixed, the maximum number of characters that can be saved is only 255. So if varchar (n) has a very small n value, it can be replaced with Tinytext. Char This everybody think about can use it.

MySQL newspaper row size too large 65535 reasons and solutions