Mysql decimal value

Question

I am trying to insert decimal value in mysql database, wanted to put these values for example:

1.500,00

Thousand, hundred and ten

What is the type of column I must declare?

Answer 1

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

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

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

or for whole:

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

and in this second case, you can use this syntax to retrieve the data by reversing the calculation:

SELECT campointeiro100 / 100 FROM minhatabela

The explanation is as follows:

• The internal replace swaps the points for nothing, leaving the value like this: 1500,00;
• the external replace changes the comma by point, leaving the value like this: 1500.00, which is the format Mysql expects for decimal numbers.
• Both INT and DECIMAL have no floating point storage problems, and are suitable for financial/monetary use.

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

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

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

Answer 2

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

The 2 indicates decimal places.

The 7 indicates maximum numerical quantity before comma.

Answer 3

In Mysql we have some types of data to store decimal numbers:

FLOAT and DOUBLE

Both represent numbers with floating points.

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

DECIMAL

Decimal numbers store a decimal number with exact precision.

In Mysql a decimal type can store a maximum of 65 digits, up to 30 digits after decimal place.

But after all, which of the guys to use?

It depends on your application. For applications working with monetary values the accuracy is very important,DECIMAL is safer.

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

Remember that Mysql works with numbers in the American standard. Soon you need to convert , for . as indicated in that reply.

Answer 4

Store the number as decimal to not lose the accuracy of decimals in monetary values. To insert/update make a formatted number conversion 1.500,00 first remove all the dots and finally replace the comma by dot 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;

exit:

R$ 15,000.00

Example

Based on:

mysql - format()

Soen

Answer 5

Use the Mysql Decimal value with its value indicated as 10.2

decimal(10,2)