mysql varchar长度问题
[NATIONAL] VARCHAR(
M
) [CHARACTER SET charset_name
] [COLLATE collation_name
]
A variable-length string. M
represents the maximum column length in characters. The range of M
is 0 to 65,535. The effective maximum length of a VARCHAR
is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used. For example, utf8
characters can require up to three bytes per character, so a VARCHAR
column that uses the utf8
character set can be declared to be a maximum of 21,844 characters.
mysql中行的最大size(maximum row size )是65535bytes。如果某表只有一列,且该列为varchar(n) not null,则最大可以存放65533bytes,为什么?因为varchar是动态列,动态列需要花2bytes来存放列的长度;如果该列为varchar(n),即允许为null,则最大可以存放65532bytes,为什么?因为需要花1bit来标记该字段是否为Null.而这个character数是不可以设置为小数的,所以要减去1byte。
下面是mf的原文:from:http://dev.mysql.com/doc/refman/5.0/en/column-count-limit.html
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, listed in the following discussion.
-
Every table has a maximum row size of 65,535 bytes. This maximum applies to all storage engines, but a given engine might have additional constraints that result in a lower effective maximum row size.
The maximum row size constrains the number of columns because the total width of all columns cannot exceed this size. For example,
utf8
characters require up to three bytes per character, so for aCHAR(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.Storage for variable-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.BLOB
andTEXT
columns count from one to four plus eight bytes each toward the row-size limit because their contents are stored separately.Declaring columns
NULL
can reduce the maximum number of columns allowed.NULL
columns require additional space in the row to record whether their values areNULL
.For
MyISAM
tables, eachNULL
column takes one bit extra(注意是bit), 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_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.These calculations do not apply for
InnoDB
tables, for which storage size is no different forNULL
columns than forNOT 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:mysql>
CREATE TABLE t1
->(c1 VARCHAR(32765) NOT NULL, c2 VARCHAR(32766) NOT NULL);
Query OK, 0 rows affected (0.01 sec)The following statement to create table
t2
fails because the columns areNULL
and require additional space that causes the row size to exceed 65,535 bytes:mysql>
CREATE TABLE t2
->(c1 VARCHAR(32765) NULL, c2 VARCHAR(32766) NULL);
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