如何向 MySQL 中的 ENUM 类型列添加更多成员？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1501958/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I add more members to my ENUM-type column in MySQL?
提问by Zaid
The MySQL reference manual does not provide a clearcut example on how to do this.
MySQL 参考手册没有提供有关如何执行此操作的明确示例。
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?
我有一个 ENUM 类型的国家/地区名称列，需要向其中添加更多国家/地区。实现此目的的正确 MySQL 语法是什么？
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.
我得到的错误是： ERROR 1265 (01000): Data truncated for column 'country' at row 1.
The countrycolumn is the ENUM-type column in the above-statement.
该country列是上述语句中的 ENUM 类型列。
SHOW CREATE TABLEOUTPUT:
显示创建表输出：
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 carmakeOUTPUT:
从 carmake输出中选择不同的国家：
+----------------+
| country |
+----------------+
| Italy |
| Germany |
| England |
| USA |
| France |
| South Korea |
| NULL |
| Australia |
| Spain |
| Czech Republic |
+----------------+
采纳答案by Pradip Chongbang
ALTER TABLE
`table_name`
MODIFY COLUMN
`column_name2` enum(
'existing_value1',
'existing_value2',
'new_value1',
'new_value2'
)
NOT NULL AFTER `column_name1`;
回答by Asaph
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:
FWIW 这也适用：
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?
我怀疑您的国家/地区列中有一些值没有出现在您的ENUM. 以下命令的输出是什么？
SELECT DISTINCT country FROM carmake;ANOTHER EDIT: What is the output of the following command?
另一个编辑：以下命令的输出是什么？
SHOW VARIABLES LIKE 'sql_mode';Is it STRICT_TRANS_TABLESor STRICT_ALL_TABLES? That could lead to an error, rather than the usual warning MySQL would give you in this situation.
是STRICT_TRANS_TABLES还是STRICT_ALL_TABLES？这可能会导致错误，而不是 MySQL 在这种情况下会给您的通常警告。
YET ANOTHER EDIT: Ok, I now see that you definitely have values in the table that are not in the new ENUM. The new ENUMdefinition only allows 'Sweden'and 'Malaysia'. The table has 'USA', 'India'and several others.
另一个编辑：好的，我现在看到您在表中肯定有值不在新的ENUM. 新ENUM定义只允许'Sweden'和'Malaysia'。桌子上有'USA'，'India'还有其他几个。
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;回答by Zaid
The discussionI had with Asaph may be unclear to follow as we went back and forth quite a bit.
我和 Asaph的讨论可能不清楚，因为我们来回走了很多次。
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.
ENUM型列是非常难以操纵的野兽。我想将两个国家（马来西亚和瑞典）添加到我的 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:
似乎 MySQL 5.1（这是我正在运行的）除了我想要的之外，只能通过重新定义现有集来更新 ENUM：
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 carmaketable already had values like 'England'and 'USA'which were not part of the new ENUM's definition.
其原因是，MySQL的声明与另一种含条目替换现有ENUM'Malaysia'和'Sweden'唯一的。MySQL 抛出了一个错误，因为该carmake表已经有像'England'和'USA'不属于 newENUM定义的一部分的值。
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 ENUMneeds to be preserved while adding new members to it. So if my existing ENUMlooks something like ENUM('England','USA'), then my new ENUMhas 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.
事实证明，ENUM在向其中添加新成员时，甚至需要保留现有元素的顺序。因此，如果我的现有ENUM看起来像ENUM('England','USA')，那么我的 newENUM必须被定义为ENUM('England','USA','Sweden','Malaysia')和 not ENUM('USA','England','Sweden','Malaysia')。只有当现有表中有使用'USA'或'England'值的记录时，此问题才会变得明显。
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.
仅ENUM当您不希望成员集在定义后发生更改时才使用s。否则，查找表更容易更新和修改。
回答by Abhishek
In MYSQL server version: 5.0.27 i tried this and it worked fine for me check in your version
在 MYSQL 服务器版本：5.0.27 中，我尝试了此操作，它对我来说运行良好，请检查您的版本
ALTER TABLE carmake
MODIFY `country` ENUM('Japan', 'USA', 'England', 'Australia', 'Germany', 'France', 'Italy', 'Spain', 'Czech Republic', 'China', 'South Korea', 'India', 'Sweden', 'Malaysia');
回答by Antonio
Here is another way...
这是另一种方式...
It adds "others" to the enum definition of the column "rtipo" of the table "firmas".
它将“others”添加到“firmas”表的“rtipo”列的枚举定义中。
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;
回答by Addi
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
仅供参考：一个有用的模拟工具 - 带有 Wampserver 3.0.6 的 phpMyAdmin - 预览 SQL：我使用“预览 SQL”来查看将在保存列更改为 ENUM 之前生成的 SQL 代码。 预览 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#
在上面你看到我已经在 ENUM 中输入了 'Ford','Toyota' 但我得到了语法 ENUM(0)，它产生了语法错误查询错误 1064#
I then copy and paste and alter the SQL and run it through SQL with a positive result.
然后我复制、粘贴和更改 SQL 并通过 SQL 运行它，结果是肯定的。
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.
这是我经常使用的快速修复，也可以用于需要更改的现有 ENUM 值。认为这可能有用。
回答by Alj
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)");
}
