How do I preserve data when changing column datatypes in MySQL?

Question

For example, suppose I have a TINYINT column that I want to change into an ENUM. Is it possible to write a MySQL query that changes the datatype of the column while mapping the existing data so that (for example) 0 becomes No, 1 becomes Yes, and 2 becomes Maybe?

Answer 1

create the new column next to the old column, use an update to move the data over, then delete the original column.

Answer 2

I'm not sure if this is possible in MySQL, but an alternative solution would be to:

1. Add the new column with the new type with a new name
2. Set the new column values (given the old column values)
3. Drop the old column
4. Rename the new column to have the same name as the old column