使用整数列在数据库中存储美国邮政编码是个好主意吗? [英] 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:
- 文本(可能最常见),即
char(5)
或varchar(9)
以支持+4扩展 - 数字,即32位整数
- Text (probably most common), i.e.
char(5)
orvarchar(9)
to support +4 extension - 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屋!