MySQL 的最佳 varchar 大小是多少？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1151667/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What are the optimum varchar sizes for MySQL?
提问by ae.
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、64、128、255（最大）
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.
我想我担心优化大型表的磁盘空间。
采纳答案by Kris Erickson
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 创建临时表（SORT、ORDER 等）时，VARCHAR 会转换为 CHAR，并且可以放入单个页面的记录越多，表扫描的内存就越少，速度也会越快是。
回答by zombat
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 pagehas 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）的磁盘空间.
