Storing 0.5 in MySQL

Question

Using a decimal (I have tried variations of it) 0.5 is always converted to 5.

I can store 1.5, etc, no problem... Just curious on how I need to set up my data type to correctly store a '0.5' number.

Thanks

EDIT:

The current data type is Decimal (3,1). I have tried float as well.

I simply retreive the number through $_POST from a Form text box which is restricted to numbers only through Java. I never considered that maybe the browser is simply sending it as a 5, I don't know.

EDIT 2: I have confirmed through echo that the 0.5 is sent fine, it's once it gets into the database that it becomes 5

Answer 1

You know, I might get voted down for this, but I've always had success (as a sort of hack) in storing values like this as a VARCHAR and then using format() function in PHP when I need to make calculations. MySQL still seems to parse it well enough for SQL functions as well. Just my 2 cents :)

Answer 2

I used a decimal value in a database with various inputs:

I have tried to recreate you issue, but I get the following:

mysql> create table test(myDec decimal(2,2));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test values(0.5);
Query OK, 1 row affected (0.01 sec)
mysql> insert into test values(.5);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+-------+
| myDec |
+-------+
|  0.50 |
|  0.50 |
+-------+
2 rows in set (0.00 sec)

Given your edit, I think that you might be getting values from international visitors, where the decimal point might be formatted using a comma instead of a dot?

mysql> insert into test values('0,5');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from test;
+-------+
| myDec |
+-------+
|  0.50 |
|  0.50 |
|  0.00 |
+-------+
3 rows in set (0.00 sec)