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

alter-table, enums, mysql

Solution

ALTER TABLE
    `table_name`
MODIFY COLUMN
    `column_name2` enum(
        'existing_value1',
        'existing_value2',
        'new_value1',
        'new_value2'
    )
NOT NULL AFTER `column_name1`;

Problem

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 | +----------------+ ```

Original source