Procedure in converting int to decimal data type?

Question

I have an int(11) column which is used to store money. I read some of the answers on SO and it seems I just need to update it to be a decimal (19,4) data type.

Are there any gotchas I should know about before I actually do the converting? My application is in PHP/Zend and I'm not using an ORM so I doubt I would need to update any sort of class to consistently identify the data type.

Answer 1

    mysql> CREATE TABLE t1 (
    -> id INT NOT NULL AUTO_INCREMENT,
    -> money INT(11) NOT NULL,
    -> PRIMARY KEY(id))
    -> ENGINE = MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql> DESCRIBE t1;
+-------+---------+------+-----+---------+----------------
| Field | Type    | Null | Key | Default | Extra
+-------+---------+------+-----+---------+----------------
| id    | int(11) | NO   | PRI | NULL    | auto_increment
| money | int(11) | NO   |     | NULL    |
+-------+---------+------+-----+---------+----------------
2 rows in set (0.01 sec)

mysql> SELECT * FROM t1;
Empty set (0.00 sec)

mysql> INSERT INTO t1 (money) VALUES (`10.01`);
ERROR 1054 (42S22): Unknown column '10.01' in 'field list'
mysql> INSERT INTO t1 (money) VALUES ('10.01');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1;
+----+-------+
| id | money |
+----+-------+
|  1 |    10 |
+----+-------+
1 row in set (0.00 sec)

mysql> ALTER TABLE t1 MODIFY COLUMN money DECIMAL(19,4);
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t1;
+----+---------+
| id | money   |
+----+---------+
|  1 | 10.0000 |
+----+---------+
1 row in set (0.00 sec)

mysql> INSERT INTO t1 (money) VALUES ('10.12');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1;
+----+---------+
| id | money   |
+----+---------+
|  1 | 10.0000 |
|  2 | 10.1200 |
+----+---------+
2 rows in set (0.00 sec)

---

You don't have any problem with dd when you alter int to decimal. But you should review your PHP code to ensure that your code works with decimal value correctly.