Difference between decimal and numeric

5

The two data types, 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 a situation that forces the programmer / DBA to use one or the other?

    
asked by anonymous 13.03.2015 / 03:28

3 answers

3

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

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

In other words, if an implementation chooses to represent DECIMAL more accurately than requested, it is free to do so. But if it wants to implement it with the exact precision - making this type functionally equivalent to NUMERIC - this also conforms to the default.

MySQL - as pointed out by bfavaretto in the comments

a> - is one of those 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 accuracy used is exactly as requested.

About using one or the other, I do not have enough experience to comment, but the bigown argument in your answer that the use of DECIMAL can impair portability (ie potentially causing different results when the database is migrated from one DBMS to another) is already a good reason, in my opinion, to avoid such a type.

    
13.03.2015 / 12:22
6

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 taste to use one name or the other.

Some people will tell you to use NUMERIC when you want to set 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 fine. But there are so many other things that are more complicated to reconcile, and if you change banks, the least of the worries is the syntax. In fact, it is likely to be worse to do this because changing banks by doing this will likely change the semantics and the results may change. So if you are worried about a possible future DB provider change and considering that MySQL only implements the semantics of NUMERIC , use only it. But reinforcing that will be the least of your problems if you want to change vendor.

If you think about portability also avoid using DEC or FIXED which are other synonyms.

The documentation says truncation depends on the operating system. This is a reason to be careful about its use. Other relevant information in this chapter .

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

    
13.03.2015 / 12:10
2
  

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

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

[ link [ link

    
13.03.2015 / 12:16