MySQL reports Row size too large 65535 reason and Solution

Source: Internet
Author: User

MySQL reports Row size too large 65535 reason and Solution

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

This article permanently updates the link address:

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.