I am having problems storing data types in MySQL. I am storing some large numbers in a field, in the billions. Now, when i store them as floating point values, they tend to leave off the 1s, tenths, and one hundreths place. To have MySQL save the value correctly, I had to change the field to store them as char. Now, when I try to sort them, it sorts them alphanumeric. Which I understand why, but I dont want it to.

Is there anyway that I can store them as intergers/floating point values, and still keep its actually value?

Thanks in advance,

    It sounds like your DECIMAL field may not be large enough -- you get to declare a size. Why don't you run "show fields from <yourtablename>" and post that here as a CODE block.

      Code that I am using now:

      Field       Type          Null       Key     Default  Extra
      amount  char(30)                               0 
      

      Code when I change it to float:

      Field       Type          Null       Key     Default  Extra
      amount   float                                       0 
      

        If you use the word 'float', then MySQL will only store 4 bytes worth of data. Let's say you need to store numbers of -999999999999.99 to 999999999999.99 (that is, roughly 1 trillion, positive or negative, with 2 post-decimal precision). Declare it like this:

        CREATE TABLE whatever (
        [...],
        amount DECIMAL(15,2),
        [...]
        );
        

        That gives you a format of
        12.2 -- 12 digit precision before your decimal place, 2 digits after, and it can be signed. So you can have positive or negative numbers up to 999999999999.99. If you flagged it UNSIGNED, you could make it (14,2) or have it be up to 10 trillion, basically.

        I know that FLOAT (without a size declared) has a 4 byte storage, which probably means that even with the most optimal storage, a signed number will peak at around 20,000,000 with hundredths precision in the decimal, although the manual isn't clear as to how the post-decimal precision is handled, which is why I'd recommend using the DECIMAL(X,Y) notation. Incidentally, DECIMAL(9) is syntactically equivalent to DECIMAL(9,2), but it never hurts to be verbose.

        Try using the decimal example from the code block, and tell me if it stores your variable right. I remember having some trouble with decimals myself, but I think that was because when I first started off with mysql, I thought DECIMAL(7,2) was XXXXXXX.YY, instead of XXXXX.YY.

          Write a Reply...