MYSQL column only showing rounded values

Beruni 0
Viewed 22

I have created a table in mysql as follows:

create table scores (id tinyint, score tinyint);
insert into scores VALUES (1, 3.50), (2, 3.65),(3, 4.00),(4, 3.85);

but when I am displaying the values, the score column shows all the values as 4.00. I have changed the datatype of the 'score' column to decimal(10,2), float and double(10,2) but with no avail.

How can I solve this,

Thanks

rounding mysql
1 Answers

You can't solve this problem without re-inserting the original data into the table because the information about the fractions was never stored in the database.

Obviously, the solution is to use an appropriate dataype. Either you ALTER the TABLE or you reCREATE it, but in any case you'll have to insert the data again:

Approach #1: ALTER TABLE

create table scores (id tinyint, score tinyint);
insert into scores VALUES (1, 3.50), (2, 3.65),(3, 4.00),(4, 3.85);
ALTER TABLE scores MODIFY score decimal(10,2);
DELETE FROM scores;
insert into scores VALUES (1, 3.50), (2, 3.65),(3, 4.00),(4, 3.85);

Approach #2: recreate the table

DROP TABLE scores;
create table scores (id tinyint, score decimal(10,2));
insert into scores VALUES (1, 3.50), (2, 3.65),(3, 4.00),(4, 3.85);

Advantage of #1 over #2: you can delete and re-insert subsets of the table's data by adding a WHERE clause to the DELETE statement. You don't need to define all indexes or constraints, again. Advantage of #2 over #1: it's simple in this case.

Mihe0
Related