database 我需要将邮政编码存储在数据库中。柱子应该有多大？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/325041/
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
I need to store postal codes in a database. How big should the column be?
提问by EvilTeach
I expect the column to be a VARCHAR2, in my Oracle Database.
我希望该列是我的 Oracle 数据库中的 VARCHAR2。
US Zips are 9.
美国邮编是 9。
Canadian is 7.
加拿大是7。
I am thinking 32 characters would be reasonable upper limit
我认为 32 个字符是合理的上限
What am I missing?
我错过了什么？
[EDIT] TIL: 12 is a reasonable answer to the question Thanks to everyone who contributed.
[编辑] TIL：12 是对这个问题的合理答案 感谢所有贡献者。
采纳答案by strager
Skimming through Wikipedia's Postal Codes page, 32 characters should be more than enough. I would say even 16 characters is good.
浏览维基百科的邮政编码页面，32 个字符应该绰绰有余。我会说即使是 16 个字符也不错。
回答by Vajk Hermecz
As already raised by @neil-mcguigan, wikipedia has a decent page on the topic. Based on that 12 characters should do it: http://en.wikipedia.org/wiki/List_of_postal_codes
正如@neil-mcguigan 已经提出的那样，维基百科在这个主题上有一个不错的页面。基于 12 个字符应该这样做：http: //en.wikipedia.org/wiki/List_of_postal_codes
The wikipedia article lists ~254 countries, which is pretty good regarding UPU (Universal Postal Union)has 192 member countries.
维基百科文章列出了大约 254 个国家，这对于UPU（万国邮政联盟）有 192 个成员国来说相当不错。
回答by Justin Cave
Why would you declare a field size larger than the actual data you are expecting to store in it?
为什么要声明一个字段大小大于您期望存储在其中的实际数据？
If the initial version of your application is going to support US and Canadian addresses (which I'm inferring from the fact that you call out those sizes in your question), I'd declare the field as VARCHAR2(9) (or VARCHAR2(10) if you intend to store the hyphen in ZIP+4 fields). Even looking at the posts others have made to postal codes across countries, VARCHAR2(9) or VARCHAR2(10) would be sufficient for the most if not all other countries.
如果您的应用程序的初始版本将支持美国和加拿大的地址（我是从您在问题中标出这些大小的事实中推断出来的），我会将该字段声明为 VARCHAR2(9)（或 VARCHAR2( 10) 如果您打算将连字符存储在 ZIP+4 字段中)。即使查看其他国家/地区邮政编码的帖子，VARCHAR2(9) 或 VARCHAR2(10) 对大多数国家（如果不是所有其他国家）也足够了。
Down the line, you can always ALTER the column to increase the length should the need arise. But it is generally hard to prevent someone, somewhere from deciding to get "creative" and stuff 50 characters into a VARCHAR2(50) field for one reason or another (i.e. because they want another line on a shipping label). You also have to deal with testing the boundary cases (will every application that displays a ZIP handle 50 characters?). And with the fact that when clients are retrieving data from the database, they are generally allocating memory based on the maximum size of the data that will be fetched, not the actual length of a given row. Probably not a huge deal in this specific case, but 40 bytes per row could be a decent chunk of RAM for some situations.
接下来，如果需要，您可以随时更改列以增加长度。但通常很难阻止某人，某处出于某种原因（即，因为他们想要运输标签上的另一行）决定获得“创造性”并将 50 个字符填充到 VARCHAR2(50) 字段中。您还必须处理测试边界情况（每个显示 ZIP 的应用程序都会处理 50 个字符吗？）。事实上，当客户端从数据库中检索数据时，他们通常根据将要获取的数据的最大大小而不是给定行的实际长度来分配内存。在这种特定情况下可能不是什么大问题，但在某些情况下，每行 40 字节可能是一块不错的 RAM。
As an aside, you might also consider storing (at least for US addresses) the ZIP code and the +4 extension separately. It is generally useful to be able to generate reports by geographical region, and you may frequently want to put everything in a ZIP code together rather than breaking it down by the +4 extension. At that point, it's useful to not have to try to SUBSTR out the first 5 characters for the ZIP code.
顺便说一句，您还可以考虑分别存储（至少对于美国地址）邮政编码和 +4 扩展名。能够按地理区域生成报告通常很有用，您可能经常希望将所有内容放在一个邮政编码中，而不是按 +4 扩展名将其分解。在这一点上，不必尝试从邮政编码的前 5 个字符中删除 SUBSTR 是很有用的。
回答by paxdiablo
What you're missing is a reason why you need the postal code to be handled specially.
您缺少的是需要特别处理邮政编码的原因。
If you don't really need to WORKwith a postal code, I would suggest not worrying about it. By work, I mean do special processing for rather than just use to print address labels and so on.
如果你并不真的需要工作与邮政编码，我会建议不要担心。通过工作，我的意思是做特殊处理，而不仅仅是用于打印地址标签等。
Simply create three or four address fields of VARCHAR2(50) [for example] and let the user input whatever they want.
只需创建三个或四个 VARCHAR2(50) 的地址字段 [例如] 并让用户输入他们想要的任何内容。
Do you really needto group your orders or transactions by postcode? I think not, since different countries have vastly different schemes for this field.
您真的需要按邮政编码对订单或交易进行分组吗？我认为不会，因为不同的国家在这个领域有非常不同的计划。
回答by Stephan Eggermont
Normalization? Postal codes might be used more than once, and might be related to street names or town names. Separate table(s).
正常化？邮政编码可能会被多次使用，并且可能与街道名称或城镇名称有关。单独的表。
回答by tegbains
Canadian Postal Codes are only 6 characters, in the form of letter's and numbers (LNLNLN)
加拿大邮政编码只有 6 个字符，采用字母和数字的形式 (LNLNLN)
回答by PodTech.io
UK have published standards: UK Government Data Standards Catalogue
英国已发布标准：英国政府数据标准目录
Max 35 characters per line
International Postal Address:
国际邮政地址：
Minimum of 2 lines and maximum of 5 lines for the postal delivery point
details, plus 1 line for country and 1 line for postcode/zip code
The UK postal code length is:
英国邮政编码长度为：
Minimum 6 and Maximum 8 characters
回答by Jay Kapasi
If you want to integrate postal codes in database then geonames database is best to use. Even though it is tough to use and understand but it is the largest geographical database available freely to users like us.
如果您想在数据库中集成邮政编码，那么最好使用 geonames 数据库。尽管它很难使用和理解，但它是像我们这样的用户可以免费使用的最大的地理数据库。
All the other such database are more or less likely have same data and structure. They just remove some extra/redundant information from database. If you are just doing it for low load systems use their free services the limits are attractive and provides more easy interface using json and ajax. You can view the limits here
所有其他此类数据库或多或少都可能具有相同的数据和结构。他们只是从数据库中删除了一些额外/冗余的信息。如果您只是为低负载系统做这件事，请使用他们的免费服务，这些限制很有吸引力，并使用 json 和 ajax 提供更简单的界面。您可以在此处查看限制
For your information varchar(20) is sufficient for storing postal codes
供您参考 varchar(20) 足以存储邮政编码
