MySQL Datatypes – Biggest Possible Double/Float for a Column

database-designdatatypesfloating pointMySQL

I am developing a PHP application in which I am in need to store values bigger than 1 million, but with the flexibility of having floating points. I know the traditional float and double. I am looking to store only up to two decimal points, and will only handle operations with numbers that have 2 decimals exclusively.

I have chosen my column type of double and unsigned, but whenever I store a row with a value bigger than one million, it gets truncated to 999999.99

Why is this? I am looking for the right datatype/solution. If you could point me in the right direction, I will appreciate it a lot!

Thank you for your time in advance!

Cheers.

Best Answer

Do not use (m,n) on the end of FLOAT or DOUBLE. That causes a rounding (at the bottom) or a truncation (at the top). If you want (m,n), you probably should use DECIMAL(m,n).

FLOAT stores 24 significant bits of data (equivalent to about 7 decimal digits; storage=4 bytes), with an exponent ranging over about 10 ** +/-38.

DOUBLE stores 53 bits (about 16 digits, 8 bytes) and has a huge range.

Your question is vague -- are you storing "money"? If so, then you really should use DECIMAL(m,2). m=8 lets you store up to a million dollars (or Euros, etc). But m can be as big as you want. (Remember to subtract the 2 before seeing how big the numbers can be.) (14,2) would store up to a trillion dollars, with precision to the penny. It will take 8 bytes of storage.

BAD NEWS : I do not think the value will be preserved !!!

I wrote this post (July 25, 2011) showing how sensitive floating point numbers are to conversions.

GOOD NEWS : Here is how you can safely compare the data conversion:

If your table has these characteristics

table is called moneytable
field you wish to convert is called moneyfield
primary key is called moneyid

Then run these commands:

DROP TABLE IF EXISTS moneytabletest;
CREATE TABLE moneytabletest LIKE moneytable;
ALTER TABLE moneytabletest MODIFY moneyfield fixed(10,2);
INSERT INTO moneytabletest SELECT * FROM moneytable;
SELECT A.moneyid,A.moneyfield olddata,B.moneyfield newdata
FROM moneytable A LEFT JOIN moneytabletest B USING (moneyid)
WHERE A.moneyfield <> B.moneyfield;

Here is the bottom line: If any rows come back from the LEFT JOIN query, the conversion will be bad. You will have to modify the definition of moneyfield, reload the moneytabletest table, and run the LEFT JOIN query again and again until no rows come back from the LEFT JOIN query. Once zero(0) rows come back, you will know what conversion will be safe.

Give it a Try !!!

Mysql – How to prepare a date and double for a thesql replace statement

Here is the output of the NOW() function

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2012-04-24 22:07:47 |
+---------------------+
1 row in set (0.03 sec)

mysql>

This is the format to enter mydate. For a double, just append .0 to any integer. No need for numbers to to have quotes:

REPLACE into mytable (CU, DA, VA) values('ABC', '2012-04-24 22:07:47', 23.0);

Best Answer

Related Solutions

MySQL conversion from float (10,2) to fixed(10,2)

BAD NEWS : I do not think the value will be preserved !!!

GOOD NEWS : Here is how you can safely compare the data conversion:

Mysql – How to prepare a date and double for a thesql replace statement

Related Question