mysql+add+enum,如何在MySQL中添加更多成员到我的ENUM类型列？

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 |

+----------------+

解决方案

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.