Search code examples
phpmysqlsqlalter-tablealter

MySql - Strange behavior when altering field enum type to tinyint

I'm updating all of my table fields with enum type to tinyint(1), but I'm seeing some strange behavior.

Say field_x type is enum('0','1','2');

After altering the table (ALTER TABLE a MODIFY field_x tinyint(1) NOT NULL DEFAULT 1), fields with original value 0 now have 1, fields with original value 1 now have 2...

Does anybody know what is causing this?

Solution

  • The underlying issue is the how enum is stored in mysql

    For enum('0','1','2') MySQL stores enum values internally as integer keys.

    So for above indexes are 

    0 = 1
1 = 2
2 = 3

    So when you alter the colum to tinyint then the saved values which are as string being converted to the corresponding indexes for tinyint and you are getting 

    1 for 0, 2 for 1 ....