Probably simple, changing from ENUM to TINYINT

Forum Moderators: open

Message Too Old, No Replies

Probably simple, changing from ENUM to TINYINT

csdude55

7:49 am on Mar 1, 2020 (gmt 0)

This is probably pretty simple, but since it's a live table I wanted to talk it out before I screw something up.

I have a column that was originally set to ENUM('high', 'low'). Since it just has the two options, I wanted to change it to TINYINT with either 0 (in place of 'high') or 1 (in place of 'low') to make things a little smaller and faster.

First I changed it to ENUM('high', 'low', '0', '1') (it wouldn't let me use 0, 1 without the quotes). Then I changed the values using:

UPDATE table SET col='0' WHERE col='high';
UPDATE table SET col='1' WHERE col='low';

Then I started thinking about the string vs. integer thing and got nervous, so I copied it to a backup table for testing. In that backup I changed it to TINYINT(1), and it LOOKS like it converted all of the '0' to 1 and all of the '1' to 2.

Well, that was unexpected! LOL

Am I missing something here, can I not use 0 as a value in TINYINT? I can use 1 and 2 instead of 0 and 1, but I'll need to change the programs again.

lammert

9:15 am on Mar 1, 2020 (gmt 0)

In the internals of the database, type ENUM isn't stored as literal values, but as an index to an array with the string representation of the values. When you convert from ENUM to TINYINT, that index value is used for the conversion, not the string value.

I would create a temporary field of type TINYINT for this conversion

ALTER TABLE your_table` ADD `col_new` TINYINT NOT NULL DEFAULT '0' AFTER `col`;
UPDATE `your_table` SET col_new='1' WHERE col='low';
ALTER TABLE `your_table` DROP `col`;
ALTER TABLE `your_table` CHANGE `col_new` `col` TINYINT NOT NULL;

<added>
Internally the representation of an ENUM is a one-byte value if there are less than 256 values, so MySQL already stores your ENUM internally as TINYINT. All you are trying to do now is replacing it with the same type with the drawback that your program becomes less readable.

csdude55

6:58 pm on Mar 1, 2020 (gmt 0)

Internally the representation of an ENUM is a one-byte value if there are less than 256 values, so MySQL already stores your ENUM internally as TINYINT. All you are trying to do now is replacing it with the same type with the drawback that your program becomes less readable.

Oh. Well, ain't that some crap...