VARCHAR（N）CHAR（N）
两则之间最大区别是在于前者是可变长度，后者是定长，面试中经常问及N代表什么意思，，
The CHAR and VARCHAR types are declared with a length that indicates the maximum number of characters you want to store. For example, CHAR(30) can hold up to 30 characters.
自己理解为可以存储的字符数，如char(30)可以存储30个字符
In contrast to CHAR, VARCHAR values are stored as a 1-byte or 2-byte length prefix plus data. The length prefix indicates the number of bytes in the value. A column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes. 这个存储的时候需要额外的1或者2byte，最大字节255
The following table illustrates the differences between CHAR and VARCHAR by showing the result of storing various string values into CHAR(4) and VARCHAR(4) columns (assuming that the column uses a single-byte character set such as latin1).
以上这个存储的字节数是以latin1为标准举例说明的
|
1
2
3
4
5
6
7
8
9
|
There is a hard limit of 4096 columns per table, but the effective 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, reducing the effective 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, utf8 characters require up to three
bytes per character, so for a CHAR(255) CHARACTER SET utf8 column, the server must allocate
255 × 3 = 765 bytes per value. Consequently, a table cannot contain more than
65,535 / 765 = 85 such columns.
|
一个表最大栏位可以有4096个，每个记录可以最大有65535字节存储。
举例char(255) uft8,每个栏位可以765字节，可以有85个栏位。
|
1
2
3
|
Storage
for
var
iable-length columns includes length bytes, which are assessed against
the row size.For example, a VARCHAR(
255
) CHARACTER SET utf8 column takes two bytes to store the length
of the value, so
each
value can take up to
767
bytes.
|
但是对于varchar类型需要有额外的2byte存储，所以每个栏位需要767byte。
|
1
2
3
4
5
6
|
BLOB and TEXT columns 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 columns NULL can reduce the maximum number of columns permitted. 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 maximum row length
in
bytes can be calculated
as
follows:每行最大byte计算方法
|
|
1
2
3
4
5
6
7
|
row length =
1
+ (sum of column lengths)
+ (number of NULL columns + delete_flag +
7
)/
8
+ (number of
var
iable-length columns)
delete_flag
is
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_flag
is
0
for
dynamic
tables because the flag
is
stored
in
the
dynamic
row header.
|
以下是关于Table max columns 及 row max bytes解释说明
These calculations do not apply for InnoDB tables. Storage size is the same for NULL and NOT
NULL columns.
The following 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:
Table Column-Count and Row-Size Limits
3944
|
1
2
3
4
|
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 table t2 fails because the columns are NULL and MyISAM requires
additional space that causes the row size to exceed 65,535 bytes:
|
1
2
3
4
5
6
|
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 some
columns to TEXT or BLOBs
|
The following statement to create table t3 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:
|
1
2
3
4
5
6
|
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
. You have to change some
columns to TEXT or BLOBs
|
Reducing the column length to 65,533 or less permits the statement to succeed.
Each table has an .frm file 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 the .frm file 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_length is space needed for “screens.” This is related to MySQL's Unireg heritage.
create_fields.elements is the number of columns.
FCOMP is 17.
n_length is the total length of all column names, including one byte per name as a separator.
int_length is related to the list of values for ENUM and SET columns.
com_length is the total length of column and table comments.
Thus, using long column names can reduce the maximum number of columns, as can the inclusion of ENUM or SET columns, or use of column, index, or table comments.
Individual storage engines might impose additional restrictions that limit table column count.
Examples:
InnoDB permits up to 1000 columns.
InnoDB restricts row size to something less than half a database page (approximately 8000
bytes), not including VARBINARY, VARCHAR, BLOB, or TEXT columns.
Windows Platform Limitations
3945
Different InnoDB storage formats (COMPRESSED, REDUNDANT) use different amounts of page
header and trailer data, which affects the amount of storage available for rows.
本文转自 aklaus 51CTO博客，原文链接:http://blog.51cto.com/aklaus/1715295
