morgamic.com stuff and things, according to Mike Morgan

Enum to Int Conversion in MySQL

Thought I'd post this since it was interesting. If you convert an enum to an int (don't ask) be aware of the following conversion:

mysql> create table foo ( bar enum('0','1') not null default '0' );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into foo values('0');
Query OK, 1 row affected (0.00 sec)

mysql> insert into foo values('1');
Query OK, 1 row affected (0.00 sec)

mysql> select * from foo;
+-----+
| bar |
+-----+
| 0   |
| 1   |
+-----+
2 rows in set (0.00 sec)

mysql> alter table foo change bar bar tinyint(1) not null default 0;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from foo;
+-----+
| bar |
+-----+
|   1 |
|   2 |
+-----+
2 rows in set (0.00 sec)

MySQL will assign int values corresponding to the non-zero-index of the enum. So, after the migration/conversion you can then use:

mysql> update foo set bar=bar-1;

Or, if you had a more complex enum you could update all corresponding indexes with their correct int values. At least they remain unique so you can adjust.

SQL is fun. For me to poop on.