Mysql – How to change a column type from SET to an ENUM

alter-tableenumMySQL

I have a table with a SET column type that need to be changed to ENUM type.
Can I do it with a simple ALTER or will this mess up my data?
NB: there are no multiple values (a,b,c) in the table.

Best Answer

Technicaly YES, I just did the test :

mysql> CREATE TABLE my_t (my_c SET("one","two"));
Query OK, 0 rows affected (0.44 sec)

mysql> INSERT INTO my_t VALUES ("one");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO my_t VALUES ("one");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO my_t VALUES ("one");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO my_t VALUES ("two");
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM my_t;
+------+
| my_c |
+------+
| one  |
| one  |
| one  |
| two  |
+------+
4 rows in set (0.00 sec)

mysql> ALTER TABLE my_t MODIFY my_c ENUM("one","two");
Query OK, 4 rows affected (1.13 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM my_t;
+------+
| my_c |
+------+
| one  |
| one  |
| one  |
| two  |
+------+
4 rows in set (0.00 sec)

You've mentioned it but it will mess if you have multiple values (replace by an empty set in ENUM):

mysql> CREATE TABLE my_t (my_c SET("one","two"));
Query OK, 0 rows affected (0.15 sec)

mysql> INSERT INTO my_t VALUES ("two,one");
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM my_t;
+---------+
| my_c    |
+---------+
| one,two |
+---------+
1 row in set (0.00 sec)

mysql> ALTER TABLE my_t MODIFY my_c ENUM("one","two");
Query OK, 1 row affected, 1 warning (0.87 sec)
Records: 1  Duplicates: 0  Warnings: 1

mysql> SELECT * FROM my_t;
+------+
| my_c |
+------+
|      |
+------+
1 row in set (0.00 sec)

Max.

Related Question