如何防止PHP舍入一个字符串（一个大小数）？

Question

I have the following scenario:

    $amount = '1000000000.00000001';
    //echo $amount;

    $query = $mysqli->prepare("UPDATE `wallets` SET `dollars` = `dollars` + ?   WHERE `userId` = ?");        
    $query->bind_param('si', $amount, $id);
    $query->execute();

The column dollars in the table is DECIMAL(18,8).

Now the problem is that if I execute this query and the current value of dollars is 0, then the new value in the table will be 1000000000.00000000. As you can see, the .00000001 got lost in the ether and my question is why?

I am specifically preventing this from happening by binding the $amount as a String and not as an Integer, yet it still gets converted to float (i assume) somewhere and cut off.

If I do this instead:

    $query = $mysqli->prepare("UPDATE `wallets` SET `dollars` = `dollars` + 1000000000.00000001   WHERE `userId` = ?");        
    $query->bind_param('i', $id);
    $query->execute();

Then it works perfectly fine and the table will contain the expected value 1000000000.00000001

So why is the $amount treated as a number, even though I am treating it as a string to make sure PHP won't screw with it?

How can I fix it?

Answer 1

If you use a string in the place where MySQL expects a number (because you add it to a number), it will autocast it to a float (or integer).

It has nothing to do with the parsing in php, since you want to parse it as a string. Parsing as a string means that php will add ' around it. So the explicit query you should compare the behaviour with is

 UPDATE `wallets` SET `dollars` = `dollars` + '1000000000.00000001'   
 WHERE `userId` = ?

and this will behave the same way as the parsed one (because, well, it is exactly what the parsing will produce).

So tell MySQL explicitely to cast the string as a decimal to prevent MySQL from doing autocasting:

UPDATE wallets SET dollars = dollars + cast((?) as decimal(18,8)) 
WHERE userId = ?

Another way would be to paste it directly (... SET dollars = dollars + $amount where ...), but that is generally a bad idea.