SQL DECIMAL 和 NUMERIC 的区别

Difference between DECIMAL and NUMERIC

提问by leeeroy

What's the difference between the SQL datatype NUMERICand DECIMAL? If databases treat these differently, I'd like to know how for at least:

SQL 数据类型NUMERIC和DECIMAL? 如果数据库以不同的方式对待这些，我想知道至少如何：

• SQL Server
• Oracle
• Db/2
• MySQL
• PostgreSQL

• 数据库服务器
• 甲骨文
• 分贝/2
• MySQL
• PostgreSQL

Furthermore, are there any differences in how database drivers interpret these types?

此外，数据库驱动程序如何解释这些类型有什么不同吗？

回答by David

They are the same for almost all purposes.

它们几乎适用于所有目的。

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:

曾几何时，不同的供应商对几乎相同的事物使用不同的名称（数字/十进制）。SQL-92 使它们相同，只有一个细微的区别，可能是特定于供应商的：

NUMERIC must be exactly as precise as it is defined — so if you define 4 decimal places, the DB must always store 4 decimal places.

NUMERIC 必须与定义的一样精确——因此，如果您定义 4 个小数位，则 DB 必须始终存储 4 个小数位。

DECIMAL must be at leastas precise as it is defined. 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 store 1.00005instead of 1.0000, affecting future calculations.

DECIMAL 必须至少与定义的一样精确。这意味着数据库实际上可以存储比指定的更多的数字（由于幕后存储有额外数字的空间）。这意味着数据库可能会存储1.00005而不是1.0000，从而影响未来的计算。

In SQL Server Numeric is defined as being identical to Decimal in every way — both will always store only the specified number of decimal places.

在 SQL Server 中，数字被定义为在各方面都与小数相同——两者都将始终仅存储指定的小数位数。

回答by gbn

They are synonyms, no difference at all.

它们是同义词，完全没有区别。

At least on SQL Serverin the ANSI SQL standards. This SO answer showssome difference in ANSI but I suspect in implementation they are the same

至少在ANSI SQL 标准中的SQL Server上。这个SO 答案显示了 ANSI 的一些差异，但我怀疑在实现中它们是相同的

回答by Vao Tsun

Postgres:No difference

Postgres：没有区别

in documentationdescription in table 8.1 looks same, yet it is not explained why it is mentioned separately, so according to Tom Lane post

在表 8.1 的文档描述中看起来相同，但没有解释为什么单独提到它，所以根据 Tom Lane 的帖子

There isn't any difference, in Postgres. There are two type names because the SQL standard requires us to accept both names. In a quick look in the standard it appears that the only difference is this:

     17)NUMERIC specifies the data type exact numeric, with the decimal
        precision and scale specified by the <precision> and <scale>.

     18)DECIMAL specifies the data type exact numeric, with the decimal
        scale specified by the <scale> and the implementation-defined
        decimal precision equal to or greater than the value of the
        specified <precision>.

ie, for DECIMAL the implementation is allowed to allow more digits than requested to the left of the decimal point. Postgres doesn't exercise that freedom so there's no difference between these types for us.

      regards, tom lane

在 Postgres 中没有任何区别。有两种类型名称，因为 SQL 标准要求我们接受这两种名称。快速浏览一下标准，似乎唯一的区别是：

     17)NUMERIC specifies the data type exact numeric, with the decimal
        precision and scale specified by the <precision> and <scale>.

     18)DECIMAL specifies the data type exact numeric, with the decimal
        scale specified by the <scale> and the implementation-defined
        decimal precision equal to or greater than the value of the
        specified <precision>.

即，对于 DECIMAL，允许实现允许比要求的小数点左侧更多的数字。Postgres 没有行使这种自由，所以这些类型对我们来说没有区别。

      regards, tom lane

also a page lower docsstate clearly, that

还有一个页面下方的文档清楚地说明，

The types decimal and numeric are equivalent. Both types are part of the SQL standard.

类型十进制和数字是等效的。这两种类型都是 SQL 标准的一部分。

and also at aliases tabledecimal [ (p, s) ]is mentioned as alias for numeric [ (p, s) ]

并且在别名表中decimal [ (p, s) ]也提到了别名numeric [ (p, s) ]

回答by Coder Girl

They are actually equivalent, but they are independent types, and not technically synonyms, like ROWVERSION and TIMESTAMP - though they may have been referred to as synonyms in the documentation at one time. That is a slightly different meaning of synonym (e.g. they are indistinguishable except in name, not one is an alias for the other). Ironic, right?

它们实际上是等价的，但它们是独立的类型，而不是技术上的同义词，如 ROWVERSION 和 TIMESTAMP——尽管它们可能曾在文档中被称为同义词。这是同义词的略有不同的含义（例如，除了名称之外，它们无法区分，一个不是另一个的别名）。很讽刺吧？

What I interpret from the wording in MSDN is actually: These types are identical, they just have different names.

我从MSDN的措辞中解释的实际上是： 这些类型是相同的，只是名称不同。

Other than the type_id values, everything here is identical:

除了 type_id 值之外，这里的所有内容都是相同的：

SELECT * FROM sys.types WHERE name IN (N'numeric', N'decimal');

I have absolutely no knowledge of any behavioral differences between the two, and going back to SQL Server 6.5, have always treated them as 100% interchangeable.

我完全不知道两者之间的任何行为差异，回到 SQL Server 6.5，一直将它们视为 100% 可互换。

for DECIMAL(18,2) and NUMERIC(18,2)? Assigning one to the other is technically a "conversion"?

Only if you do so explicitly. You can prove this easily by creating a table and then inspecting the query plan for queries that perform explicit or - you might expect - implicit conversions. Here's a simple table:

仅当您明确这样做时。您可以通过创建一个表，然后检查执行显式或（您可能期望）隐式转换的查询的查询计划来轻松证明这一点。这是一个简单的表格：

    CREATE TABLE [dbo].[NumDec]
(
    [num] [numeric](18, 0) NULL,
    [dec] [decimal](18, 0) NULL
);

Now run these queries and capture the plan:

现在运行这些查询并捕获计划：

DECLARE @num NUMERIC(18,0);
DECLARE @dec DECIMAL(18,0);

    SELECT 
      CONVERT(DECIMAL(18,0), [num]), -- conversion
      CONVERT(NUMERIC(18,0), [dec])  -- conversion
    FROM dbo.NumDec
    UNION ALL SELECT [num],[dec] 
      FROM dbo.NumDec WHERE [num] = @dec  -- no conversion
    UNION ALL SELECT [num],[dec] 
      FROM dbo.NumDec WHERE [dec] = @num; -- no conversion

we have explicit conversions where we asked for them, but no explicit conversions where we might have expected them. Seems the optimizer is treating them as interchangeable, too.

我们在要求它们的地方有显式转换，但在我们可能期望它们的地方没有显式转换。似乎优化器也将它们视为可互换的。

Personally, I prefer to use the term DECIMAL just because it's much more accurate and descriptive. BIT is "numeric" too.

就个人而言，我更喜欢使用 DECIMAL 一词，因为它更准确和更具描述性。BIT 也是“数字”。