为什么我要在MySQL中为varchar选择255以外的任何长度?

I know the differnce between CHAR and VARCHAR,

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

CHAR - Fixed length

CHAR(固定长度

VARCHAR - Variable length (size + 1 byte)

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 (50) varchar (100) varchar (255) 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)为什么要指定其他的灯?

9 个解决方案

#1

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的大小设置一个上限。假设您正在存储一个邮件列表，并且有有限的地址行空间。通过设置地址字段的上限，现在允许数据库为您强制执行最大地址行长度。

#2

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字节，则使用两个长度字节。

#3

CHAR Vs VARCHAR

CHAR Vs VARCHAR

CHAR is used for Fixed Length Size Variable.
VARCHAR is 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 vs VARCHAR的最佳答案

Edit

编辑

You can set maximum upper limit for the column.
可以为列设置最大上限。
Performance and storage can have effect.
性能和存储可以产生影响。

Thanks.

谢谢。

#4

Fixed-length (Static) Tables are 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.

固定长度(静态)表更快。当表中的每一列都是“固定长度”时，表也被认为是“静态的”或“固定长度的”。非定长列类型的示例有:VARCHAR、文本、BLOB。

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

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并使您的表成为一个静态表。这样他们就更快了。

#5

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)表示,尽管,好的设计原则建议你设置字段长度适当所以)如果有人经过表方案,并试图找出多少数据存储在特定的领域,他们可以看到某些字段将比别人更少的数据和b)可以防止少量的额外工作由数据库引擎,因为它从一个VARCHAR(10)截断较少的空间比在插入一个VARCHAR(255)。

You can view extra details about it here:

#6

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在运行select时，会对与char定义的列产生性能冲击。所以，也许你想选择char，如果你知道这个字段永远是一定长度的，并且你有性能问题…

#7

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字段中存储一个小于字段长度的值长度，则比较速度会更快。

#8

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，它将所有东西都存储为文本，以证明它不再重要。

#9

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 (50) varchar (100) varchar (255) varchar (255)

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

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

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个字符的长度)

#1