222

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> 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 |
+----------------+
Zaid
  • 36,680
  • 16
  • 86
  • 155

8 Answers8

216
ALTER TABLE
    `table_name`
MODIFY COLUMN
    `column_name2` enum(
        'existing_value1',
        'existing_value2',
        'new_value1',
        'new_value2'
    )
NOT NULL AFTER `column_name1`;
Hannele
  • 9,301
  • 6
  • 48
  • 68
Pradip Chongbang
  • 2,188
  • 1
  • 11
  • 6
  • 12
    Most ALTER TABLE commands will completely rewrite the whole table. Is mysql clever enough to not do that with enum ? – John Oct 09 '18 at 22:10
  • 4
    enum is just a fancy integer, with a string representation. adding items to the end is fine, since you just add meaning old values. but changing the order / removing enums will make those numbers undefined. (eg. 1=>italy, 2=>germany), then extending will be (1=>italy, 2=>germany, 3=>sweenden). – lintabá Mar 21 '19 at 15:13
  • 2
    @John depends. For MariaDB, adding new values at the end of the enum can be done `inplace` since 10.3.7: https://mariadb.com/kb/en/library/innodb-online-ddl-operations-with-algorithminstant/#adding-a-new-enum-option – Felipe Philipp Sep 18 '19 at 08:17
  • @John i just tested this with Mysql and the exisitng data rows are not affected. – Abdullah Khan May 30 '22 at 09:56
  • 1
    @AbdullahKhan by now I also gained some experience: Mysql sometimes can do it without modification, sometimes it will recreate the entire table. It depends on internal states, like how many enum it has and how many you are adding, I don't recall the specifics but if you have just a couple hundred enums it will work without re-creation IF: You do not modify any enum, you only add new ones at the end. – John May 31 '22 at 15:20
109

Your code works for me. Here is my test case:

mysql> CREATE TABLE carmake (country ENUM('Canada', 'United States'));
Query OK, 0 rows affected (0.00 sec)

mysql> 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> ALTER TABLE carmake CHANGE country country ENUM('Sweden','Malaysia');
Query OK, 0 rows affected (0.53 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 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;
Asaph
  • 159,146
  • 25
  • 197
  • 199
  • There's more than one column in my `carmake` table. Could that have anything to do with it? – Zaid Oct 01 '09 at 05:14
  • Unlikely but post the output of this command "SHOW CREATE TABLE carmake" and I'll look at it. – Asaph Oct 01 '09 at 05:16
  • @Zaid I see you've added the error message "The error I get is: ERROR 1265 (01000): Data truncated for column 'country' at row 1.". I think the problem is that you have values already in the country column that are not listed in the enum. – Asaph Oct 01 '09 at 05:20
  • That's not the case. The ENUM type doesn't allow for entries that are not present in the ENUM itself. My table is about 50 entries at the moment. At a glance, I can tell you that no invalid values are present. – Zaid Oct 01 '09 at 05:26
  • 1
    @Zaid be careful about saying that. MySQL is notorious for allowing garbage into ENUM columns. It will silently convert non-conforming values to empty strings, for instance. Are you 100% sure you don't have any offending values? No empty strings? No leading or trailing whitespace? Case differences? Accented characters? – Asaph Oct 01 '09 at 05:36
  • @Asaph: The `SHOW VARIABLES LIKE 'sql_mode';` command returns `STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION`. What does that mean? – Zaid Oct 01 '09 at 05:37
  • 2
    @Zaid I think you do have values in your table that are absent from your updated ENUM definition. Your new definition only allows Sweden and Malaysia. Your table has USA, India, Germany... None of those values will be allowed in your new ENUM. If what you are trying to do is *add* Sweden and Malaysia while preserving the original members of the ENUM, you'll need to relist *all* the original ENUM values plus the 2 new ones in your ALTER statement. – Asaph Oct 01 '09 at 05:44
  • @Asaph: That's a bummer. I was hoping that for a way to append to the existing list. Thanks for your help anyway. I really appreciate it. – Zaid Oct 01 '09 at 05:50
  • 2
    @Zaid You're welcome. If you use a countries table with a foreign key instead of the ENUM as I suggested early on, you'll be able to simply add rows for the new countries. BTW: If you've found my suggestions helpful, please mark my answer correct :) – Asaph Oct 01 '09 at 05:55
  • Perhaps you could combine this with a query of the existing enum members. – Svante Oct 01 '09 at 06:10
  • @Asaph: I originally chose to go with this ENUM type because I wanted to avoid the hassle of having to deal with stray/duplicate entries. I had no idea ENUMs were this unwieldy... – Zaid Oct 01 '09 at 06:11
  • 4
    -1 For lack of clarity on what the actual solution is here. There's too much conversation and a lot of "try this, oh well try this instead." The initial response doesn't even answer the OP's actual question - it's not until later that the you realize what the problem was, and then you go into simple code samples that don't actually mean anything to someone who is finding this question/answer at a later time. The context of your edits is/was temporal, and is no longer obvious. – Jim Rubenstein Feb 02 '14 at 14:17
  • Also please don't take my above comment personally, I think the content needed to answer the question is present, it just requires a lot more reconstruction of the events as they unfolded, and flipping back and forth, to understand what the actual end answer really is. – Jim Rubenstein Feb 02 '14 at 14:20
78

Important note: this is a legacy 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 & 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.

Zaid
  • 36,680
  • 16
  • 86
  • 155
  • I would venture a stronger bottom line... "only use ENUMs when you are 100% dead certain positive that the values will never change". If a table grows large, it will be a pain if you ever have to change those values. – DougW Aug 19 '14 at 21:06
  • 8
    I'm not sure I agree with that bottom line. Trust me I don't like ENUM's at all but I don't see the danger in ADDING to the possible ENUM. ENUM is, at it's core, a mapping of 0 -> Option 1, 1-> Option 2, etc. Adding to that shouldn't cause an issue. – JoshStrange Feb 10 '15 at 20:07
  • 2
    @JoshStrange It's not so much a danger, it can be a huge inconvenience when the order of your ENUM is important (for example, when used for ordering). – 1in9ui5t Jun 26 '15 at 16:55
  • 2
    I think it's important also to say in the bottom line that that is valid only with legacy versions of MySQL because for what I understand, with newer ones there is no problem. – Niccolò Jun 22 '17 at 10:55
34

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');
Hannele
  • 9,301
  • 6
  • 48
  • 68
Abhishek
  • 528
  • 6
  • 17
2

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;
Antonio
  • 31
  • 5
1

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.

Addi
  • 199
  • 14
0

mysql data truncated for column 'status' at row 1 enum Above error may be caused during adding new enum values, if old values exist in records and altered ENUM do not include it

ShaileshKumarMPatel
  • 129
  • 1
  • 6
-14

It's possible if you believe. Hehe. try this code.

public function add_new_enum($new_value)
  {
    $table="product";
    $column="category";
         $row = $this->db->query("SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = ? AND COLUMN_NAME = ?", array($table, $column))->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->db->query("ALTER TABLE product CHANGE category category ENUM($new_category)");
  }

Before adding new value

After adding new value

Alj
  • 1
  • 4
  • 13
    I don't see how this brings anything new to the table. The question is purely from a MySQL standpoint as well. Your answer incorporates random PHP that is completely irrelevant and you make no attempt at explaining any of it. Useless resource of information – Jonathan Aug 30 '16 at 09:01