The number of columns added by MySQL exceeds a certain number, so the problem of adding columns is not allowed.

E.7.4. table column-count and row-size limits

There is a hard limit of 4096 columns per table, but the specified tive maximum may be less for a given table. The exact limit depends on several interacting factors.

• Every table (regardless of storage engine) has a maximum row size of 65,535 bytes. storage engines may place additional constraints on this limit, which reduces the specified tive maximum row size.

  The maximum row size constrains the number (and possibly size) of columns because the total length of all columns cannot exceed this size. For example,utf8Characters require up to three bytes per character, so forCHAR(255) CHARACTER SET utf8Column, the server must allocate 255 × 3 = 765 bytes per value. Consequently, a table cannot contain more than 65,535/765 = 85 such columns.

  Storage for variable-length columns primary des length bytes, which are assessed against the row size. For example,VARCHAR(255) CHARACTER SET utf8Column takes two bytes to store the length of the value, so each value can take up to 767 bytes.

  Text types "href =" http://dev.mysql.com/doc/refman/5.0/en/blob.html ">BLOBAnd text types "href =" http://dev.mysql.com/doc/refman/5.0/en/blob.html ">TEXTColumns count from one to four plus eight bytes each toward the row-size limit because their contents are stored separately from the rest of the row.

  Declaring ColumnsNULLCan reduce the maximum number of columns permitted.MyISAMTables,NULLColumns require additional space in the row to record whether their values areNULL. EachNULLColumn takes one bit extra, rounded up to the nearest byte. The maximum row length in bytes can be calculated as follows:

  row length = 1             + (sum of column lengths)             + (number of NULL columns + delete_flag + 7)/8             + (number of variable-length columns)

  delete_flagIs 1 for tables with static row format. Static tables use a bit in the row record for a flag that indicates whether the row has been deleted.delete_flagIs 0 for Dynamic tables because the flag is stored in the dynamic row header. For information aboutMyISAMTable formats, see section 14.1.3,"MyISAMTable storage formats ".

  These calculations do not applyInnoDBTables. storage size is the sameNULLAndNOT NULLColumns.

  The following statement to Create Tablet1Succeeds 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 = MyISAM CHARACTER SET latin1;Query OK, 0 rows affected (0.02 sec)

  The following statement to Create Tablet2Fails because the columns areNULLAndMyISAMRequires additional space that causes the row size to exceed 65,535 bytes:

  mysql> CREATE TABLE t2    -> (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 theused table type, not counting BLOBs, is 65535. You have to change somecolumns to TEXT or BLOBs

  The following statement to Create Tablet3Fails 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 t3    -> (c1 VARCHAR(65535) NOT NULL)    -> ENGINE = MyISAM CHARACTER SET latin1;ERROR 1118 (42000): Row size too large. The maximum row size for theused table type, not counting BLOBs, is 65535. You have to change somecolumns to TEXT or BLOBs

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

• Each table has.frmFile that contains the table definition. The server uses the following expression to check some of the table information stored in the file against an upper limit of 64kb:

  if (info_length+(ulong) create_fields.elements*FCOMP+288+    n_length+int_length+com_length > 65535L || int_count > 255)

  The portion of the information stored in.frmFile that is checked against the expression cannot grow beyond the 64kb limit, so if the table definition reaches this size, no more columns can be added.

  The relevant factors in the expression are:

  • info_lengthIs space needed for "screens." This is related to MySQL's unireg heritage.

  • create_fields.elementsIs the number of columns.

  • FCOMPIs 17.

  • n_lengthIs the total length of all column names, including one byte per name as a separator.

  • int_lengthIs related to the list of valuesENUMAndSETColumns.

  • com_lengthIs the total length of column and Table comments.

  Thus, using long column names can reduce the maximum number of columns, as can the impactENUMOrSETColumns, or use of column or table comments.

• Individual storage engines might impose additional restrictions that limit table column count. Examples:

  • InnoDBPermits up to 1000 columns.

  • InnoDBRestricts row size to something less than half a database page (approximately 8000 bytes), not includingVARBINARY,VARCHAR, Text types "href =" http://dev.mysql.com/doc/refman/5.0/en/blob.html ">BLOB, Or text types "href =" http://dev.mysql.com/doc/refman/5.0/en/blob.html ">TEXTColumns.

  • DifferentInnoDBStorage formats (COMPRESSED,REDUNDANT) Use different amounts of page header and trailer data, which affects the amount of storage available for rows.