CODEWITHSUNDEEP
mysqlsql-updatedecimal
Hamid Naghipour
Hamid Naghipour

Reputation: 3625

losing decimal precision in string filed when updating MySQL table

I have a filed with varchar(255) data type. when I run this query :

update sample_tbl 
set in_order_real = in_order_real + 21.215541764099030466 
where id = 15

it removes extra decimals.

for example in_order_real number is 0 and when I run this query it keeps only some decimals and round up while it is a string field. how can i fix this issue?

Upvotes: 1

Views: 559

Answers (2)

Rick James
Rick James

Reputation: 142296

FLOAT holds 24 significant bits -- about 7 decimal digits of significance.

DOUBLE: 53 and 16

So, putting 21.215541764099030466 into a FLOAT will garble the digits after about 7 digits: ~21.21554. For DOUBLE: ~21.21554176409903

Can't do arithmetic in VARCHARs; it is converted either to DOUBLE or DECIMAL.

Where do your numbers come from? For money, use DECIMAL(..., 2); for sensor readings, use FLOAT.

Upvotes: 0

Akina
Akina

Reputation: 42642

You must cast your string value to DECIMAL with proper precision.

CREATE TABLE sample_tbl (id INT, in_order_real VARCHAR(255))
SELECT 15 id, 1.23 in_order_real
UNION ALL
SELECT 16, 1.23;

SELECT * FROM sample_tbl;
id in_order_real
15 1.23
16 1.23 
update sample_tbl 
set in_order_real = in_order_real + 21.215541764099030466 
where id = 15;

update sample_tbl 
set in_order_real = CAST(in_order_real AS DECIMAL(30, 18)) + 21.215541764099030466 
where id = 16;

SELECT * FROM sample_tbl;
id in_order_real
15 22.44554176409903
16 22.445541764099030466

db<>fiddle here

If you don't know actual decimal digits amount then use maximal possible value. Additionally you may trim trailing zeros in the result.

Upvotes: 3

Related Questions