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?
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 ....