How can I store a number with a leading zero in mysql database?-CodePudding

I am trying to store the number 0.0015 in the database. I have tried float, integer but I am getting zero not the exact figure I have entered. Is there a datatype which can store such a value?

CodePudding user response：

Normally you'd use DECIMAL (aka NUMERIC), with a specified scale and precision, here are the docs for it. FLOAT should also work, but you need to be aware of floating point arithmetics quirks, so DECIMAL is preferred.

Here's a dbfiddle example

If you see your data as 0 then it's either an issue with how you're inserting (maybe importing from a file) or your client rounds it down to 0 and you need to tweak it. As you can see from the dbfiddle above, it works perfectly fine.

CodePudding user response：

This number (0.0015) is not representable in binary. See the following example in python:

Python 3.10.2 (tags/v3.10.2:a58ebcc, Jan 17 2022, 14:12:15) [MSC v.1929 64 bit (AMD64)] on win32
>>> x = 0.0015
>>> (x   1)  - 1
0.0015000000000000568

This means that the storing in mysql (or any other language that converts the number to binary) will show up representation errors. You can use numeric types that doesn't do any conversion to binary, like decimal or numeric.