MySQL add fields to an Enum

Question

I have to add some enum options to a database table. The problem being, I will have to do this to several tables & databases, and they may not all have the same enum data type. Is there a why to write an alter table query or something similar to append options to a enum data type?

If there is no way to do this purely in MySQL, how would you approach this problem with a PHP script?

If you are using the same ENUM values across multiple tables, it might be worthwhile to create a table with those values and use foreign keys to refrence to the enum names. This way you can maintain the ENUMs in a single location. This solution is truely situational though, so I'd want to hear more about what youa re trying to accomplish and what the enums are for before I'd say "go for it". — Kevin Peno
– Kevin Peno, Commented Oct 30, 2009 at 19:33
it mainly because of the multiple databases. They are all instances of a CMS, but some have custom additions, and some do not, therefore, I can't just use a blanket alter statement for all of them. Also, I can see the benefits of storing these values in a seperate table, but that isn't really an option due to the amount of code that would have to be changed. — GSto
– GSto, Commented Oct 30, 2009 at 19:55

longneck · Accepted Answer · 2009-10-30 20:04:54Z

2

there is not easy way to append enum values.

this is ugly and untested, but i think it will give you an idea:

<?php

$sql = "select table_schema
             , table_name
             , column_name
             , column_type
             , is_nullable
             , column_default
          from information_schema
         where data_type = 'enum'";

$res = mysql_query($sql);

while ($row = mysql_fetch_assoc($res)) {

  // these are important -->       leading comma --v      close paren --v
  $new_enum = substr($row['column_type', 0, -1) . ",'new','enums','here')"

  $sql = "alter table `{$row['table_schema']}`.`{$row['table_name']}`";
  $sql .= " modify column `{$row['column_name']}`";
  $sql .= " $new_enum";
  $sql .= ($row['is_nullable'] == 'YES') ? " NULL" : " NOT NULL";
  $sql .= " default $row['column_default']";

  mysql_query($sql);
}

you could probably do this "purely in mysql" with a stored procedure, but that's more than i can wrap my brain around right now. :)

answered Oct 30, 2009 at 20:04

longneck

12.3k3 gold badges39 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Crusader Over a year ago

SHOW COLUMNS FROM $table LIKE '$field' is better.

Collectives™ on Stack Overflow

MySQL add fields to an Enum

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related