Mysql – Row size limits in Mysql w.r.t to TEXT and BLOB columns

innodbMySQLmysql-5.7size;

There is a well-known limitation in Mysql for row size which 65,535 bytes.
While going through the docs,

I have found that

BLOB and TEXT columns only contribute 9 to 12 bytes toward the row
size limit because their contents are stored separately from the rest
of the row.

I did go through the MySQL documentation but unable to understand when a TEXT column will contribute 9 bytes and when it will contribute 12 bytes. Can anyone explain how to interpret the statement?

Best Answer

It is also stored the length of the data, that is why it is 9 - 12 , if you want it exactly.

https://github.com/jeremycole/mysql/blob/master/storage/innobase/include/btr0cur.h#L762

/** The reference in a field for which data is stored on a different page.
The reference is at the end of the 'locally' stored part of the field.
'Locally' means storage in the index record.
We store locally a long enough prefix of each column so that we can determine
the ordering parts of each index record without looking into the externally
stored part. */
/*-------------------------------------- @{ */
#define BTR_EXTERN_SPACE_ID     0   /*!< space id where stored */
#define BTR_EXTERN_PAGE_NO      4   /*!< page no where stored */
#define BTR_EXTERN_OFFSET       8   /*!< offset of BLOB header
                        on that page */
#define BTR_EXTERN_LEN          12  /*!< 8 bytes containing the
                        length of the externally
                        stored part of the BLOB.
                        The 2 highest bits are
                        reserved to the flags below. */
/*-------------------------------------- @} */
Related Question