How do I add more members to my ENUM-type column in MySQL

Question

The MySQL reference manual does not provide a clearcut example on how to do this   I have an ENUM-type column of country names that I need to add more countries to  What is the correct MySQL syntax to achieve this   Here s my attempt   ALTER TABLE carmake CHANGE country country ENUM  Sweden   Malaysia      The error I get is  ERROR 1265  01000   Data truncated for column  country  at row 1   The country column is the ENUM-type column in the above-statement   SHOW CREATE TABLE OUTPUT   mysql gt  SHOW CREATE TABLE carmake   --------- ---------------------------------------------------------------------    Table     Create Table  --------- ---------------------------------------------------------------------    carmake   CREATE TABLE  carmake     carmake id  tinyint 4  NOT NULL AUTO INCREMENT   name  tinytext   country  enum  Japan   USA   England   Australia   Germany   France   Italy   Spain   Czech Republic   China   South Korea   India   DEFAULT NULL  PRIMARY KEY   carmake id    KEY  name    name  3     ENGINE InnoDB AUTO INCREMENT 49 DEFAULT CHARSET latin1    --------- ---------------------------------------------------------------------  1 row in set  0 00 sec     SELECT DISTINCT country FROM carmake OUTPUT    ----------------    country           ----------------    Italy              Germany            England            USA                France             South Korea        NULL               Australia          Spain              Czech Republic    ----------------

User · Answer

FYI  A useful simulation tool - phpMyAdmin with Wampserver 3 0 6 - Preview SQL  I use  Preview SQL  to see the SQL code that would  be generated before you save the column with the change to ENUM   Preview SQL  Above you see that  I have entered  Ford   Toyota  into the ENUM but I am getting syntax ENUM 0  which is generating syntax error Query error 1064    I then copy and paste and alter the SQL and run it through SQL with a positive result   SQL changed  This is a quickfix that I use often and can also be used on existing ENUM values that need to be altered  Thought this might be useful

User · Answer

ALTER TABLE      table name  MODIFY COLUMN      column name2  enum           existing value1            existing value2            new value1            new value2        NOT NULL AFTER  column name1

User · Answer

In MYSQL server version  5 0 27 i tried this and it worked fine for me check in your version  ALTER TABLE carmake      MODIFY  country  ENUM  Japan    USA    England    Australia    Germany    France    Italy    Spain    Czech Republic    China    South Korea    India    Sweden    Malaysia

User · Answer

Your code works for me  Here is my test case   mysql gt  CREATE TABLE carmake  country ENUM  Canada    United States     Query OK  0 rows affected  0 00 sec   mysql gt  SHOW CREATE TABLE carmake   --------- -------------------------------------------------------------------------------------------------------------------------    Table     Create Table                                                                                                               --------- -------------------------------------------------------------------------------------------------------------------------    carmake   CREATE TABLE  carmake       country  enum  Canada   United States   default NULL   ENGINE MyISAM DEFAULT CHARSET latin1    --------- -------------------------------------------------------------------------------------------------------------------------  1 row in set  0 00 sec   mysql gt  ALTER TABLE carmake CHANGE country country ENUM  Sweden   Malaysia    Query OK  0 rows affected  0 53 sec  Records  0  Duplicates  0  Warnings  0  mysql gt  SHOW CREATE TABLE carmake   --------- --------------------------------------------------------------------------------------------------------------------    Table     Create Table                                                                                                          --------- --------------------------------------------------------------------------------------------------------------------    carmake   CREATE TABLE  carmake       country  enum  Sweden   Malaysia   default NULL   ENGINE MyISAM DEFAULT CHARSET latin1    --------- --------------------------------------------------------------------------------------------------------------------  1 row in set  0 00 sec    What error are you seeing   FWIW this would also work   ALTER TABLE carmake MODIFY COLUMN country ENUM  Sweden   Malaysia     I would actually recommend a country table rather than enum column  You may have hundreds of countries which would make for a rather large and awkward enum   EDIT  Now that I can see your error message   ERROR 1265  01000   Data truncated for column  country  at row 1   I suspect you have some values in your country column that do not appear in your ENUM  What is the output of the following command   SELECT DISTINCT country FROM carmake   ANOTHER EDIT  What is the output of the following command   SHOW VARIABLES LIKE  sql mode    Is it STRICT TRANS TABLES or STRICT ALL TABLES  That could lead to an error  rather than the usual warning MySQL would give you in this situation   YET ANOTHER EDIT  Ok  I now see that you definitely have values in the table that are not in the new ENUM  The new ENUM definition only allows  Sweden  and  Malaysia   The table has  USA    India  and several others   LAST EDIT  MAYBE   I think you re trying to do this   ALTER TABLE carmake CHANGE country country ENUM  Italy    Germany    England    USA    France    South Korea    Australia    Spain    Czech Republic    Sweden    Malaysia   DEFAULT NULL

User · Answer

The discussion I had with Asaph may be unclear to follow as we went back and forth quite a bit   I thought that I might clarify the upshot of our discourse for others who might face similar situations in the future to benefit from   ENUM-type columns are very difficult beasts to manipulate  I wanted to add two countries  Malaysia  amp  Sweden  to the existing set of countries in my ENUM   It seems that MySQL 5 1  which is what I am running  can only update the ENUM by redefining the existing set in addition to what I want   This did not work   ALTER TABLE carmake CHANGE country country ENUM  Sweden   Malaysia   DEFAULT NULL    The reason was that the MySQL statement was replacing the existing ENUM with another containing the entries  Malaysia  and  Sweden  only  MySQL threw up an error because the carmake table already had values like  England  and  USA  which were not part of the new ENUM s definition   Surprisingly  the following did not work either   ALTER TABLE carmake CHANGE country country ENUM  Australia   England   USA     Sweden   Malaysia   DEFAULT NULL    It turns out that even the order of elements of the existing ENUM needs to be preserved while adding new members to it  So if my existing ENUM looks something like ENUM  England   USA    then my new ENUM has to be defined as ENUM  England   USA   Sweden   Malaysia   and not ENUM  USA   England   Sweden   Malaysia    This problem only becomes manifest when there are records in the existing table that use  USA  or  England  values   BOTTOM LINE   Only use ENUMs when you do not expect your set of members to change once defined  Otherwise  lookup tables are much easier to update and modify

User · Answer

Here is another way     It adds  others  to the enum definition of the column  rtipo  of the table  firmas    set  new enum    others   set  table name    firmas   set  column name    rtipo   select column type into  tmp from information schema columns    where table name    table name and column name  column name  set  tmp   insert  tmp  instr  tmp       0  concat         new enum           set  tmp   concat  alter table     table name    modify     column name        tmp   prepare stmt from  tmp  execute stmt  deallocate prepare stmt

User · Answer

It s possible if you believe  Hehe  try this code   public function add new enum  new value           table  product        column  category             row    this- gt db- gt query  SELECT COLUMN TYPE FROM INFORMATION SCHEMA COLUMNS         WHERE TABLE NAME     AND COLUMN NAME       array  table   column  - gt row array          old category   array         new category         foreach  explode      str replace          substr  row  COLUMN TYPE    5   strlen  row  COLUMN TYPE    - 6     as  val                  getting the old category first           old category  val     val           new category       old category  val                         after the end of foreach  add the  new value to  new category         new category       new value             Then alter the table column with the new enum       this- gt db- gt query  ALTER TABLE product CHANGE category category ENUM  new category           Before adding new value  After adding new value

[mysql] How do I add more members to my ENUM-type column in MySQL?

Examples related to mysql

Examples related to enums

Examples related to alter-table