Difference between decimal and Numeric

Question

The two types of data, DECIMAL and NUMERIC, are/can be used to store exact values.

For example:

Is there any difference between Salario DECIMAL(5,2) and Salario NUMERIC(5,2) in Mysql?

Is there any situation that requires the programmer/DBA to use one or the other?

Answer 1

For Mysql they are synonymous. The SQL standard defines that there is a difference between them but Mysql has simplified this and only the behavior of NUMERIC has been implemented. So it’s a matter of liking to use one name or the other.

Some people will say to use the NUMERIC when defining a specific number of digits and DECIMAL when you want to set a minimum number of digits. Mysql will not respect this but moving to another database will be right. But there are so many other complicated things to reconcile and if you change banks the least of your worries is syntax. It’s actually probably worse to do this because if you switch banks doing this will probably change the semantics and the results may change. So if you are concerned about a possible future change of DB provider and considering that Mysql only implements the semantics of NUMERIC, use only him. But I reinforce that this will be the least of your problems if you want to change supplier.

If thinking of portability also avoid using DEC or FIXED which are other synonyms.

To documentation says that truncation depends on the operating system. This is a reason to be careful with its use. Other relevant information in that chapter.

I know that some programmers prefer a use an integer by multiplying the decimal places. That is, if you will work with two pennies the recorded value is always times 100 to maintain the scale. Applications that access the bank should know how to handle this.

Answer 2

According to the SQL2003 standard (§6.1 Data Types), whose relevant excerpt you can see transcribed in that reply in Soen (the standard in itself is not free to consult, needing to be purchased), establishes a small difference between these two types:

• The NUMERIC needs to have the precision exact specified as well as the scale;
• The DECIMAL need to have at least the specified accuracy, and the identical scale.

In other words, if an implementation chooses to represent the DECIMAL with greater precision than requested, she is free to do so. But if she wants to implement it with exact accuracy - making this kind functionally equivalent to NUMERIC - This is also in line with the standard.

Mysql - like pointed out by bfavaretto in the comments - is one that does not distinguish between types (SQL Server is another). According to documentation, in Mysql "NUMERIC is implemented as DECIMAL", so that its behavior is identical. And, as required by the standard, the precision used is exactly the requested.

About using one or the other, I don’t have enough experience to comment, but Maniero’s argument in your answer that the use of DECIMAL can harm portability (i.e. potentially lead to different results when the bank is migrated from one DBMS to another) is already a good reason, in my opinion, to avoid this type.

Answer 3

NUMERIC and DECIMAL types are implemented as the same type by Mysql, as permitted by the SQL92 standard. They are used for values for which it is important to preserve accuracy such as monetary data. When a field of any of these types is declared the accuracy and scale can be (and normally is) specified

Below are some links that explain well about these types of data:

[http://ftp.nchu.edu.tw/MySQL/doc/refman/4.1/pt/numeric-types.html] [http://www.rcoli.com.br/2012/08/tipos-de-campos-no-mysql-saiba-como-escolher-o-tipo-correto/]