Related Documentation Download this Manual
PDF (US Ltr) - 31.2Mb
PDF (A4) - 31.2Mb
PDF (RPM) - 30.4Mb
EPUB - 7.7Mb
HTML Download (TGZ) - 7.5Mb
HTML Download (Zip) - 7.6Mb
HTML Download (RPM) - 6.5Mb
Eclipse Doc Plugin (TGZ) - 8.3Mb
Eclipse Doc Plugin (Zip) - 10.1Mb
Man Pages (TGZ) - 183.8Kb
Man Pages (Zip) - 295.2Kb
Info (Gzip) - 2.9Mb
Info (Zip) - 2.9Mb
Excerpts from this Manual

MySQL 5.6 Reference Manual  /  ...  /  Limits on Table Column Count and Row Size

C.10.4 Limits on Table Column Count and Row Size

Column Count Limits

MySQL has hard limit of 4096 columns per table, but the effective maximum may be less for a given table. The exact column limit depends on several factors:

Row Size Limits

The maximum row size for a given table is determined by several factors:

Row Size Limit Examples
  • The MySQL maximum row size limit of 65,535 bytes is demonstrated in the following InnoDB and MyISAM examples. The limit is enforced regardless of storage engine, even though the storage engine may be capable of supporting larger rows.

    mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
           c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
           f VARCHAR(10000), g VARCHAR(6000)) ENGINE=InnoDB CHARACTER SET latin1;
    ERROR 1118 (42000): 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
    
    mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
           c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
           f VARCHAR(10000), g VARCHAR(6000)) ENGINE=MyISAM CHARACTER SET latin1;
    ERROR 1118 (42000): 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
    

    In the following MyISAM example, changing a column to TEXT avoids the 65,535-byte row size limit and permits the operation to succeed because BLOB and TEXT columns only contribute 9 to 12 bytes toward the row size.

    mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
           c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
           f VARCHAR(10000), g TEXT(6000)) ENGINE=MyISAM CHARACTER SET latin1;
    Query OK, 0 rows affected (0.02 sec)
    

    The operation succeeds for an InnoDB table because changing a column to TEXT avoids the MySQL 65,535-byte row size limit, and InnoDB off-page storage of variable-length columns avoids the InnoDB row size limit.

    mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
           c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
           f VARCHAR(10000), g TEXT(6000)) ENGINE=InnoDB CHARACTER SET latin1;
    Query OK, 0 rows affected (0.02 sec)
    
  • Storage for variable-length columns includes length bytes, which are counted toward the row size. For example, a VARCHAR(255) CHARACTER SET utf8mb3 column takes two bytes to store the length of the value, so each value can take up to 767 bytes.

    The statement to create table t1 succeeds because the columns require 32,765 + 2 bytes and 32,766 + 2 bytes, which falls within the maximum row size of 65,535 bytes:

    mysql> CREATE TABLE t1
           (c1 VARCHAR(32765) NOT NULL, c2 VARCHAR(32766) NOT NULL)
           ENGINE = InnoDB CHARACTER SET latin1;
    Query OK, 0 rows affected (0.02 sec)
    

    The statement to create table t2 fails because, although the column length is within the maximum length of 65,535 bytes, two additional bytes are required to record the length, which causes the row size to exceed 65,535 bytes:

    mysql> CREATE TABLE t2
           (c1 VARCHAR(65535) NOT NULL)
           ENGINE = InnoDB CHARACTER SET latin1;
    ERROR 1118 (42000): 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
    

    Reducing the column length to 65,533 or less permits the statement to succeed.

    mysql> CREATE TABLE t2
           (c1 VARCHAR(65533) NOT NULL)
           ENGINE = InnoDB CHARACTER SET latin1;
    Query OK, 0 rows affected (0.01 sec)
    
  • For MyISAM tables, NULL columns require additional space in the row to record whether their values are NULL. Each NULL column takes one bit extra, rounded up to the nearest byte.

    The statement to create table t3 fails because MyISAM requires space for NULL columns in addition to the space required for variable-length column length bytes, causing the row size to exceed 65,535 bytes:

    mysql> CREATE TABLE t3
           (c1 VARCHAR(32765) NULL, c2 VARCHAR(32766) NULL)
           ENGINE = MyISAM CHARACTER SET latin1;
    ERROR 1118 (42000): 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
    

    For information about InnoDB NULL column storage, see Section 14.8.3, “Physical Row Structure of InnoDB Tables”.

  • InnoDB restricts row size (for data stored locally within the database page) to slightly less than half a database page. For example, the maximum row size is slightly less than 8KB for the default 16KB InnoDB page size, which is defined by the innodb_page_size configuration option.

    The statement to create table t4 fails because the defined columns exceed the row size limit for a 16KB InnoDB page.

    Note

    innodb_strict_mode is enabled in the following example to ensure that InnoDB returns an error if the defined columns exceed the InnoDB row size limit. When innodb_strict_mode is disabled (the default), creating a table that uses REDUNDANT or COMPACT row format succeeds with a warning if the InnoDB row size limit is exceeded.

    DYNAMIC and COMPRESSED row formats are more restrictive in this regard. Creating a table that uses DYNAMIC or COMRESSED row format fails with an error if the InnoDB row size limit is exceeded, regardless of the innodb_strict_mode setting.

    mysql> SET SESSION innodb_strict_mode=1;  
    mysql> CREATE TABLE t4 (
           c1 CHAR(255),c2 CHAR(255),c3 CHAR(255),
           c4 CHAR(255),c5 CHAR(255),c6 CHAR(255),
           c7 CHAR(255),c8 CHAR(255),c9 CHAR(255),
           c10 CHAR(255),c11 CHAR(255),c12 CHAR(255),
           c13 CHAR(255),c14 CHAR(255),c15 CHAR(255),
           c16 CHAR(255),c17 CHAR(255),c18 CHAR(255),
           c19 CHAR(255),c20 CHAR(255),c21 CHAR(255),
           c22 CHAR(255),c23 CHAR(255),c24 CHAR(255),
           c25 CHAR(255),c26 CHAR(255),c27 CHAR(255),
           c28 CHAR(255),c29 CHAR(255),c30 CHAR(255),
           c31 CHAR(255),c32 CHAR(255),c33 CHAR(255)
           ) ENGINE=InnoDB ROW_FORMAT=COMPACT DEFAULT CHARSET latin1;
    ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB or using 
    ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 
    bytes is stored inline.
    

User Comments
Sign Up Login You must be logged in to post a comment.