Search code examples
mysqldatabasedatabase-designlimitrows

MySQL - How is the row size above 65535

I understand that the maximum row limit in mysql is 65535, which is equal to (2 ^ 16) - 1. I also understand that it is bad database design to have extremely long rows like that. However, this is my schema

CREATE TABLE mytable(
  a VARCHAR(20000),
  b VARCHAR(20000),
  c VARCHAR(20000),
  d VARCHAR(5535)
) CHARACTER SET=latin1;

This is the output I get

Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

Let's do the math again

20000 + 20000 + 20000 + 5535 = 65535

which is equal to and does not surpass the limit. For the record, the highest value for column d that works is 5526.

I do not understand where those additional 9 characters come from.

Solution

  • The size of VARCHAR is calculated like this:

    len + 1 bytes if column is 0 – 255 bytes, len + 2 bytes if column may require more than 255 bytes

    so 

    CREATE TABLE mytable(
  a VARCHAR(20000),       -- 20002
  b VARCHAR(20000),       -- 20002
  c VARCHAR(20000),       -- 20002
  d VARCHAR(5535)         --  5537
) CHARACTER SET=latin1;--    65543 !!!! 8 Bytes to much

    see this https://mariadb.com/kb/en/mariadb/data-type-storage-requirements/