ALTER COLUMN TYPE from tinyInt to Varchar in Mysql

Question

I need to change column type from tinyInt(used as bool) to Varchar, without loosing data. I have found many answers on stack-overflow but all of them are written in postgres and I have no idea how to rewrite it in Mysql. Answers for this problem on stack-overflow looks like that:

ALTER TABLE mytabe ALTER mycolumn TYPE VARCHAR(10) USING CASE WHEN mycolumn=0 THEN 'Something' ELSE 'TEST' END;

How would similar logic look like in Mysql?

Answer 1

The syntax you show has no equivalent in MySQL. There's no way to modify values during an ALTER TABLE. An ALTER TABLE in MySQL will only translate values using builtin type casting. That is, an integer will be translated to the string format of that integer value, just it would in a string expression.

For MySQL, here's what you have to do:

1. Add a new column:

   ALTER TABLE mytable ADD COLUMN type2 VARCHAR(10);
   

2. Backfill that column:

   UPDATE mytable SET type2 = CASE `type` WHEN 0 THEN 'Something' ELSE 'TEST' END;
   

   If the table has millions of rows, you may have to do this in batches.

3. Drop the old column and optionally rename the new column to the name of the old one:

   ALTER TABLE mytable DROP COLUMN `type`, RENAME COLUMN type2 to `type`;
   

Another approach would be to change the column, allowing integers to convert to the string format of the integer values. Then update the strings as you want.

ALTER TABLE mytable MODIFY COLUMN `type` VARCHAR(10);

UPDATE mytable SET `type` = CASE `type` WHEN '0' THEN 'Something' ELSE 'TEST' END;

Either way, be sure to test this first on another table before trying it on your real table.