Search code examples
mysqlenumscharvarchar

Convert varchar/char to enum

Is there a fast easy way to convert a varchar/char to an enum? I tried to do this but it doesn't work

alter table zipcodes add column state_short_enum enum(
    (select state_short from zipcodes group by state_short)
)

I get this error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near 
'(select state_short from zipcodes group by state_short))' at line 1
Solution

  • An enumeration value must be a literal thus you can't use user variables or scalar subquery. Probably you will have to create ALTER statement as a string.

    Example:

    SET @codes := CONCAT((SELECT GROUP_CONCAT(CONCAT("'", state_short, "'")) FROM zipcodes), "'");
SET @alter_sql := CONCAT('ALTER TABLE `zipcodes` ADD `state_short_enum` ENUM(', @codes, ')');
PREPARE stmt FROM @alter_sql;
EXECUTE stmt;