Blog | Status Updates | About | Contact | Advertise

October 27, 2006 / Filed under: MySQL

Often in your database development work, you'll come across a situation where a particular column type no longer "fits" the type of data that needs to be stored in it.

For example, let's say you're storing dollar amounts in an integer field, which can't hold decimal values.

Later on, someone decides they need to store decimal values in that field, but can't.

You need to convert that integer field to a float field, but you don't want to touch any existing data in that field. You want the existing data to remain the way it is, and only start accommodating decimal values.

Rather than just changing the field type directly, and hoping that no data is lost or converted to something else - the best (and safest) way to accomplish this is to create a new field, and copy the old values over.

UPDATE table SET newField = primaryField;

This query copies all of the values from primaryField to newField.

Then, remove primaryField entirely, and rename newField to primaryField.

Sometimes it helps to only rename primaryField to something like primaryField_old, in case it needs to be restored immediately, for whatever reason.