DECIMAL和NUMERIC之间的区别 [英] Difference between DECIMAL and NUMERIC
问题描述
SQL数据类型 NUMERIC
和 DECIMAL
有什么区别?
如果数据库将这些区别对待,我想至少知道如何做:
What's the difference between the SQL datatype NUMERIC
and DECIMAL
?
If databases treat these differently, I'd like to know how for at least:
- SQL Server
- Oracle
- Db / 2
- MySQL
- PostgreSQL
- SQL Server
- Oracle
- Db/2
- MySQL
- PostgreSQL
此外,数据库驱动程序如何解释这些类型?
Furthermore, are there any differences in how database drivers interpret these types?
推荐答案
几乎所有目的它们都是相同的。
They are the same for almost all purposes.
一次,不同的供应商使用了不同的名称( NUMERIC
/ DECIMAL
)。 SQL-92使它们相同,但有一个细微的差异,这可能是特定于供应商的:
At one time different vendors used different names (NUMERIC
/DECIMAL
) for almost the same thing. SQL-92 made them the same with one minor difference which can be vendor specific:
NUMERIC
必须精确无误。已定义-因此,如果在小数点左边定义4个小数位,在小数点右边定义4个小数位,则数据库必须始终存储4 + 4个小数位,不能多也不能少。
NUMERIC
must be exactly as precise as it is defined — so if you define 4 decimal places to the left of the decimal point and 4 decimal places to the right of it, the DB must always store 4 + 4 decimal places, no more, no less.
DECIMAL
是免费的,如果可以更容易实现,则可以使用更大的数字。这意味着数据库实际上可以存储比指定数量更多的数字(由于幕后存储空间中有多余的数字)。这意味着在上面的4 + 4小数位示例中,数据库可能允许存储 12345.0000
,但是仍然存储 1.00005
DECIMAL
is free to allow higher numbers if that's easier to implement. This means that the database can actually store more digits than specified (due to the behind-the-scenes storage having space for extra digits). This means the database might allow storing 12345.0000
in the above example of 4 + 4 decimal places, but storing 1.00005
is still not allowed if doing so could affect any future calculations.
当前大多数数据库系统都将 DECIMAL
和 NUMERIC
可以是完美的同义词,也可以是两个具有完全相同行为的不同类型。如果类型完全不同,则可能无法在 DECIMAL
列上引用 NUMERIC $ c定义外键约束$ c>列,反之亦然。
Most current database systems treat DECIMAL
and NUMERIC
either as perfect synonyms, or as two distinct types with exactly the same behavior. If the types are considered distinct at all, you might not be able to define a foreign key constrain on a DECIMAL
column referencing a NUMERIC
column or vice versa.
这篇关于DECIMAL和NUMERIC之间的区别的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!