Possible Duplicate:
Importance of varchar length in MySQL table可能重复:varchar长度在MySQL表中的重要性
When using VARCHAR (assuming this is the correct data type for a short string) does the size matter? If I set it to 20 characters, will that take up less space or be faster than 255 characters?
当使用VARCHAR(假设这是短字符串的正确数据类型)时，大小是否重要?如果我将它设置为20个字符，那么它会占用更少的空间，还是会比255个字符快?
8 个解决方案
#1
10
In general, for a VARCHAR field, the amount of data stored in each field determines its footprint on the disk rather than the maximum size (unlike a CHAR field which always has the same footprint).
通常，对于VARCHAR字段，每个字段中存储的数据量决定了它在磁盘上的占用空间，而不是最大大小(与始终具有相同占用空间的CHAR字段不同)。
There is an upper limit on the total data stored within all fields of an index of 900 bytes (900 byte index size limit in character length).
在索引为900字节的所有字段中存储的总数据有一个上限(字符长度为900字节的索引大小限制)。
The larger you make the field, the more likely people will try to use for purposes other than what you intended - and the greater the screen real-estate required to show the value - so its good practice to try to pick the right size, rather than assuming that if you make it as large as possible it will save you having to revisit the design.
更大的你,越多的人会尝试使用你目的以外的任何其他目的,屏幕房地产需要显示的值就越大,所以它的良好的实践来选择正确的大小,而不是假设如果你让它尽可能大,它将让你不得不重新设计。
#2
9
Yes, is matter when you indexing multiple columns.
是的，多列索引很重要。
Prefixes can be up to 1000 bytes long (767 bytes for InnoDB tables). Note that prefix limits are measured in bytes, whereas the prefix length in CREATE TABLE statements is interpreted as number of characters. Be sure to take this into account when specifying a prefix length for a column that uses a multi-byte character set.
前缀最长可达1000字节(InnoDB表的767字节)。注意，前缀限制是以字节为单位度量的，而CREATE TABLE语句中的前缀长度被解释为字符的数量。在为使用多字节字符集的列指定前缀长度时，一定要考虑到这一点。
source : http://dev.mysql.com/doc/refman/5.0/en/column-indexes.html
来源:http://dev.mysql.com/doc/refman/5.0/en/column-indexes.html
In a latin1 collation, you can only specify up 3 columns of varchar(255).
While can specify up to 50 columns for varchar(20)
在latin1排序中，只能指定varchar(255)的3列。可以指定最多50列varchar(20)
In-directly, without proper index, it will slow-down query speed
直接地，没有适当的索引，它将减慢查询速度
In terms of storage, it does not make difference,
as varchar stand for variable-length strings
在存储方面，它没有区别，因为varchar表示变长字符串
#3
5
The actual differences are:
实际的差异是:
-
TINYTEXT and other TEXT fields are stored separately from in-memory row inside MySQL heap, whereas VARCHAR() fields add up to 64k limit (so you can have more than 64k in TINYTEXTs, whereas you won't with VARCHAR).
TINYTEXT和其他文本字段分别存储在MySQL堆中的内存行中，而VARCHAR()字段加起来是64k限制(因此TINYTEXTs中可以有超过64k的字段，而VARCHAR中不能)。
-
TINYTEXT and other 'blob-like' fields will force SQL layer (MySQL) to use on-disk temporary tables whenever they are used, whereas VARCHAR will be still sorted 'in memory' (though will be converted to CHAR for the full width).
TINYTEXT和其他类似blob的字段将强制SQL层(MySQL)在使用时使用磁盘上的临时表，而VARCHAR将仍然在“内存中”进行排序(不过将被转换为CHAR，以获得完整的宽度)。
-
InnoDB internally doesn't really care whether it is tinytext or varchar. It is very easy to verify, create two tables, one with VARCHAR(255), another with TINYINT, and insert a record to both. They both will take single 16k page - whereas if overflow pages are used, TINYTEXT table should show up as taking at least 32k in 'SHOW TABLE STATUS'.
InnoDB内部并不关心它是tinytext还是varchar。很容易验证，创建两个表，一个带有VARCHAR(255)，另一个带有TINYINT，并向两个表插入一条记录。它们都将占用单个16k页——而如果使用溢出页，TINYTEXT表在“show table STATUS”中应该显示为至少占用32k页。
I usually prefer VARCHAR(255) - they don't cause too much of heap fragmentation for single row, and can be treated as single 64k object in memory inside MySQL. On InnoDB size differences are negligible.
我通常更喜欢VARCHAR(255)—它们不会对单行造成太多的堆碎片，并且可以在MySQL内存中作为单个64k对象来处理。在InnoDB上，大小差异可以忽略不计。
#4
4
In the documentation of MySQL: http://dev.mysql.com/doc/refman/5.0/en/char.html
在MySQL文档中:http://dev.mysql.com/doc/refman/5.0/en/char.html
You have a table that indicates the bytes of a VARCHAR(4) (vs a CHAR(4)).
您有一个表，它指示VARCHAR(4)的字节(vs . CHAR(4)))。
A simple VARCHAR(4) without string, only 1 byte. Then, a simple VARCHAR(255) without string is 1byte. A VARCHAR(4) with 'ab' is 3 bytes, and a VARCHAR(255) with 'ab' is 3 bytes. It's the same, but with the lenght limit :)
一个简单的VARCHAR(4)，没有字符串，只有1字节。然后，一个没有字符串的简单VARCHAR(255)是1字节。带“ab”的VARCHAR(4)为3字节，带“ab”的VARCHAR(255)为3字节。是一样的，但是有极限
#5
2
This will have no effect on performance. In this case the constraint merely helps ensure data integrity.
这对性能没有影响。在这种情况下，约束仅仅有助于确保数据的完整性。
#7
0
If you set it to 20, it will save only the first 20 characters. So yes, it will take up less space than 255 characters :).
如果您将它设置为20，它将只保存前20个字符。因此，它所占的空间将小于255个字符:)。
#8
0
The required storage space for VARCHAR is as follows:
VARCHAR所需的存储空间如下:
VARCHAR(L),VARBINARY(L)—L+ 1 bytes if column values require 0 – 255 bytes,L+ 2 bytes if values may require more than 255 bytesVARCHAR(L)， VARBINARY(L) - L + 1个字节(如果列值需要0 - 255个字节)，如果值需要大于255个字节，则为L + 2个字节
So VARCHAR does only require the space for the string plus one or two additional bytes for the length of the string.
所以VARCHAR只需要字符串的空间加上字符串长度的一个或两个额外的字节。
#1
10
In general, for a VARCHAR field, the amount of data stored in each field determines its footprint on the disk rather than the maximum size (unlike a CHAR field which always has the same footprint).
通常，对于VARCHAR字段，每个字段中存储的数据量决定了它在磁盘上的占用空间，而不是最大大小(与始终具有相同占用空间的CHAR字段不同)。
There is an upper limit on the total data stored within all fields of an index of 900 bytes (900 byte index size limit in character length).
在索引为900字节的所有字段中存储的总数据有一个上限(字符长度为900字节的索引大小限制)。
The larger you make the field, the more likely people will try to use for purposes other than what you intended - and the greater the screen real-estate required to show the value - so its good practice to try to pick the right size, rather than assuming that if you make it as large as possible it will save you having to revisit the design.
更大的你,越多的人会尝试使用你目的以外的任何其他目的,屏幕房地产需要显示的值就越大,所以它的良好的实践来选择正确的大小,而不是假设如果你让它尽可能大,它将让你不得不重新设计。
#2
9
Yes, is matter when you indexing multiple columns.
是的，多列索引很重要。
Prefixes can be up to 1000 bytes long (767 bytes for InnoDB tables). Note that prefix limits are measured in bytes, whereas the prefix length in CREATE TABLE statements is interpreted as number of characters. Be sure to take this into account when specifying a prefix length for a column that uses a multi-byte character set.
前缀最长可达1000字节(InnoDB表的767字节)。注意，前缀限制是以字节为单位度量的，而CREATE TABLE语句中的前缀长度被解释为字符的数量。在为使用多字节字符集的列指定前缀长度时，一定要考虑到这一点。
source : http://dev.mysql.com/doc/refman/5.0/en/column-indexes.html
来源:http://dev.mysql.com/doc/refman/5.0/en/column-indexes.html
In a latin1 collation, you can only specify up 3 columns of varchar(255).
While can specify up to 50 columns for varchar(20)
在latin1排序中，只能指定varchar(255)的3列。可以指定最多50列varchar(20)
In-directly, without proper index, it will slow-down query speed
直接地，没有适当的索引，它将减慢查询速度
In terms of storage, it does not make difference,
as varchar stand for variable-length strings
在存储方面，它没有区别，因为varchar表示变长字符串
#3
5
The actual differences are:
实际的差异是:
-
TINYTEXT and other TEXT fields are stored separately from in-memory row inside MySQL heap, whereas VARCHAR() fields add up to 64k limit (so you can have more than 64k in TINYTEXTs, whereas you won't with VARCHAR).
TINYTEXT和其他文本字段分别存储在MySQL堆中的内存行中，而VARCHAR()字段加起来是64k限制(因此TINYTEXTs中可以有超过64k的字段，而VARCHAR中不能)。
-
TINYTEXT and other 'blob-like' fields will force SQL layer (MySQL) to use on-disk temporary tables whenever they are used, whereas VARCHAR will be still sorted 'in memory' (though will be converted to CHAR for the full width).
TINYTEXT和其他类似blob的字段将强制SQL层(MySQL)在使用时使用磁盘上的临时表，而VARCHAR将仍然在“内存中”进行排序(不过将被转换为CHAR，以获得完整的宽度)。
-
InnoDB internally doesn't really care whether it is tinytext or varchar. It is very easy to verify, create two tables, one with VARCHAR(255), another with TINYINT, and insert a record to both. They both will take single 16k page - whereas if overflow pages are used, TINYTEXT table should show up as taking at least 32k in 'SHOW TABLE STATUS'.
InnoDB内部并不关心它是tinytext还是varchar。很容易验证，创建两个表，一个带有VARCHAR(255)，另一个带有TINYINT，并向两个表插入一条记录。它们都将占用单个16k页——而如果使用溢出页，TINYTEXT表在“show table STATUS”中应该显示为至少占用32k页。
I usually prefer VARCHAR(255) - they don't cause too much of heap fragmentation for single row, and can be treated as single 64k object in memory inside MySQL. On InnoDB size differences are negligible.
我通常更喜欢VARCHAR(255)—它们不会对单行造成太多的堆碎片，并且可以在MySQL内存中作为单个64k对象来处理。在InnoDB上，大小差异可以忽略不计。
#4
4
In the documentation of MySQL: http://dev.mysql.com/doc/refman/5.0/en/char.html
在MySQL文档中:http://dev.mysql.com/doc/refman/5.0/en/char.html
You have a table that indicates the bytes of a VARCHAR(4) (vs a CHAR(4)).
您有一个表，它指示VARCHAR(4)的字节(vs . CHAR(4)))。
A simple VARCHAR(4) without string, only 1 byte. Then, a simple VARCHAR(255) without string is 1byte. A VARCHAR(4) with 'ab' is 3 bytes, and a VARCHAR(255) with 'ab' is 3 bytes. It's the same, but with the lenght limit :)
一个简单的VARCHAR(4)，没有字符串，只有1字节。然后，一个没有字符串的简单VARCHAR(255)是1字节。带“ab”的VARCHAR(4)为3字节，带“ab”的VARCHAR(255)为3字节。是一样的，但是有极限
#5
2
This will have no effect on performance. In this case the constraint merely helps ensure data integrity.
这对性能没有影响。在这种情况下，约束仅仅有助于确保数据的完整性。
#6
#7
0
If you set it to 20, it will save only the first 20 characters. So yes, it will take up less space than 255 characters :).
如果您将它设置为20，它将只保存前20个字符。因此，它所占的空间将小于255个字符:)。
#8
0
The required storage space for VARCHAR is as follows:
VARCHAR所需的存储空间如下:
VARCHAR(L),VARBINARY(L)—L+ 1 bytes if column values require 0 – 255 bytes,L+ 2 bytes if values may require more than 255 bytesVARCHAR(L)， VARBINARY(L) - L + 1个字节(如果列值需要0 - 255个字节)，如果值需要大于255个字节，则为L + 2个字节
So VARCHAR does only require the space for the string plus one or two additional bytes for the length of the string.
所以VARCHAR只需要字符串的空间加上字符串长度的一个或两个额外的字节。