database 数据库字段的标准长度列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20958/
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
List of standard lengths for database fields
提问by Patrick McElhaney
I'm designing a database table and once again asking myself the same stupid question: How long should the firstname field be?
我正在设计一个数据库表，并再次问自己同样愚蠢的问题：名字字段应该多长？
Does anyone have a list of reasonable lengths for the most common fields, such as first name, last name, and email address?
有没有人有最常见字段的合理长度列表，例如名字、姓氏和电子邮件地址？
采纳答案by jrc
W3C's recommendation:
W3C 的建议：
If designing a form or database that will accept names from people with a variety of backgrounds, you should ask yourself whether you really need to have separate fields for given name and family name.
… Bear in mind that names in some cultures can be quite a lot longer than your own. … Avoid limiting the field size for names in your database. In particular, do not assume that a four-character Japanese name in UTF-8 will fit in four bytes – you are likely to actually need 12.
https://www.w3.org/International/questions/qa-personal-names
如果设计的表单或数据库将接受来自不同背景的人的姓名，您应该问自己是否真的需要为名字和姓氏设置单独的字段。
… 请记住，某些文化中的名字可能比您自己的名字要长得多。...避免限制数据库中名称的字段大小。特别是，不要假设 UTF-8 中的四字符日语名称可以容纳四个字节——您实际上可能需要 12 个。
https://www.w3.org/International/questions/qa-personal-names
For database fields, VARCHAR(255)is a safe default choice, unless you can actually come up with a good reason to use something else. For typical web applications, performance won't be a problem. Don't prematurely optimize.
对于数据库字段，VARCHAR(255)是一个安全的默认选择，除非您实际上可以提出使用其他内容的充分理由。对于典型的 Web 应用程序，性能不会成为问题。不要过早地优化。
回答by Eric Z Beard
I just queried my database with millions of customers in the USA.
我刚刚在美国的数百万客户中查询了我的数据库。
The maximum first namelength was 46. I go with 50. (Of course, only 500 of those were over 25, and they were all cases where data imports resulted in extra junk winding up in that field.)
Last namewas similar to first name.
Email addressesmaxed out at 62 characters. Most of the longer ones were actually lists of email addresses separated by semicolons.
Street addressmaxes out at 95 characters. The long ones were all valid.
Max citylength was 35.
名字的最大长度是 46。我选择 50。（当然，其中只有 500 个超过 25，而且它们都是数据导入导致该字段中出现额外垃圾的情况。）
姓氏与名字相似。
电子邮件地址最多为 62 个字符。大多数较长的实际上是由分号分隔的电子邮件地址列表。
街道地址最多为 95 个字符。长的都是有效的。
最大城市长度为 35。
This should be a decent statistical spread for people in the US. If you have localization to consider, the numbers could vary significantly.
对于美国人来说，这应该是一个不错的统计数据。如果您要考虑本地化，则数字可能会有很大差异。
回答by Ian Nelson
UK Government Data Standards Cataloguedetails the UK standards for this kind of thing. It suggests 35 characters for each of Given Name and Family Name, or 70 characters for a single field to hold the Full Name, and 255 characters for an email address. Amongst other things..
英国政府数据标准目录详细介绍了此类事情的英国标准。它建议每个名字和姓氏 35 个字符，或 70 个字符用于保存全名的单个字段，以及 255 个字符的电子邮件地址。除其他外..
回答by Neil McGuigan
Some probably correct column lengths
一些可能正确的列长度
Min Max
Hostname 1 255
Domain Name 4 253
Email Address 7 254
Email Address [1] 3 254
Telephone Number 10 15
Telephone Number [2] 3 26
HTTP(S) URL w domain name 11 2083
URL [3] 6 2083
Postal Code [4] 2 11
IP Address (incl ipv6) 7 45
Longitude numeric 9,6
Latitude numeric 8,6
Money[5] numeric 19,4
[1] Allow local domains or TLD-only domains
[2] Allow short numbers like 911 and extensions like 16045551212x12345
[3] Allow local domains, tv:// scheme
[4] http://en.wikipedia.org/wiki/List_of_postal_codes. Use max 12 if storing dash or space
[5] http://stackoverflow.com/questions/224462/storing-money-in-a-decimal-column-what-precision-and-scale
A long rant on personal names
对个人名字的长篇大论
A personal name is either a Polynym (a name with multiple sortablecomponents), a Mononym (a name with only one component), or a Pictonym (a name represented by a picture - this exists due to people like Prince).
个人名称可以是 Polynym（具有多个可排序组件的名称）、Mononym（只有一个组件的名称）或 Pictonym（由图片表示的名称 - 这是由于像 Prince 这样的人而存在的）。
A person can have multiple names, playing roles, such as LEGAL, MARITAL, MAIDEN, PREFERRED, SOBRIQUET, PSEUDONYM, etc. You might have business rules, such as "a person can only have one legal name at a time, but multiple pseudonyms at a time".
一个人可以有多个名字，扮演角色，比如LEGAL、MARITAL、MAIDEN、PREFERRED、SOBRIQUET、PSEUDONYM等。你可能有业务规则，比如“一个人一次只能有一个法定名字，但可以有多个化名一次”。
Some examples:
一些例子：
names: [
{
type:"POLYNYM",
role:"LEGAL",
given:"George",
middle:"Herman",
moniker:"Babe",
surname:"Ruth",
generation:"JUNIOR"
},
{
type:"MONONYM",
role:"SOBRIQUET",
mononym:"The Bambino" /* mononyms can be more than one word, but only one component */
},
{
type:"MONONYM",
role:"SOBRIQUET",
mononym:"The Sultan of Swat"
}
]
or
或者
names: [
{
type:"POLYNYM",
role:"PREFERRED",
given:"Malcolm",
surname:"X"
},
{
type:"POLYNYM",
role:"BIRTH",
given:"Malcolm",
surname:"Little"
},
{
type:"POLYNYM",
role:"LEGAL",
given:"Malik",
surname:"El-Shabazz"
}
]
or
或者
names:[
{
type:"POLYNYM",
role:"LEGAL",
given:"Prince",
middle:"Rogers",
surname:"Nelson"
},
{
type:"MONONYM",
role:"SOBRIQUET",
mononym:"Prince"
},
{
type:"PICTONYM",
role:"LEGAL",
url:"http://upload.wikimedia.org/wikipedia/en/thumb/a/af/Prince_logo.svg/130px-Prince_logo.svg.png"
}
]
or
或者
names:[
{
type:"POLYNYM",
role:"LEGAL",
given:"Juan Pablo",
surname:"Fernández de Calderón",
secondarySurname:"García-Iglesias" /* hispanic people often have two surnames. it can be impolite to use the wrong one. Portuguese and Spaniards differ as to which surname is important */
}
]
Given names, middle names, surnames can be multiple words such as "Billy Bob" Thornton, or Ralph "Vaughn Williams".
名字、中间名、姓氏可以是多个单词，例如"Billy Bob" Thornton, 或Ralph "Vaughn Williams"。
回答by Kibbee
I would say to err on the high side. Since you'll probably be using varchar, any extra space you allow won't actually use up any extra space unless somebody needs it. I would say for names (first or last), go at least 50 chars, and for email address, make it at least 128. There are some really long email addresses out there.
我会说在高方面犯错。由于您可能会使用 varchar，因此您允许的任何额外空间实际上都不会占用任何额外空间，除非有人需要它。我会说姓名（名字或姓氏）至少要 50 个字符，电子邮件地址至少要 128 个。那里有一些非常长的电子邮件地址。
Another thing I like to do is go to Lipsum.comand ask it to generate some text. That way you can get a good idea of just what 100 bytes looks like.
我喜欢做的另一件事是访问Lipsum.com并要求它生成一些文本。这样你就可以很好地了解 100 字节的样子。
回答by Mike
I pretty much always use a power of 2 unless there is a good reason not to, such as a customer facing interface where some other number has special meaning to the customer.
我几乎总是使用 2 的幂，除非有充分的理由不这样做，例如面向客户的界面，其中一些其他数字对客户具有特殊意义。
If you stick to powers of 2 it keeps you within a limited set of common sizes, which itself is a good thing, and it makes it easier to guess the size of unknown objects you may encounter. I see a fair number of other people doing this, and there is something aesthetically pleasing about it. It generally gives me a good feeling when I see this, it means the designer was thinking like an engineer or mathematician. Though I'd probably be concerned if only prime numbers were used. :)
如果您坚持使用 2 的幂，它会将您限制在一组有限的常见尺寸范围内，这本身就是一件好事，并且可以更轻松地猜测您可能遇到的未知物体的尺寸。我看到相当多的其他人这样做，并且在美学上有一些令人愉悦的东西。当我看到这个时，通常会给我一种很好的感觉，这意味着设计师像工程师或数学家一样思考。虽然我可能会担心是否只使用素数。:)
回答by Steve Chambers
I wanted to find the same and the UK Government Data Standards mentioned in the accepted answer sounded ideal. However none of these seemed to exist any more - after an extended search I found it in an archive here: http://webarchive.nationalarchives.gov.uk/+/http://www.cabinetoffice.gov.uk/govtalk/schemasstandards/e-gif/datastandards.aspx. Need to download the zip, extract it and then open default.htm in the html folder.
我想找到相同的，并且在接受的答案中提到的英国政府数据标准听起来很理想。然而，这些似乎不再存在 - 经过长时间的搜索，我在此处的档案中找到了它：http: //webarchive.nationalarchives.gov.uk/+/http: //www.cabinetoffice.gov.uk/govtalk/ schemasstandards/e-gif/datastandards.aspx。需要下载zip，解压，然后打开html文件夹中的default.htm。
回答by PodTech.io
These might be useful to someone;
这些可能对某人有用；
youtube max channel length = 20
facebook max name length = 50
twitter max handle length = 15
email max length = 255
http://www.interoadvisory.com/2015/08/6-areas-inside-of-linkedin-with-character-limits/
http://www.interoadvisory.com/2015/08/6-areas-inside-of-linkedin-with-character-limits/
回答by Micheal Mouner Mikhail Youssif
+------------+---------------+---------------------------------+
| Field | Length (Char) | Description |
+------------+---------------+---------------------------------+
|firstname | 35 | |
|lastname | 35 | |
|email | 255 | |
|url | 60+ | According to server and browser |
|city | 45 | |
|address | 90 | |
+------------+---------------+---------------------------------+
Edit: Added some spacing
编辑：添加了一些间距
回答by Loren Charnley
Just looking though my email archives, there are a number of pretty long "first" names (of course what is meant by first is variable by culture). One example is Krishnamurthy - which is 13 letters long. A good guess might be 20 to 25 letters based on this. Email should be muchlonger since you might have [email protected]. Also, gmail and some other mail programs allow you to use [email protected] where "sometag" is anything you want to put there so that you can use it to sort incoming emails. I frequently run into web forms that don't allow me to put in my full email address without considering any tags. So, if you need a fixed email field maybe something like [email protected] in characters for a total of 90 characters (if I did my math right!).
看看我的电子邮件档案，有很多很长的“名字”（当然，first 的含义因文化而异）。一个例子是 Krishnamurthy——它有 13 个字母长。基于此，一个好的猜测可能是 20 到 25 个字母。电子邮件应该是多长，因为你可能有[email protected]。此外，gmail 和其他一些邮件程序允许您使用 [email protected]，其中“sometag”是您想要放置的任何内容，以便您可以使用它对收到的电子邮件进行排序。我经常遇到不允许我在不考虑任何标签的情况下输入完整电子邮件地址的网络表单。因此，如果您需要一个固定的电子邮件字段，则可能是 [email protected] 字符，总共 90 个字符（如果我的数学计算正确的话！）。
