10.4 Limit the number of columns and the size of rows in a table

Official address: http://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html

The hard rule in each table is that only 4,096 columns are allowed to be stored, and the effective maximum value may be less than the given table, and the exact limit depends on several interactions.

1. Each table (does not care about the storage engine) has a single row maximum value of 65535 bytes. The storage engine may place additional constraints on this limit to reduce the effective maximum row size.

The maximum row size limits the number of dimensions (possibly storage size), because the total length of all columns cannot exceed this size

For example, each UTF8 character requires 3 bytes.

So a char (255) UTF8 The column of the character set. The server must allocate 255 x 3 = 765 bytes for each value, so a table cannot contain 65535/765 = 85 such columns.

The storage of variable-length columns includes length and bytes. This is an evaluation of the row size.

For example, a varchar (255) UTF8 Character Set column requires two bytes to store the worth of length.

So each value can occupy 767 bytes.

The BLOB and TEXT fields are counted because their contents are stored in the remainder of the row, respectively.

The limit on the size of each row, from 1 to 4 + 8 bytes of value.

Declaring a column's null value can reduce the maximum number of allowable columns.

For MyISAM tables, the null column requires additional space to record whether their row value is null. Each null column requires an extra, rounded to the nearest byte

The maximum line length of a byte 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_flag = 1 is a static format table. Static tables use a bit representation in the row record, indicating whether the row has been removed from the identity bit.

Delete_flag 0 Dynamic Table because the flag identifies the dynamic row header that is stored.

For information on MyISAM table formats, see section 16.2.3, "storage format for MyISAM tables".

In the InnoDB table, is a null column or a non-null column with the same storage size. So the previous calculation does not apply.

The following statement creates the T1 table successfully because the columns require 32,765 + 2 bytes and 32,766 + 2 bytes,

His maximum row size is 65535 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 the used table type, not counting BLOBs, is 65535. Some columns to TEXT or BLOBs

Reducing column lengths from 65533 or less can create success.

2. A single storage engine may impose additional restrictions on the number of restricted table columns. For example:

(1) InnoDB allows up to 1000 columns.

(2) InnoDB limits the page size of the database page to 4kb,8kb, 16KB, 32KB, which is slightly less than half the row size. For a 64KB page size limit, the InnoDB row size is approximately 16000 bytes.

The row size limit differs for variable-length columns (VARBINARY, VARCHAR, BLOB, and TEXT).

(3) different INNODB storage formats (compressed, redundant) use different amounts of page header and tail data, which affects the amount of rows stored.

This article from the "Flying Ants" blog, declined to reprint!

10.4 Limit the number of columns and the size of rows in a table