0
  • tableName: tb_assets
  • columnName: free
  • columnType: decimal(32,16)
  • sql-1: update tb_assets set free = 16224.0221121221736466 where id=6;
  • sql-2: update tb_assets set free = free + '100000000' where id=6;
  • after sql-1: fee: 16224.0221121221736466
  • after sql-1&sql-2: fee: 100016224.0221121200000000
  • compare: fee: 16224.0221121221736466 fee: 100016224.0221121200000000 100016224(after sql-2 executed,Integer part.loss precision with integer's digits - 1)

please help me understand why the decimal part will lose precision.

Explanation about why a decimal type column added with a string in Mysql, it will lose precision.

I wanna know what is the detail when execute the sql "update tb_assets set free = free + '100000000' where id=6;"

Youga Tang
  • 11
  • Exactly what problem are you tackling that requires such an enormous range of precision? – Tangentially Perpendicular Aug 08 '23 at 03:42
  • Problem is reproduced here: https://dbfiddle.uk/Y0rm7T-4 My guess is that there is a maximum precision when handling numbers as strings - but I don't know if this is true or documented. Note: if you explicitly cast that string to decimal(32,16) the loss of precision does not occur. – Paul Maxwell Aug 08 '23 at 03:53
  • see: https://stackoverflow.com/a/55329711/2067753 and https://dbfiddle.uk/5z6nbo0k when you add the string you restrict the result to double precision. So either don't add strings or cast the string to decimal(32,16) or signed if an integer – Paul Maxwell Aug 08 '23 at 04:08

0 Answers0