Oracle 主键：NUMBER 与 NUMBER(7,0)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1234634/
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
Oracle primary keys: NUMBER vs NUMBER(7,0)
提问by chris
Is there any benefit to specifying the precision on the PK? Is 7,0 sufficient, given that there will probably never be more than a few thousand records?
指定 PK 的精度有什么好处吗？鉴于可能永远不会超过几千条记录，7,0 是否足够？
Any dangers to not specifying the precision?
不指定精度有什么危险吗？
回答by Quassnoi
NUMBER(7, 0)just constrains the domain of values.
NUMBER(7, 0)只限制值域。
Their internal represenations do not differ:
它们的内部表示没有区别：
CREATE TABLE t_pk (col1 NUMBER(7, 0) NOT NULL, col2 NUMBER(38) NOT NULL)
INSERT
INTO t_pk
VALUES (9999999, 9999999)
SELECT DUMP(col1), DUMP(col2)
FROM t_pk
DUMP(col1) DUMP(col2)
--- ---
Typ=2 Len=5: 196,10,100,100,100 Typ=2 Len=5: 196,10,100,100,100
In Oracle, the NUMBERs are stored as centesimal digits of the numeric value normalized to 0.01 <= N < 1and prepended with the exponent.
在 中Oracle，NUMBERs 存储为数值的百分位数，归一化为0.01 <= N < 1指数并以指数为前缀。
In the example above:
在上面的例子中：
196is the192-based exponent (4).10is decimal9100's are decimal99's
196是192基于指数 (4)。10是十进制9100是十进制99的
The whole number reads in decimal as 00.09 99 99 99 * (100 ^ 4) = 9,999,999
整数以十进制读取为 00.09 99 99 99 * (100 ^ 4) = 9,999,999
The more digits are required to satisfy the precision requested, the more of them will be stored of course.
满足要求的精度需要的数字越多，当然存储的数字就越多。
When you insert a precise value into a less precise column, it just gets rounded to column's precision and is stored rounded.
当您将精确值插入不太精确的列时，它只会四舍五入到列的精度并四舍五入存储。
Therefore, it is safe performance-wise to declare you column NUMBER(38), since it implies no overhead over NUMBER(7, 0)(for the numbers that fit both types).
因此，在性能方面声明你 column 是安全的NUMBER(38)，因为它意味着没有开销NUMBER(7, 0)（对于适合两种类型的数字）。
However, if your PRIMARY KEYs are integer by nature, you better specify precision as 0to make sure no fractional value ever gets to your table.
但是，如果您的PRIMARY KEYs 本质上是整数，则最好指定精度0以确保没有小数值进入您的表。
Update:
更新：
@Macalso pointed that the clients may rely on the column datatype to figure out the values domain.
@Mac还指出客户端可能依赖列数据类型来确定值域。
If your application expects an INT32, you should make your number a NUMBER(9)or below (or whatever type your client considers to be convertable to Int32).
如果您的应用程序需要INT32，您应该将您的号码NUMBER(9)设为a或以下（或您的客户认为可转换为的任何类型Int32）。
回答by Mac
On the database side of the problem, I have nothing to add to Quassnoi's answer.
在问题的数据库方面，我对Quassnoi 的回答没有任何补充。
But it is worth noting that it may as well have an impact on applications that access the database (or, to be more accurate, on the developers of these applications). In .NET for instance, if you get an IDataRecordincluding your primary key (using ODP .NET), a call to GetInt32will miserably fail when your column is defined as NUMBER(38) and succeed when defined as NUMBER(7) (even when when the value is in the correct range).
但值得注意的是，它也可能对访问数据库的应用程序（或者，更准确地说，对这些应用程序的开发人员）产生影响。例如，在 .NET 中，如果您获得一个IDataRecord包括您的主键（使用 ODP .NET），当您的列定义为 NUMBER(38) 时调用GetInt32将悲惨地失败，并且在定义为 NUMBER(7) 时成功（即使当值在正确的范围内时）。
回答by Jeffrey Kemp
If you're not expecting more than, say 100K records in the table, if you specify the PK with N(7,0) you'll get an early warning if some runaway process ends up overflowing the PK. If you specified it with N(38) the warning will not appear so early, perhaps.
如果您不期望超过，例如表中的 100K 记录，如果您使用 N(7,0) 指定 PK，如果某个失控进程最终使 PK 溢出，您将收到早期警告。如果你用 N(38) 指定它，警告可能不会那么早出现。
I'd always err on the side of constraining sizes to the smallest expected for the "life of the product", with a reasonable margin for error.
我总是错误地将尺寸限制为“产品寿命”的最小预期，并有合理的误差余量。
