What is the equivalent of float8( e.g. in postgres) for mysql

by

I have a mysql database and i want to insert values similar to this one 12,3456.78. I tried using the float and double type but the result is rounded to 12 and using decimal is still not helping, i get 12.0. I saw in another database( postgres database) that for that type of column you can use the float8 but i dont know how to use it for a mysql database because i only have 3 options for real numbers( float, double, decimal and i wrote the outputs in the beginning).

How can i solve this without changing the database?

  • 1

    not with you why is decimal not a suitable option?

    – 

  • @P.Salmon i want the value to look like the one i put as example with dot and comma and using decimal i lost the comma

    – 

  • in that case varchar or char of some sort in mysql but you won’t be able to do arithmetic on it directly.

    – 




  • Numeric types have no commas or dots, they’re binary values. Dots and commas are only used when formatting the values as strings, or parsing strings into numbers. When that happens the developer specifies what culture, language or separator to use

    – 

  • the result is rounded to 12 this means the parsing code is incorrect, not the type. Where did the text come from, where is it being converted? That’s where the bug is, not in the type. Storing strings instead of actual numeric or date values is a very serious database bug

    – 

By default, the dot is interpreted as the decimal separator and comma as a column/value separator. As a result, you probably inserted 12 into one column and 3456.78 somewhere else: demo

create table test (f float,d decimal(65,2));
insert into test select 12,345.6;
select * from test;
f d
12 345.60

That being said, you probably want a text-to-number parsing function and a CAST() on input and number-to-text FORMAT() on output:

TRUNCATE test;

INSERT INTO test 
SELECT CAST(REPLACE('12,3456.78',',','') AS FLOAT),
       CAST(REPLACE('12,3456.78',',','') AS DECIMAL(65,2));

SELECT FORMAT(f,2) AS f, 
       FORMAT(d,2) AS d, 
       FORMAT(f,2,'de_DE') AS f_de, 
       FORMAT(d,2,'de_DE') AS d_de
FROM test;
f d f_de d_de
123,456.78 123,456.78 123.456,78 123.456,78

You haven’t shared your actual code but as pointed out @Panagiotis Kanavos, this sort of problem does look like it could have been caused by constructing a query through string interpolation. Most client libraries and ORMs offer ways to automatically and safely handle the formatting, conversion and transfer in/out/between your sources. It’s not only more secure, it’s also faster and more convenient.

in mysql we can use double

CREATE TABLE test_table (
    test_column_name DOUBLE
);

Leave a Comment