我看到VARCHAR(255)被如此频繁地使用(而不是另一个长度)有什么好的理由吗?

时间:2022-01-15 16:04:12

In multiple courses, books, and jobs, I have seen text fields defined as VARCHAR(255) as kind of the default for "shortish" text. Is there any good reason that a length of 255 is chosen so often, other than being a nice round number? Is it a holdout from some time in the past when there was a good reason (whether or not it applies today)?

在许多课程、书籍和工作中,我看到文本字段被定义为VARCHAR(255),作为“短”文本的默认字段。除了一个漂亮的整数之外,255的长度被选择的如此频繁有什么好的理由吗?它是一个过去有一个很好的理由(是否适用于今天)的坚持吗?

I realize, of course, that a tighter limit would be more ideal, if you somehow know the maximum length of the string. But if you are using VARCHAR(255) that probably indicates that you don't know the max length, only that it is a "shortish" string.

当然,我意识到,如果你知道弦的最大长度,更严格的限制会更理想。但是如果您使用VARCHAR(255),这可能表明您不知道最大长度,而只是表示它是一个“短”字符串。


Note: I found this question (varchar(255) v tinyblob v tinytext), which says that VARCHAR(n) requires n+1 bytes of storage for n<=255, n+2 bytes of storage for n>255. Is this the only reason? That seems kind of arbitrary, since you would only be saving two bytes compared to VARCHAR(256), and you could just as easily save another two bytes by declaring it VARCHAR(253).

注意:我发现了这个问题(varchar(255) v tinyblob v tinytext),它说varchar(n)需要n+1字节的存储空间,n<=255, n+2字节的存储空间,n>255。这是唯一的原因吗?这似乎有点随意,因为与VARCHAR(256)相比,您将只节省两个字节,并且您可以通过声明它VARCHAR(253)轻松地保存另外两个字节。

8 个解决方案

#1


69  

Historically, 255 characters has often been the maximum length of a VARCHAR in some DBMSes, and it sometimes still winds up being the effective maximum if you want to use UTF-8 and have the column indexed (because of index length limitations).

在历史上,在某些dbms中,255个字符通常是VARCHAR的最大长度,如果您希望使用UTF-8并对列进行索引(由于索引长度限制),它有时仍然是有效的最大长度。

#2


126  

255 is used because it's the largest number of characters that can be counted with an 8-bit number. It maximizes the use of the 8-bit count, without frivolously requiring another whole byte to count the characters above 255.

255之所以被使用,是因为它是最多的可以用8位数字来计数的字符。它最大限度地使用8位计数,而不愚蠢地要求另一个字节来计数255以上的字符。

When used this way, VarChar only uses the number of bytes + 1 to store your text, so you might as well set it to 255, unless you want a hard limit (like 50) on the number of characters in the field.

当使用这种方式时,VarChar只使用字节数+ 1来存储文本,所以您最好将其设置为255,除非您想要对字段中的字符数量设置一个硬限制(比如50)。

#3


15  

Probably because both SQL Server and Sybase (to name two I am familiar with) used to have a 255 character maximum in the number of characters in a VARCHAR column. For SQL Server, this changed in version 7 in 1996/1997 or so... but old habits sometimes die hard.

可能是因为SQL Server和Sybase(举两个我比较熟悉的例子)在VARCHAR列的字符数中都有255个字符的最大值。对于SQL Server,这在1996/1997年的版本7中发生了改变……但旧习惯有时很难改掉。

#4


13  

I'm going to answer the literal question: no, there isn't a good reason you see VARCHAR(255) used so often (there are indeed reasons, as discussed in the other answers, just not good ones). You won't find many examples of projects that have failed catastrophically because the architect chose VARCHAR(300) instead of VARCHAR(255). This would be an issue of near-total insignificance even if you were talking about CHAR instead of VARCHAR.

我将回答这个字面上的问题:不,你看到VARCHAR(255)被如此频繁地使用不是一个好理由(确实有理由,正如其他答案中讨论的那样,只是不太好)。由于架构师选择VARCHAR(300)而不是VARCHAR(255),所以您不会发现很多项目以灾难性方式失败的例子。这将是一个几乎完全无关紧要的问题,即使你谈论的是CHAR而不是VARCHAR。

#5


8  

When you say 2^8 you get 256, but the numbers in computers terms begins from the number 0. So, then you got the 255, you can probe it in a internet mask for the IP or in the IP itself.

当你说2 ^ 8得到256,但是电脑上的数字从0。那么,你就得到了255,你可以用互联网的面具来探测IP或者IP本身。

255 is the maximum value of a 8 bit integer : 11111111 = 255

255是8位整数的最大值:11111111 = 255

Does that help?

这有帮助吗?

#6


6  

Note: I found this question (varchar(255) v tinyblob v tinytext), which says that VARCHAR(n) requires n+1 bytes of storage for n<=255, n+2 bytes of storage for n>255. Is this the only reason? That seems kind of arbitrary, since you would only be saving two bytes compared to VARCHAR(256), and you could just as easily save another two bytes by declaring it VARCHAR(253).

注意:我发现了这个问题(varchar(255) v tinyblob v tinytext),它说varchar(n)需要n+1字节的存储空间,n<=255, n+2字节的存储空间,n>255。这是唯一的原因吗?这似乎有点随意,因为与VARCHAR(256)相比,您将只节省两个字节,并且您可以通过声明它VARCHAR(253)轻松地保存另外两个字节。

No. you don't save two bytes by declaring 253. The implementation of the varchar is most likely a length counter and a variable length, nonterminated array. This means that if you store "hello" in a varchar(255) you will occupy 6 bytes: one byte for the length (the number 5) and 5 bytes for the five letters.

不。声明253不会节省两个字节。varchar的实现很可能是长度计数器和可变长度、非终止数组。这意味着如果在varchar(255)中存储“hello”,将占用6个字节:长度为1字节(数字5),五个字母为5字节。

#7


3  

An unsigned 1 byte number can contain the range [0-255] inclusive. So when you see 255, it is mostly because programmers think in base 10 (get the joke?) :)

一个未签名的1字节数可以包含范围[0-255]。所以当你看到255的时候,主要是因为程序员以10为基数来思考(懂笑话吗?)

Actually, for a while, 255 was the largest size you could give a VARCHAR in MySQL, and there are advantages to using VARCHAR over TEXT with indexing and other issues.

实际上,在一段时间内,255是您可以在MySQL中提供VARCHAR的最大大小,而且使用VARCHAR而不是文本索引和其他问题是有好处的。

#8


3  

In many applications, like MsOffice (until version 2000 or 2002), the maximum number of characters per cell was 255. Moving data from programs able of handling more than 255 characters per field to/from those applications was a nightmare. Currently, the limit is less and less hindering.

在许多应用程序中,比如MsOffice(直到2000年或2002年版本),每个单元的最大字符数是255。将能够处理每个字段超过255个字符的程序中的数据从这些应用程序移到或移到这些应用程序是一场噩梦。目前,限制越来越少。

#1


69  

Historically, 255 characters has often been the maximum length of a VARCHAR in some DBMSes, and it sometimes still winds up being the effective maximum if you want to use UTF-8 and have the column indexed (because of index length limitations).

在历史上,在某些dbms中,255个字符通常是VARCHAR的最大长度,如果您希望使用UTF-8并对列进行索引(由于索引长度限制),它有时仍然是有效的最大长度。

#2


126  

255 is used because it's the largest number of characters that can be counted with an 8-bit number. It maximizes the use of the 8-bit count, without frivolously requiring another whole byte to count the characters above 255.

255之所以被使用,是因为它是最多的可以用8位数字来计数的字符。它最大限度地使用8位计数,而不愚蠢地要求另一个字节来计数255以上的字符。

When used this way, VarChar only uses the number of bytes + 1 to store your text, so you might as well set it to 255, unless you want a hard limit (like 50) on the number of characters in the field.

当使用这种方式时,VarChar只使用字节数+ 1来存储文本,所以您最好将其设置为255,除非您想要对字段中的字符数量设置一个硬限制(比如50)。

#3


15  

Probably because both SQL Server and Sybase (to name two I am familiar with) used to have a 255 character maximum in the number of characters in a VARCHAR column. For SQL Server, this changed in version 7 in 1996/1997 or so... but old habits sometimes die hard.

可能是因为SQL Server和Sybase(举两个我比较熟悉的例子)在VARCHAR列的字符数中都有255个字符的最大值。对于SQL Server,这在1996/1997年的版本7中发生了改变……但旧习惯有时很难改掉。

#4


13  

I'm going to answer the literal question: no, there isn't a good reason you see VARCHAR(255) used so often (there are indeed reasons, as discussed in the other answers, just not good ones). You won't find many examples of projects that have failed catastrophically because the architect chose VARCHAR(300) instead of VARCHAR(255). This would be an issue of near-total insignificance even if you were talking about CHAR instead of VARCHAR.

我将回答这个字面上的问题:不,你看到VARCHAR(255)被如此频繁地使用不是一个好理由(确实有理由,正如其他答案中讨论的那样,只是不太好)。由于架构师选择VARCHAR(300)而不是VARCHAR(255),所以您不会发现很多项目以灾难性方式失败的例子。这将是一个几乎完全无关紧要的问题,即使你谈论的是CHAR而不是VARCHAR。

#5


8  

When you say 2^8 you get 256, but the numbers in computers terms begins from the number 0. So, then you got the 255, you can probe it in a internet mask for the IP or in the IP itself.

当你说2 ^ 8得到256,但是电脑上的数字从0。那么,你就得到了255,你可以用互联网的面具来探测IP或者IP本身。

255 is the maximum value of a 8 bit integer : 11111111 = 255

255是8位整数的最大值:11111111 = 255

Does that help?

这有帮助吗?

#6


6  

Note: I found this question (varchar(255) v tinyblob v tinytext), which says that VARCHAR(n) requires n+1 bytes of storage for n<=255, n+2 bytes of storage for n>255. Is this the only reason? That seems kind of arbitrary, since you would only be saving two bytes compared to VARCHAR(256), and you could just as easily save another two bytes by declaring it VARCHAR(253).

注意:我发现了这个问题(varchar(255) v tinyblob v tinytext),它说varchar(n)需要n+1字节的存储空间,n<=255, n+2字节的存储空间,n>255。这是唯一的原因吗?这似乎有点随意,因为与VARCHAR(256)相比,您将只节省两个字节,并且您可以通过声明它VARCHAR(253)轻松地保存另外两个字节。

No. you don't save two bytes by declaring 253. The implementation of the varchar is most likely a length counter and a variable length, nonterminated array. This means that if you store "hello" in a varchar(255) you will occupy 6 bytes: one byte for the length (the number 5) and 5 bytes for the five letters.

不。声明253不会节省两个字节。varchar的实现很可能是长度计数器和可变长度、非终止数组。这意味着如果在varchar(255)中存储“hello”,将占用6个字节:长度为1字节(数字5),五个字母为5字节。

#7


3  

An unsigned 1 byte number can contain the range [0-255] inclusive. So when you see 255, it is mostly because programmers think in base 10 (get the joke?) :)

一个未签名的1字节数可以包含范围[0-255]。所以当你看到255的时候,主要是因为程序员以10为基数来思考(懂笑话吗?)

Actually, for a while, 255 was the largest size you could give a VARCHAR in MySQL, and there are advantages to using VARCHAR over TEXT with indexing and other issues.

实际上,在一段时间内,255是您可以在MySQL中提供VARCHAR的最大大小,而且使用VARCHAR而不是文本索引和其他问题是有好处的。

#8


3  

In many applications, like MsOffice (until version 2000 or 2002), the maximum number of characters per cell was 255. Moving data from programs able of handling more than 255 characters per field to/from those applications was a nightmare. Currently, the limit is less and less hindering.

在许多应用程序中,比如MsOffice(直到2000年或2002年版本),每个单元的最大字符数是255。将能够处理每个字段超过255个字符的程序中的数据从这些应用程序移到或移到这些应用程序是一场噩梦。目前,限制越来越少。