MySQL: миграция с ENUM на VARCHAR

Перед началом работ необходимо сделать бэкап.

Итак, есть поле таблицы:

`status` enum('new','suspended','complete') default NULL,

Напрямую, насколько я понял, конвертировать его в VARCHAR нельзя. Поэтому пойдем по пути через промежуточное поле, создаем его:

alter table mod_ressrvlist add newstatus varchar(255) not null;

Итого, у нас теперь два поля в табоице:

`status` enum('new','suspended','complete') default NULL,
`newstatus` varchar(255) NOT NULL,

Итак, сейчас у нас следующее содержимое таблиц:

select status, newstatus from sometable;
+-----------+-----------+
| status | newstatus |
+-----------+-----------+
| new | |
| suspended | |
| new | |
| new | |
| suspended | |
| new | |
| new | |
| new | |
| new | |
| new | |
| new | |
| suspended | |
| suspended | |
| new | |
| suspended | |
| suspended | |
| suspended | |
| new | |
| new | |
| new | |
| new | |
| suspended | |
| new | |
| new | |
| new | |
| new | |
| new | |
| new | |
| new | |
| new | |
| suspended | |
| suspended | |
| new | |
| new | |
| new | |
| new | |
| new | |
| suspended | |
| new | |
| suspended | |
| new | |
| new | |
| suspended | |
| new | |
| new | |
+-----------+-----------+

Копируем значения:

update sometable set newstatus = status;

Итого:

sql> select status, newstatus from mod_ressrvlist;
+-----------+-----------+
| status | newstatus |
+-----------+-----------+
| new | new |
| suspended | suspended |
| new | new |
| new | new |
| suspended | suspended |
| new | new |
| new | new |
| new | new |
| new | new |
| new | new |
| new | new |
| suspended | suspended |
| suspended | suspended |
| new | new |
| suspended | suspended |
| suspended | suspended |
| suspended | suspended |
| new | new |
| new | new |
| new | new |
| new | new |
| suspended | suspended |
| new | new |
| new | new |
| new | new |
| new | new |
| new | new |
| new | new |
| new | new |
| new | new |
| suspended | suspended |
| suspended | suspended |
| new | new |
| new | new |
| new | new |
| new | new |
| new | new |
| suspended | suspended |
| new | new |
| suspended | suspended |
| new | new |
| new | new |
| suspended | suspended |
| new | new |
| new | new |
+-----------+-----------+

После этого переименовываем старую таблицу:

ALTER TABLE sometable CHANGE status oldstatus enum('new','suspended','complete') default NULL;

Переименовываем новую в требуемое имя:

ALTER TABLE sometable CHANGE newstatus status varchar(255) NOT NULL;

Стираем старый филд с enum:

alter table sometable drop oldstatus;

Првоеряем новый тип поля:

`status` varchar(255) NOT NULL,

Делаем контрольную выборку:

select status from sometable;
+-----------+
| status |
+-----------+
| new |
| suspended |
| new |
| new |
| suspended |
| new |
| new |
| new |
| new |
| new |
| new |
| suspended |
| suspended |
| new |
| suspended |
| suspended |
| suspended |
| new |
| new |
| new |
| new |
| suspended |
| new |
| new |
| new |
| new |
| new |
| new |
| new |
| new |
| suspended |
| suspended |
| new |
| new |
| new |
| new |
| new |
| suspended |
| new |
| suspended |
| new |
| new |
| suspended |
| new |
| new |
+-----------+

Все! :)