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

Zaid picture Zaid · Oct 1, 2009 · Viewed 183.1k times · Source

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

Answer

Pradip Chongbang picture Pradip Chongbang · Nov 11, 2016
ALTER TABLE
    `table_name`
MODIFY COLUMN
    `column_name2` enum(
        'existing_value1',
        'existing_value2',
        'new_value1',
        'new_value2'
    )
NOT NULL AFTER `column_name1`;