MySQL：为什么使用 VARCHAR(20) 而不是 VARCHAR(255)？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1262174/
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
MySQL: Why use VARCHAR(20) instead of VARCHAR(255)?
提问by caw
Possible Duplicate:
Are there disadvantages to using a generic varchar(255) for all text-based fields?
In MYSQL you can choose a length for the VARCHAR field type. Possible values are 1-255.
在 MYSQL 中，您可以为 VARCHAR 字段类型选择长度。可能的值为 1-255。
But what are its advantages if you use VARCHAR(255) that is the maximum instead of VARCHAR(20)? As far as I know, the size of the entries depends only on the real length of the inserted string.
但是，如果您使用最大值 VARCHAR(255) 而不是 VARCHAR(20)，它的优势是什么？据我所知，条目的大小仅取决于插入字符串的实际长度。
size (bytes) = length+1
大小（字节）= 长度+1
So if you have the word "Example" in a VARCHAR(255) field, it would have 8 bytes. If you have it in a VARCHAR(20) field, it would have 8 bytes, too. What is the difference?
因此，如果在 VARCHAR(255) 字段中有“示例”一词，它将有 8 个字节。如果您在 VARCHAR(20) 字段中有它，它也会有 8 个字节。有什么不同？
I hope you can help me. Thanks in advance!
我希望你能帮助我。提前致谢！
采纳答案by RC.
Check out: Reference for Varchar
查看：Varchar 参考
In short there isn't much difference unless you go over the size of 255 in your VARCHAR which will require another byte for the length prefix.
简而言之，除非您在 VARCHAR 中超过 255 的大小，否则没有太大区别，这将需要另一个字节作为长度前缀。
The length indicates more of a constraint on the data stored in the column than anything else. This inherently constrains the MAXIMUM storage size for the column as well. IMHO, the length should make sense with respect to the data. If your storing a Social Security # it makes no sense to set the length to 128 even though it doesn't cost you anything in storage if all you actually store is an SSN.
长度表明对存储在列中的数据的约束比其他任何东西都多。这也固有地限制了列的 MAXIMUM 存储大小。恕我直言，长度应该对数据有意义。如果您存储的是社会保障号，那么将长度设置为 128 是没有意义的，即使您实际存储的只是 SSN 的存储不会花费您任何费用。
回答by Dan Diplo
There are many valid reasons for choosing a value smaller than the maximum that are not related to performance. Setting a size helps indicate the type of data you are storing and also can also act as a last-gasp form of validation.
选择小于最大值且与性能无关的值有很多正当理由。设置大小有助于指示您存储的数据类型，还可以作为最后的验证形式。
For instance, if you are storing a UK postcode then you only need 8 characters. Setting this limit helps make clear the type of data you are storing. If you chose 255 characters it would just confuse matters.
例如，如果您要存储英国邮政编码，那么您只需要 8 个字符。设置此限制有助于明确您存储的数据类型。如果您选择 255 个字符，那只会使事情变得混乱。
回答by HLGEM
I don't know about mySQL but in SQL Server it will let you define fields such that the total number of bytes used is greater than the total number of bytes that can actually be stored in a record. This is a bad thing. Sooner or later you will get a row where the limit is reached and you cannot insert the data.
我不了解 mySQL，但在 SQL Server 中，它可以让您定义字段，以便使用的总字节数大于实际可以存储在记录中的总字节数。这是一件坏事。迟早你会得到一行达到限制并且你不能插入数据。
It is far better to design your database structure to consider row size limits.
设计数据库结构以考虑行大小限制要好得多。
Additionally yes, you do not want people to put 200 characters in a field where the maximum value should be 10. If they do, it is almost always bad data.
另外是的，您不希望人们将 200 个字符放在最大值应为 10 的字段中。如果这样做，几乎总是坏数据。
You say, well I can limit that at the application level. But data does not get into the database just from one application. Sometimes multiple applications use it, sometimes data is imported and sometimes it is fixed manually from the query window (update all the records to add 10% to the price for instance). If any of these other sources of data don't know about the rules you put in your application, you will have bad, useless data in your database. Data integrity must be enforced at the database level (which doesn't stop you from also checking before you try to enter data) or you have no integrity. Plus it has been my experience that people who are too lazy to design their database are often also too lazy to actually put the limits into the application and there is no data integrity check at all.
你说，我可以在应用程序级别限制它。但是数据不会仅仅从一个应用程序进入数据库。有时多个应用程序使用它，有时导入数据，有时从查询窗口手动修复它（例如更新所有记录以增加 10% 的价格）。如果这些其他数据源中的任何一个不了解您在应用程序中放置的规则，那么您的数据库中就会有坏的、无用的数据。必须在数据库级别强制执行数据完整性（这不会阻止您在尝试输入数据之前也进行检查），否则您就没有完整性。另外，我的经验是，懒得设计数据库的人通常也懒得实际将限制放入应用程序中，而且根本没有数据完整性检查。
They have a word for databases with no data integrity - useless.
他们对没有数据完整性的数据库有一个词 - 无用。
回答by Martin v. L?wis
There is a semantical difference (and I believe that's the only difference): if you try to fill 30 non-space characters into varchar(20), it will produce an error, whereas it will succeed for varchar(255). So it is primarily an additional constraint.
存在语义差异（我认为这是唯一的区别）：如果您尝试将 30 个非空格字符填充到 varchar(20) 中，则会产生错误，而对于 varchar(255) 则会成功。所以它主要是一个额外的约束。
回答by OneNerd
Well, if you want to allow for a larger entry, or limit the entry size perhaps.
好吧，如果您想允许更大的条目，或者可能限制条目大小。
For example, you may have first_name as a VARCHAR 20, but perhaps street_address as a VARCHAR 50 since 20 may not be enough space. At the same time, you may want to control how large that value can get.
例如，您可能将 first_name 作为 VARCHAR 20，但可能将 street_address 作为 VARCHAR 50，因为 20 可能没有足够的空间。同时，您可能希望控制该值可以获得多大。
In other words, you have set a ceiling of how large a particular value can be, in theory to prevent the table (and potentially the index/index entries) from getting too large.
换句话说，您已经设置了一个特定值可以有多大的上限，理论上是为了防止表（以及可能的索引/索引条目）变得太大。
You could just use CHAR which is a fixed width as well, but unlike VARCHAR which can be smaller, CHAR pads the values (although this makes for quicker SQL access.
您也可以只使用固定宽度的 CHAR，但与可以更小的 VARCHAR 不同，CHAR 填充值（尽管这可以加快 SQL 访问速度。
回答by Mitchel Sellers
From a database perspective performance wise I do not believe there is going to be a difference.
从数据库的角度来看，性能方面我不相信会有区别。
However, I think a lot of the decision on the length to use comes down to what you are trying to accomplish and documenting the system to accept just the data that it needs.
但是，我认为很多关于使用长度的决定归结为您要完成的工作以及记录系统以仅接受它需要的数据。
