mysql varchar innodb page size limit 8100 bytes
Regarding innodb, someone recently told me:
"the varchar content beyond 768 bytes is stored in supplemental 16K pages"
This is very interesting. If each varchar will be latin1, which I believe stores as 1byte per letter, would a single varchar(500) (<768 bytes) require an extra i/o as a varchar(1000) (>768 bytes) would?? (this question is to find out if all varchars or just big varchars are split into a separate page)
Is the 768 limit per varchar or for all varchars in the row added together? (for example, does this get optimized - varchar(300), varchar(300), varchar(300): [where each 开发者_如何学Pythonindividual varchar column is below 768 but together they are above 768 characters]?
I am confused about if the 768 limit relates to each individual varchar or all varchars in the row totaled (as in the question). Any clarification?
EDIT: Removed part about CHARS due to finding out about their limit of 255 max.
The documentation of the current MySQL does not mention this constraint. It actually gives the impression that VARCHAR is stored in the row itself, as the sum of the length of all VARCHARs together in a row cannot be more than 65k bytes.
It also mentions that storage is length+data, which is different from what you describe.
I did find a mention of a splitted storage in MySQL compression internals:
Tables created in previous versions of InnoDB use the “Antelope” file format, which supports only ROW_FORMAT=REDUNDANT and ROW_FORMAT=COMPACT. In these formats, InnoDB stores the first 768 bytes of BLOB, VARCHAR and TEXT columns in the clustered index record along with the primary key. The 768-byte prefix is followed by a 20-byte pointer to the overflow pages that contain the rest of the column value.
To me this suggests that each column is considered individually when determining whether that column needs an overflow.
This seems to contrast with newer versions of MySQL which seem to store VARCHAR quite differently. A VARCHAR in a newer version has a max length of 65k bytes, which seems to be stored in the record iself (no overflow), which results in the constraint that all columns together may not be longer than 65k byte.
a CHAR is very different. It's maximum length is 255, and it is stored in the row itself. So no CHAR(500) :)
The length of a CHAR column is fixed to the length that you declare when you create the table. The length can be any value from 0 to 255. When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed unless the PAD_CHAR_TO_FULL_LENGTH SQL mode is enabled.
精彩评论