为什么我应该为 MySQL 中的 varchar 选择 255 以外的任何其他长度？

Why should I ever choose any other length than 255 for varchar in MySQL?

提问by Lizard

I know the differnce between CHAR and VARCHAR,

我知道 CHAR 和 VARCHAR 之间的区别，

CHAR - Fixed length

VARCHAR - Variable length (size + 1 byte)

CHAR - 固定长度

VARCHAR - 可变长度（大小 + 1 个字节）

But I wanted to know what was the purpse of the having the option for a varchar length e.g. VARCHAR(50), VARCHAR(100), VARCHAR(255)

但我想知道什么是对一个varchar长度例如具有选择权的purpse VARCHAR(50)，VARCHAR(100)，VARCHAR(255)

This seems pointless to me because the actual space used depends on the value stored in the database.

这对我来说似乎毫无意义，因为实际使用的空间取决于存储在数据库中的值。

So my questions are:

所以我的问题是：

1) It is fine to set all my varchar's to 255 2) Why would you want to specify any other lenght?

1) 可以将我所有的 varchar 设置为 255 2) 为什么要指定任何其他长度？

采纳答案by MarkD

1) If you dont want to limit the maximum size of a stored varchar, then yes it is fine. That being said...

1) 如果您不想限制存储的 varchar 的最大大小，那么可以。话虽如此...

2) In many cases you want to set an upper limit for the size of a varchar. Lets say you are storing a mailing list, and have a limited amount of space for an address line. By setting an upper limit for your address field, you now allow the database to enforce a maximum address line length for you.

2) 在许多情况下，您希望为 varchar 的大小设置上限。假设您正在存储一个邮件列表，并且地址行的空间有限。通过为您的地址字段设置上限，您现在允许数据库为您强制执行最大地址行长度。

回答by Jauzsika

Excerpt from the MySQL documentation:

摘自MySQL 文档：

The CHAR and VARCHAR types are similar, but differ in the way they are stored and retrieved. As of MySQL 5.0.3, they also differ in maximum length and in whether trailing spaces are retained.

CHAR 和 VARCHAR 类型相似，但存储和检索的方式不同。从 MySQL 5.0.3 开始，它们在最大长度和是否保留尾随空格方面也有所不同。

The CHAR and VARCHAR types are declared with a length that indicates the maximum number of characters you want to store. For example, CHAR(30) can hold up to 30 characters.

CHAR 和 VARCHAR 类型声明的长度指示要存储的最大字符数。例如，CHAR(30) 最多可容纳 30 个字符。

The length of a CHAR column is fixed to the length that you declare when you create the table. The length can be any value from 0 to 255. When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed.

CHAR 列的长度固定为您在创建表时声明的长度。长度可以是 0 到 255 之间的任何值。存储 CHAR 值时，它们会用空格右填充到指定的长度。检索 CHAR 值时，将删除尾随空格。

Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions. The effective maximum length of a VARCHAR in MySQL 5.0.3 and later is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used.

VARCHAR 列中的值是可变长度的字符串。在 MySQL 5.0.3 之前，长度可以指定为 0 到 255 之间的值，在 5.0.3 及更高版本中可以指定为 0 到 65,535。MySQL 5.0.3 及更高版本中 VARCHAR 的有效最大长度受最大行大小（65,535 字节，在所有列之间共享）和使用的字符集的约束。

In contrast to CHAR, VARCHAR values are stored as a one-byte or two-byte length prefix plus data. The length prefix indicates the number of bytes in the value. A column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes.

与 CHAR 相比，VARCHAR 值存储为一字节或两字节长度的前缀加上数据。长度前缀表示值中的字节数。如果值需要不超过 255 个字节，则列使用一个长度字节，如果值可能需要超过 255 个字节，则使用两个长度字节。

回答by Aniket Kulkarni

CHAR Vs VARCHAR

CHAR 与 VARCHAR

CHARis used for Fixed Length Size Variable.
VARCHARis used for Variable Length Size Variable.

CHAR用于固定长度大小变量。
VARCHAR用于可变长度大小变量。

E.g.

例如

create table emp
(f_name CHAR(20),
 l_name VARCHAR(20)
);

insert into emp values('Suraj','Chandak');

select length(f_name), length(l_name) from emp;

Output will be

length(f_name)          Length(l_name)
   20                       7

The best answer for CHAR vs VARCHAR

CHAR 与 VARCHAR的最佳答案

Edit

编辑

• You can set maximum upper limit for the column.
• Performance and storage can have effect.

• 您可以为列设置最大上限。
• 性能和存储会产生影响。

Thanks.

谢谢。

回答by musafar006

Fixed-length (Static) Tablesare Faster. When every single column in a table is “fixed-length”, the table is also considered “static”or “fixed-length”. Examples of column types that are NOT fixed-length are: VARCHAR, TEXT, BLOB.

http://net.tutsplus.com/tutorials/other/top-20-mysql-best-practices/

固定长度（静态）表更快。当表中的每一列都是“固定长度”时，该表也被视为“静态”或“固定长度”。非固定长度的列类型示例有：VARCHAR、TEXT、BLOB。

http://net.tutsplus.com/tutorials/other/top-20-mysql-best-practices/

So if your table does not have any other fields that are varchar, text, or blob; you can use char and make your table a static one. That way they are faster.

因此，如果您的表没有任何其他 varchar、text 或 blob 字段；您可以使用 char 并使您的表格成为静态表格。这样他们就更快了。

回答by Alon Kogan

The main difference between these two value types comes into place when doing a comparison between strings.

在字符串之间进行比较时，这两种值类型之间的主要区别就出现了。

In a CHAR column which its length is predefined you'll have to "run" all the way throughout the column length, while in VARCHAR column you'll need to "run" all the way throughout the value length and not column length, which is way faster in most cases.

在其长度是预定义的 CHAR 列中，您必须在整个列长度中“运行”，而在 VARCHAR 列中，您需要在整个值长度而不是列长度中“运行”在大多数情况下更快。

Therefore a value length which is smaller than the field length will be compared faster if stored in a VARCHAR field.

因此，如果存储在 VARCHAR 字段中，则小于字段长度的值长度将被更快地比较。

回答by Grygoriy Gonchar

But I wanted to know what was the purpse of the having the option for a varchar length e.g. VARCHAR(50), VARCHAR(100), VARCHAR(255)

This seems pointless to me because the actual space used depends on the value stored in the database.

但我想知道具有 varchar 长度选项的目的是什么，例如 VARCHAR(50), VARCHAR(100), VARCHAR(255)

这对我来说似乎毫无意义，因为实际使用的空间取决于存储在数据库中的值。

Specifying e.g. VARCHAR(5) instead of VARCHAR(500) can give you better performance in some cases, e.g. for operations which use in-memory temporary tables.

指定例如 VARCHAR(5) 而不是 VARCHAR(500) 可以在某些情况下为您提供更好的性能，例如对于使用内存临时表的操作。

Another case is to restrict column length to compliment domain requirements (when your value should not be greater then some maximum. Example: full domain name in DNS may not exceed the length of 253 characters)

另一种情况是限制列长度以满足域要求（当您的值不应大于某个最大值时。例如：DNS 中的完整域名不能超过 253 个字符的长度）

回答by Grygoriy Gonchar

1) Technically it is fine, because the fields are created with only 1 or 2 bytes in length in the beginning. Afterwards, they'll grow as necessary.

1) 从技术上讲，这很好，因为这些字段在创建时只有 1 或 2 个字节的长度。之后，它们将根据需要增长。

2) Having said that though, good design principles suggest that you set field lengths appropriately so a) If someone goes through the table scheme and tries to work out how much data is stored in particular fields, they can see that certain fields will hold less data than others and b) you can prevent small amounts of extra work done by the database engine because it has to truncate less space from a VARCHAR(10) field than a VARCHAR(255) during an insert.

2) 尽管如此，好的设计原则建议您适当设置字段长度 a) 如果有人通过表格方案并尝试计算特定字段中存储了多少数据，他们可以看到某些字段将容纳更少b) 您可以防止数据库引擎完成少量额外工作，因为它在插入期间从 VARCHAR(10) 字段截断的空间比 VARCHAR(255) 少。

You can view extra details about it here:

您可以在此处查看有关它的更多详细信息：

http://dev.mysql.com/doc/refman/5.0/en/char.html

http://dev.mysql.com/doc/refman/5.0/en/char.html

回答by Greg Gauthier

I have read elsewhere that varchar comes with a performance hit relative to char, when you run selects against columns defined with them. So, maybe you want to choose char, if you know for sure the field will always be a certain length, and you have performance issues...

我在别处读到，当您对用它们定义的列运行选择时，varchar 会带来相对于 char 的性能下降。所以，也许您想选择字符，如果您确定该字段将始终是特定长度，并且您有性能问题...

回答by Toby Allen

1) Yes.

1) 是的。

2) Historically it was a performance hit.

2）从历史上看，这是一个性能打击。

Look at databases such as sqlite that store everything as text for evidence that it no longer really matters.

看看像 sqlite 这样将所有内容都存储为文本的数据库，以证明它不再真正重要。