使用整数列在数据库中存储美国邮政编码是个好主意吗？ [英] Is it a good idea to use an integer column for storing US ZIP codes in a database?

问题描述

从第一眼看，我会有两个基本选择，可将邮政编码存储在数据库表：

From first glance, it would appear I have two basic choices for storing ZIP codes in a database table:

1. 文本（可能最常见），即 char（5）或 varchar（9）以支持+4扩展


2. 数字，即32位整数

1. Text (probably most common), i.e. char(5) or varchar(9) to support +4 extension
2. Numeric, i.e. 32-bit integer

如果我们假设没有国际关注，这两个都将满足数据的要求。在过去，我们一般只是走文本路线，但我想知道，如果有人做相反的事情？从简单的比较看，整数方法看起来像两个明显的优点：

Both would satisfy the requirements of the data, if we assume that there are no international concerns. In the past we've generally just gone the text route, but I was wondering if anyone does the opposite? Just from brief comparison it looks like the integer method has two clear advantages:

• 它的本质是自动限制为数字只有（而没有验证的文本风格可以存储字母等，据我所知，在邮政编码中是有效的）。 这意味着我们可以/ would /应该放弃正常的用户输入验证。


• 它占用更少的空间，4个字节

• It is, by means of its nature, automatically limited to numerics only (whereas without validation the text style could store letters and such which are not, to my knowledge, ever valid in a ZIP code). This doesn't mean we could/would/should forgo validating user input as normal, though!
• It takes less space, being 4 bytes (which should be plenty even for 9-digit ZIP codes) instead of 5 or 9 bytes.

此外，它似乎不会伤害显示输出多。在数值上敲击 ToString（）很简单，使用简单的字符串操作插入连字符或空格或任何用于+4扩展名的字符串，并使用字符串格式化恢复前导零。

Also, it seems like it wouldn't hurt display output much. It is trivial to slap a ToString() on a numeric value, use simple string manipulation to insert a hyphen or space or whatever for the +4 extension, and use string formatting to restore leading zeroes.

有没有什么会阻止使用 int 作为仅限美国邮政编码的数据类型？

Is there anything that would discourage using int as a datatype for US-only ZIP codes?

推荐答案

数字邮政编码是一种小小的误导。

A numeric ZIP code is -- in a small way -- misleading.

数字应表示数字 。邮政编码不添加或减少或参与任何数字操作。 12309 - 12345不计算从Schenectady市中心到我的邻居的距离。

Numbers should mean something numeric. ZIP codes don't add or subtract or participate in any numeric operations. 12309 - 12345 does not compute the distance from downtown Schenectady to my neighborhood.

授予，对于邮政编码，没有人困惑。

Granted, for ZIP codes, no one is confused. However, for other number-like fields, it can be confusing.

由于邮政编码不是数字 - 它们只是用受限制的字母表编码 - 我建议避免数字字段。 1字节的保存不值得。我认为意义比字节更重要。

Since ZIP codes aren't numbers -- they just happen to be coded with a restricted alphabet -- I suggest avoiding a numeric field. The 1-byte saving isn't worth much. And I think that that meaning is more important than the byte.

。

对于前导零...是我的观点。数字没有前导零。邮政编码上存在有意义的前导零是另一个证明，它们不是数字。

"As for leading zeroes..." is my point. Numbers don't have leading zeros. The presence of meaningful leading zeros on ZIP codes is yet another proof that they're not numeric.

这篇关于使用整数列在数据库中存储美国邮政编码是个好主意吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！