Some limitations of the "MySQL" structure for the governor degree

The DDL changes that were developed today are again confusing, with more fields in the table, and the wish to change the existing two varchar (4000) fields to varchar (20000), I think innodb the storage of varchar is not the first 768 bytes to record the current row space, So the change will not have any problems, but the fact that the iron has given me a solid slap, direct error, now back down this error!

Simulation test:

CREATE TABLE `ttt` (      `id` DOUBLE ,      `select_type` VARCHAR (57),      `table` VARCHAR (192),      `type` VARCHAR (30),      `possible_keys` VARCHAR (22288),      `key` VARCHAR (192),      `key_len` VARCHAR (22288),      `ref` VARCHAR (3072),      `rows` DOUBLE ,      `Extra` VARCHAR (765)  ); 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

See the hint, the table of 2 varchar field length is set too long, need to change to the type of Text,blob, modified after the execution succeeded.

mysql> use test;  Database changed  mysql>   mysql> CREATE TABLE `ttt` (      -> `id` DOUBLE ,      -> `select_type` VARCHAR (57),      -> `table` VARCHAR (192),      -> `type` VARCHAR (30),      -> `possible_keys` TEXT,      -> `key` VARCHAR (192),      -> `key_len`  TEXT,      -> `ref` VARCHAR (3072),      -> `rows` DOUBLE ,      -> `Extra` VARCHAR (765)      -> );   Query OK, 0 rows affected (0.00 sec)

Doubt: varchar (n), is this n not the maximum of 65535? Why do I get an error when I set it to 12288? 12,288:65,535 is a lot smaller.

Officer Net: http://dev.mysql.com/doc/refman/5.6/en/column-count-limit.html:

Every table (regardless of storage engine) has a maximum row size of 65,535 bytes. Storage engines may place additional constraints on this limit, reducing the effective maximum row size.

Resolution:65,535 describes the bytes summation of fields that are non-large field types for the entire table.

To see the detailed analysis, we still have to look down.

Each table has a hard limit of 4,096 columns, but to a specific table is often less than this number, the exact limit depends on several interaction factors:

1. The maximum row size limit for each table (regardless of the storage engine) is 65,535 bytes. The storage engine may impose additional restrictions on this limit, reducing the maximum valid row size.

   • Depending on the row size limit, the number of columns also depends on the specific field length, for example, the UTF8 character requires three bytes of storage, so for char (255) CHARACTER set UTF8 column, the server must allocate 255x3 =765 bytes. Therefore, a table cannot contain columns that are more than 65,535/765=85.
   • Variable-length columns also consider the number of bytes that store the actual length of the column when evaluating the field size. For example, a VARCHAR (255) CHARACTER Set UTF8 column requires an additional two bytes to store the value length information, so the column requires up to 767 bytes of storage, in fact, the maximum can store 65533 bytes, the remaining two bytes store the length information.
   • The Blob and text columns are different from the varchar fields, and the column length information is independent of the President store and can reach 65535 bytes of real storage.
   • Declaring a null column can reduce the maximum number of columns allowed. For a myisam table, the null column requires additional space in the row to record whether its value is null. Each null column requires an extra bit, rounded up to the nearest byte.

   最大行长度计算如下：    row length = 1            + (sum of column lengths)            + (number of NULL columns + delete_flag + 7)/8            + (number of variable-length columns)

   For a static table, Delete_flag = 1, the static table identifies whether the row has been deleted by logging a bit on the row. Dynamic table when Delete_flag = 0, because the tag is stored in the dynamic beginning, the dynamic table can be judged according to Row_format, details refer to section 15.2.3, "MyISAM Table Storage Formats"

   For InnoDB tables, the null and NOT NULL column storage sizes are the same, so the above calculation does not apply.

   The following tests are not a problem, (32765 + 2 + 32766 + 2) bytes < 655535

   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)

   Because the null attribute requires additional storage space, it exceeds the maximum limit of 65535

   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 the used table type, not counting BLOBs, is 65535. You have to change somecolumns to TEXT or BLOBs

   The maximum limit of 65535 is exceeded because the field needs to occupy additional storage space in the line.

   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

3. In addition, some storage engines may impose additional restrictions on the number of columns in the table. For example:

   • InnoDB allows a single table up to 1000 columns
   • innodb limit row size to half of the database page, excluding Varbinary,varchar,blob or text columns. For more information, more impact is manifested by DML than DML. For more information limits on InnoDB Tables
   • InnoDB Different storage formats (compression, redundancy) use a different number of page headers and tails, which affects the length that can be used to store rows.

   • If InnoDB strict mode is disabled, the table is created with a redundant (redundant) or compact format, and if the column exceeds the maximum row size, it is also defined as a success, only to produce a warning:

      Code class= "Language-none" >|  Warning | 139 | Row size too large (> 8123). Changing some columns to TEXT or BLOB or using row_format=dynamic or row_format=compressed may help.
       In the current row format, the BLOB prefix of 768 bytes is stored inline.  

   • If InnoDB strict mode is disabled, the table is created with the dynamic or compressed (compressed) format, and if the column exceeds the maximum row size, the definition will be successful, and the error will be directly:

      Code class= "Language-none" >error 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB could help.
      In the current row format, the BLOB prefix of 0 bytes is stored inline.  

5. Each table has a. frm file that contains the table definition. The contents of the file definition also affect the maximum number of fields, as detailed in limits imposed by. frm file Structure

Reference:

• MySQL BUG: Error 1118 (42000): Row size too large. The maximum row size for the Used table type
• Limits on Table Column Count and Row Size
• Some limitations of the "MySQL" data length
• Limits imposed by. frm File Structure

Some limitations of the "MySQL" structure for the governor degree