我需要将邮政编码存储在数据库中。 柱子应该有多大？
在我的 Oracle 数据库中，我希望该列是 VARCHAR2。
美国邮政编码是 9。
加拿大邮政编码是 7。
我认为 32 个字符是合理的上限，
我错过了什么？
[编辑] TIL：12 是该问题的合理答案 感谢所有做出贡献的人。
I expect the column to be a VARCHAR2, in my Oracle Database.
US Zips are 9.
Canadian is 7.
I am thinking 32 characters would be reasonable upper limit
What am I missing?
[EDIT]
TIL: 12 is a reasonable answer to the question
Thanks to everyone who contributed.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（8）
浏览一下维基百科的邮政编码页面，32 个字符应该足够了。 我想说即使是 16 个字符也很好。
Skimming through Wikipedia's Postal Codes page, 32 characters should be more than enough. I would say even 16 characters is good.
正如@neil-mcguigan 已经提出的那样，维基百科在这个主题上有一个不错的页面。 基于这 12 个字符应该做到这一点：http://en.wikipedia.org/wiki/List_of_postal_codes
维基百科文章列出了约 254 个国家/地区，这对于 UPU（通用邮政联盟）有192个成员国。
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
The wikipedia article lists ~254 countries, which is pretty good regarding UPU (Universal Postal Union) has 192 member countries.
为什么您声明的字段大小大于您期望存储在其中的实际数据？
如果您的应用程序的初始版本将支持美国和加拿大地址（我从您在问题中调用这些大小的事实推断出这一点），我会将该字段声明为 VARCHAR2(9) （或 VARCHAR2( 10) 如果您打算将连字符存储在 ZIP+4 字段中）。 即使查看其他人针对各个国家/地区的邮政编码发布的帖子，VARCHAR2(9) 或 VARCHAR2(10) 对于大多数（如果不是所有）其他国家/地区来说也足够了。
以后，如果需要，您可以随时更改列以增加长度。 但通常很难阻止某人、某个地方出于某种原因决定“发挥创意”并将 50 个字符填充到 VARCHAR2(50) 字段中（即，因为他们想要在运输标签上再添加一行）。 您还必须处理边界情况的测试（每个显示 ZIP 的应用程序都会处理 50 个字符吗？）。 事实上，当客户端从数据库检索数据时，它们通常根据将获取的数据的最大大小而不是给定行的实际长度来分配内存。 在这种特定情况下可能没什么大不了的，但在某些情况下，每行 40 字节可能是相当大的 RAM 块。
另外，您还可以考虑单独存储（至少对于美国地址）邮政编码和 +4 扩展名。 能够按地理区域生成报告通常很有用，并且您可能经常希望将邮政编码中的所有内容放在一起，而不是通过 +4 扩展名将其分解。 此时，不必尝试 SUBSTR 出邮政编码的前 5 个字符，这很有用。
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.
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.
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.
正常化？ 邮政编码可能会多次使用，并且可能与街道名称或城镇名称相关。 单独的表。
Normalization? Postal codes might be used more than once, and might be related to street names or town names. Separate table(s).
您缺少的是需要特殊处理邮政编码的原因。
如果您确实不需要使用邮政编码工作，我建议您不要担心。 我所说的工作，是指进行特殊处理，而不仅仅是用于打印地址标签等。
只需创建三个或四个 VARCHAR2(50) 地址字段[例如]，然后让用户输入他们想要的任何内容。
您真的需要按邮政编码对订单或交易进行分组吗？ 我认为不是，因为不同的国家在这个领域有截然不同的方案。
What you're missing is a reason why you need the postal code to be handled specially.
If you don't really need to WORK with 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.
Do you really need to group your orders or transactions by postcode? I think not, since different countries have vastly different schemes for this field.
加拿大邮政编码只有 6 个字符，采用字母和数字的形式 (LNLNLN)
Canadian Postal Codes are only 6 characters, in the form of letter's and numbers (LNLNLN)
英国已发布标准：英国政府数据标准目录
国际邮政地址：
英国邮政编码长度为：
UK have published standards: UK Government Data Standards Catalogue
International Postal Address:
The UK postal code length is:
如果您想将邮政编码集成到数据库中，那么最好使用 geonames 数据库。 尽管它很难使用和理解，但它是像我们这样的用户可以免费使用的最大的地理数据库。
所有其他此类数据库或多或少可能具有相同的数据和结构。 他们只是从数据库中删除一些额外/冗余的信息。 如果您只是为低负载系统执行此操作，请使用他们的免费服务，这些限制很有吸引力，并且使用 json 和 ajax 提供更简单的界面。 您可以在此处查看限制，
以供您参考 varchar(20) 足以存储邮政编码
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.
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
For your information varchar(20) is sufficient for storing postal codes