MySql decimal value

One of the options is to use DECIMAL( tamanho, casas ) , but I particularly recommend using INT and multiply values by 100.

But, first of all, you have to convert the value to a number with periods in place of the decimal separator, which can be done like this:

-- Usando DECIMAL(6,2) por exemplo:
INSERT INTO
   minhatabela
SET
   campodecimal = REPLACE( REPLACE( '1.500,00', '.' ,'' ), ',', '.' )

or for integers:

-- Usando INT(10) por exemplo:
INSERT INTO
   minhatabela
SET
   campointeiro100 = 100 * REPLACE( REPLACE( '1.500,00', '.' ,'' ), ',', '.' )

In this second case, you can use this syntax to retrieve the data by inverting the calculation:

SELECT campointeiro100 / 100 FROM minhatabela

The explanation is as follows:

• The internal replace changes the dots by nothing, leaving the value like this: 1500,00 ;
• the external replace changes the comma by a point, leaving the value like this: 1500.00 , which is the format that MySQL expects for numbers with decimals.
• Both INT and DECIMAL have no floating-point storage problems, and are suitable for financial / monetary use.

INT has the advantage of being much simplified, and DECIMAL has the advantage of appearing formatted on the screen in the desired way, but only using SQL query tools.

Because it is usually done through an application, it is much more interesting to leave the screen formatting to the application, so INT usually ends up being more advantageous in terms of space and speed. Remember that in this case, REPLACE and * 100 can already be done directly on the application side, by a proper function, leaving the use of INT transparent.

On the other hand, DECIMAL gives less work if you need to change the number of homes in the future. Question to apply the most convenient for the practical case.

In MySQL we have some data types to store decimal numbers:

FLOAT and DOUBLE

Both represent numbers with floating points.

The difference between the two is in their precision and the size in bytes of each. The FLOAT has an accuracy of approximately 7 decimal places, with DOUBLE up to 14, which results in double disk size compared to FLOAT .

DECIMAL

Decimal-type numbers store a decimal number with exact precision .

In MySQL, a decimal type can store up to 65 digits, up to 30 digits after the decimal place.

But anyway, which one do you use?

It depends on your application. For applications that work with monetary values, precision is , and DECIMAL is more secure.

For applications where precision is not important, or scientific calculations, go from FLOAT or DOUBLE .

Remember that MySQL works with numbers in the US standard. It is therefore necessary to convert , to . as indicated in this response .

Type DECIMAL(7,2) The quickest and easiest way.

2 indicates decimal places.

The 7 indicates the maximum numeric quantity before the comma.

Store the number as decimal so that you do not lose decimal places in monetary values. To insert / update do a conversion of the formatted number 1.500,00 first remove all points and finally replace the comma by point at the end it will look like this: 1500.00 . In a select you can use the code below

SELECT concat('R$ ', format(15000, 2)) as valor;

output:

R$ 15,000.00

Example

Based on:

mysql - format ()

SOen