Mysql change column data type from integer to decimal(3,2) without loosing old data

Question

I have a price column with the type of int(10) unsigned, but for some reason, I need to change the type of column to decimal(3,2), how can I do this without losing data stored already with int(10)?

ALTER TABLE packages MODIFY price  decimal(3,2)

but this command changes all value to 9,99 ?

how can i do this with minimum pain?

Answer 1

Your prices probably get floored down to the maximum allowed decimal you've given. Try:

DELIMITER $$

CREATE PROCEDURE statement(IN dynamic_statement TEXT)
BEGIN
      SET @dynamic_statement := dynamic_statement;
      PREPARE prepared_statement FROM @dynamic_statement;
      EXECUTE prepared_statement;
      DEALLOCATE PREPARE prepared_statement;
  END;

DELIMITER ;

SET @var_digit_length :=
(SELECT MAX(CEIL(LOG(10, ABS(price))))
FROM packages)
;

SET @var_precision := 2;

CALL statement(CONCAT('
    ALTER TABLE packages MODIFY price decimal(',
    (@var_digit_length + @var_precision), ',', @var_precision, ');
'));



DROP PROCEDURE statement;

Answer 2

Simply convert datat type by this sql scripts if you don't need to backup and/or just want to data type:

/* Convert column data type */

ALTER TABLE PriceTable MODIFY COLUMN Price_column decimal(3, 2);