Enum to Int Conversion in MySQL

Standard

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.

7 thoughts on “Enum to Int Conversion in MySQL”

  3. What I usually do when I convert the data type of a column: I make a new column with the right type, do an update-and-convert command over the whole table, shuffling the old data into the new column. Then once that worked, I remove the old column and rename the new one.

    That’s a step more, but it doesn’t give you bad surprises.

  5. Yeah, it also helps in case you want to have more than 2 boolean states: true, false, maybe, unsure, uncertain, certain, and pretty sure.

  7. sullivat

    Storing a boolean as an enum with two ints (well, you can only have strings in a mysql enum, but you can pretend they are ints) lets you toggle the value of the bool really cool-like:

    > create table mytable ( george enum(‘0’, ‘1’) );
    > insert into mytable values (‘0’);
    > update mytable set george = binary ( george = 1 );
    > select * from mytable;
    { ‘george’: ‘1’ }
    > update mytable set george = binary ( george = 1 );
    > select * from mytable;
    { ‘george’: ‘0’ }

    By the way, I named the column “George” in honor of George Boole, the inventor of Boolean algebra.

    To George:

    Thanks.

Comments are closed.