Is it possible in MySQL in a single statement to both modify the data type of a column and update the existing data in that column so it's compatible with the new data type? Or would I need to, for example, create a new column with the new data type, then use the original column's data to update the values of the new column, and then finally drop the original column?

I want to change an int(11) to a time column where the column's original int(11) data can range from 0 to 180 which represent 5 minute timeslots from 8am-11pm. So, for example, the original int(11) data of say 0 would need to get updated to the new time data of 08:00:00, or 96 updated to 16:00:00, etc..

Obviously if I try modifying the column's data type without any "intervention" on the existing data with the following:

ALTER TABLE <table> MODIFY <column> time DEFAULT NULL

...I'm guessing MySQL will simply not allow it to happen since my old data is likely not compatible with the new data type, and it will obviously also not know how to parse the data the way I want as described above.

CodePudding user response：

No, you'll have to do this in several steps as you describe.