How does MySQL store a varchar field? Can I assume that the following pattern represents sensible storage sizes :
MySQL如何存储varchar字段?我是否可以假设以下模式表示合理的存储大小:
1,2,4,8,16,32,64,128,255 (max)
1、2、4、8、16、32、64128255(最大)
A clarification via example. Lets say I have a varchar field of 20 characters. Does MySQL when creating this field, basically reserve space for 32 bytes(not sure if they are bytes or not) but only allow 20 to be entered?
通过例子说明。假设我有一个20个字符的varchar字段。在创建这个字段时，MySQL是否基本上保留32字节的空间(不确定是否为字节)，而只允许输入20字节?
I guess I am worried about optimising disk space for a massive table.
我想我担心的是为一个巨大的表优化磁盘空间。
2 个解决方案
#1
47
To answer the question, on disk MySql uses 1 + the size that is used in the field to store the data (so if the column was declared varchar(45), and the field was "FooBar" it would use 7 bytes on disk, unless of course you where using a multibyte character set, where it would be using 14 bytes). So, however you declare your columns, it wont make a difference on the storage end (you stated you are worried about disk optimization for a massive table). However, it does make a difference in queries, as VARCHAR's are converted to CHAR's when MySql makes a temporary table (SORT, ORDER, etc) and the more records you can fit into a single page, the less memory and faster your table scans will be.
回答这个问题,磁盘上的MySql使用1 +的大小使用的字段来存储数据(如果列被宣布为varchar(45),字段“FooBar”,将用7字节在磁盘上,除非你使用多字节字符集,它将使用14个字节)。因此，无论您如何声明列，它都不会对存储端产生影响(您表示您担心大型表的磁盘优化)。但是，它确实对查询有影响，因为当MySql创建一个临时表(排序、排序等)时，VARCHAR会被转换为CHAR，并且您可以装入一个页面的记录越多，那么内存就越少，表扫描速度也就越快。
#2
22
MySQL stores a varchar field as a variable length record, with either a one-byte or a two-byte prefix to indicate the record size.
MySQL将varchar字段存储为可变长度记录，其中一个字节或两个字节的前缀表示记录大小。
Having a pattern of storage sizes doesn't really make any difference to how MySQL will function when dealing with variable length record storage. The length specified in a varchar(x) declaration will simply determine the maximum length of the data that can be stored. Basically, a varchar(16) is no different disk-wise than a varchar(128).
拥有一个存储大小的模式对于MySQL在处理可变长度记录存储时的功能并没有什么影响。varchar(x)声明中指定的长度将简单地确定可以存储的数据的最大长度。基本上，varchar(16)和varchar(128)没有什么不同。
This manual page has a more detailed explanation.
本手册页有更详细的说明。
Edit: With regards to your updated question, the answer is still the same. A varchar field will only use up as much space on disk as the data you store in it (plus a one or two byte overhead). So it doesn't matter if you have a varchar(16) or a varchar(128), if you store a 10-character string in it, you're only going to use 10 bytes (plus 1 or 2) of disk space.
编辑:关于你的问题，答案仍然是一样的。varchar字段将只占用磁盘上与您在其中存储的数据相同的空间(加上一个或两个字节的开销)。所以，如果您有一个varchar(16)或varchar(128)，如果您在其中存储10个字符的字符串，那么您只需要使用10个字节(加上1或2)的磁盘空间。
#1
47
To answer the question, on disk MySql uses 1 + the size that is used in the field to store the data (so if the column was declared varchar(45), and the field was "FooBar" it would use 7 bytes on disk, unless of course you where using a multibyte character set, where it would be using 14 bytes). So, however you declare your columns, it wont make a difference on the storage end (you stated you are worried about disk optimization for a massive table). However, it does make a difference in queries, as VARCHAR's are converted to CHAR's when MySql makes a temporary table (SORT, ORDER, etc) and the more records you can fit into a single page, the less memory and faster your table scans will be.
回答这个问题,磁盘上的MySql使用1 +的大小使用的字段来存储数据(如果列被宣布为varchar(45),字段“FooBar”,将用7字节在磁盘上,除非你使用多字节字符集,它将使用14个字节)。因此，无论您如何声明列，它都不会对存储端产生影响(您表示您担心大型表的磁盘优化)。但是，它确实对查询有影响，因为当MySql创建一个临时表(排序、排序等)时，VARCHAR会被转换为CHAR，并且您可以装入一个页面的记录越多，那么内存就越少，表扫描速度也就越快。
#2
22
MySQL stores a varchar field as a variable length record, with either a one-byte or a two-byte prefix to indicate the record size.
MySQL将varchar字段存储为可变长度记录，其中一个字节或两个字节的前缀表示记录大小。
Having a pattern of storage sizes doesn't really make any difference to how MySQL will function when dealing with variable length record storage. The length specified in a varchar(x) declaration will simply determine the maximum length of the data that can be stored. Basically, a varchar(16) is no different disk-wise than a varchar(128).
拥有一个存储大小的模式对于MySQL在处理可变长度记录存储时的功能并没有什么影响。varchar(x)声明中指定的长度将简单地确定可以存储的数据的最大长度。基本上，varchar(16)和varchar(128)没有什么不同。
This manual page has a more detailed explanation.
本手册页有更详细的说明。
Edit: With regards to your updated question, the answer is still the same. A varchar field will only use up as much space on disk as the data you store in it (plus a one or two byte overhead). So it doesn't matter if you have a varchar(16) or a varchar(128), if you store a 10-character string in it, you're only going to use 10 bytes (plus 1 or 2) of disk space.
编辑:关于你的问题，答案仍然是一样的。varchar字段将只占用磁盘上与您在其中存储的数据相同的空间(加上一个或两个字节的开销)。所以，如果您有一个varchar(16)或varchar(128)，如果您在其中存储10个字符的字符串，那么您只需要使用10个字节(加上1或2)的磁盘空间。