如何在MySQL的枚举类型列中添加更多成员？

Question

MySQL参考手册并没有提供一个明确的例子来解释如何实现这个功能。

我有一个ENUM类型列，包含国家名称，我需要添加更多的国家。请问应该使用什么MySQL语法来实现呢？

以下是我的尝试：

ALTER TABLE carmake CHANGE country country ENUM('Sweden','Malaysia');

我遇到的错误是：ERROR 1265 (01000): Data truncated for column 'country' at row 1.

country列是上述语句中的ENUM类型列。

SHOW CREATE TABLE命令输出：

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)

从carmake选择不同的国家 输出：

+----------------+
| country        |
+----------------+
| Italy          |
| Germany        |
| England        |
| USA            |
| France         |
| South Korea    |
| NULL           |
| Australia      |
| Spain          |
| Czech Republic |
+----------------+

- Zaid

Answer 1

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

- Pradip Chongbang

---

大多数 ALTER TABLE 命令将完全重写整个表。MySQL 是否足够聪明，不会对枚举类型进行这样的操作？ - John

枚举只是一个带有字符串表示的高级整数。在末尾添加项目是可以的，因为你只是添加意义相同的旧值。但是改变顺序或删除枚举将使这些数字未定义。(例如: 1代表意大利，2代表德国)，然后扩展将是(1代表意大利，2代表德国，3代表瑞典)。 - lintabá

@John 取决于情况。对于MariaDB，自10.3.7起可以在原地添加枚举的新值：https://mariadb.com/kb/en/library/innodb-online-ddl-operations-with-algorithminstant/#adding-a-new-enum-option - Felipe Philipp

@John，我刚刚用MySQL测试了一下，现有的数据行没有受到影响。 - Abdullah Khan

@AbdullahKhan 到目前为止，我也获得了一些经验： MySQL有时可以在不修改的情况下完成操作，有时它会重新创建整个表。 这取决于内部状态，例如它有多少枚举和你要添加多少枚举，我不记得具体细节，但如果你只有几百个枚举，只要你不修改任何枚举，只在末尾添加新的枚举，它就可以在不重新创建的情况下工作。 - John

Answer 2

你的代码对我有效。这是我的测试用例：

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)

你看到了什么错误？

顺便说一下，这个也可以运行：

ALTER TABLE carmake MODIFY COLUMN country ENUM('Sweden','Malaysia');

我实际上建议使用国家表而不是枚举列。您可能有数百个国家，这将导致非常大且尴尬的枚举。

编辑：现在我可以看到您的错误消息：

ERROR 1265 (01000): Data truncated for column 'country' at row 1.

我怀疑你的国家列中有一些值，这些值在你的ENUM中不存在。以下命令的输出是什么？

SELECT DISTINCT country FROM carmake;

另一次编辑：以下命令的输出是什么？

SHOW VARIABLES LIKE 'sql_mode';

是STRICT_TRANS_TABLES还是STRICT_ALL_TABLES？这可能会导致错误，而不是MySQL通常在这种情况下给出的警告。

再次编辑：好的，我现在看到您的表中肯定有不在新ENUM中的值。 新的ENUM定义仅允许'Sweden'和'Malaysia'。该表具有'USA'、'India'和其他多个值。

最后编辑（也许）：我想你是想这样做：

ALTER TABLE carmake CHANGE country country ENUM('Italy', 'Germany', 'England', 'USA', 'France', 'South Korea', 'Australia', 'Spain', 'Czech Republic', 'Sweden', 'Malaysia') DEFAULT NULL;

- Asaph

---

@Zaid小心说话。MySQL以允许垃圾进入ENUM列而臭名昭著。例如，它将默默地将不符合规范的值转换为空字符串。您是否100％确定没有任何冒犯的价值？没有空字符串？没有前导或尾随的空格？大小写差异？带重音的字符？ - Asaph

@Zaid 我认为你的表中有一些值不在更新后的ENUM定义中。 你的新定义只允许瑞典和马来西亚。 你的表中有美国，印度，德国... 在你的新ENUM中，这些都将不被允许。 如果你要做的是在保留原始ENUM成员的情况下添加瑞典和马来西亚，你需要在ALTER语句中重新列出所有原始ENUM值加上2个新值。 - Asaph

@Zaid 不用谢。如果你使用一个带有外键的国家表，而不是我早先建议的 ENUM，那么你只需添加新国家的行即可。顺便说一句：如果你觉得我的建议有用，请标记我的答案为正确 :) - Asaph

由于缺乏关于实际解决方案的明确说明，这篇文章存在不少交流内容和“试试这个，哦不对试试这个”的情况。最初的回复甚至没有回答提问者的问题——直到后面才意识到问题所在，然后给出了一些简单的代码示例，但对于以后查看此问题/答案的人来说并没有什么实际意义。您的编辑背景是临时的，现在已不再明显。 - Jim Rubenstein

请不要把我上面的评论当成个人攻击，我认为回答问题所需的内容已经存在，只需要更多地重构事件并来回翻阅，才能理解真正的最终答案。 - Jim Rubenstein

Answer 3

重要提示：这是一个遗留答案

我与Asaph的讨论可能不太容易理解，因为我们来回交流了很多次。

我想澄清我们讨论的结果，让其他可能在未来面临类似情况的人受益：

ENUM类型列非常难以操作。我想向现有的国家集合中添加两个国家（马来西亚和瑞典）。

看起来MySQL 5.1（我正在运行的版本）只能通过重新定义现有集合并添加我想要的内容来更新ENUM：

这种方法行不通：

ALTER TABLE carmake CHANGE country country ENUM('Sweden','Malaysia') DEFAULT NULL;

原因是MySQL语句将现有的ENUM替换为另一个仅包含条目'马来西亚'和'瑞典'的枚举。 MySQL抛出错误，因为"carmake"表已经有像'英国'和'美国'这样不属于新的ENUM定义的值。

令人惊讶的是，以下内容也没有起作用：

ALTER TABLE carmake CHANGE country country ENUM('Australia','England','USA'...'Sweden','Malaysia') DEFAULT NULL;

ENUM需要保留现有元素的顺序，同时在添加新成员时也要保持一致。因此，如果现有的ENUM看起来像ENUM('England'，'USA')，那么我的新ENUM必须定义为ENUM('England'，'USA'，'Sweden'，'Malaysia')而不是ENUM('USA'，'England'，'Sweden'，'Malaysia')。只有当现有表中存在使用'USA'或'England'值的记录时，才会出现这个问题。

底线：

只有在您不希望定义后集合成员发生更改时才使用ENUM。否则，查找表更容易更新和修改。

- Zaid

---

我会冒昧给出一个更加强烈的底线，即 "只有在你确信值永远不会改变时才使用 ENUMs"。如果表格变得很大，如果必须更改这些值，那将是一件非常麻烦的事情。 - DougW

我不确定我同意那个底线。相信我，我一点也不喜欢ENUM，但我没有看到在添加到可能的ENUM中存在危险。ENUM的本质是将0映射到选项1，1映射到选项2等等。增加这个映射不应该会引起问题。 - JoshStrange

@JoshStrange 这并不是一个危险，但当你的 ENUM 的顺序很重要时（例如用于排序），它可能会带来巨大的不便。 - 1in9ui5t

我认为在底线上也重要指出这仅适用于MySQL的旧版本，因为据我了解，对于新版本来说没有问题。 - Niccolò

Answer 4

在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');

- Abhishek

---

不确定为什么这个没有更多的赞。它很简单，而且对我很有效。 - ancestral

我可以确认它同样适用于MySQL 5.7。 - Tsvetan Ganev

似乎它也适用于MySQL 8。 - Stephane

Answer 5

这里有另一种方法...

它在表“firmas”的列“rtipo”的枚举定义中添加了“others”。

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

Answer 6

FYI：一个有用的模拟工具——phpMyAdmin与Wampserver 3.0.6 - 预览SQL：我使用“预览SQL”来查看在将更改保存到ENUM列之前将生成的SQL代码。Preview SQL 如上所示，我已经将“Ford”，“Toyota”输入到ENUM中，但是我得到了生成语法错误的语法ENUM（0）。Query error 1064# 然后，我复制并粘贴并修改SQL，并通过SQL运行它以获得积极的结果。 SQL changed 这是我经常使用的快速修复方法，也可以用于需要更改的现有ENUM值。希望这对你有所帮助。

- Addi

Answer 7

mysql数据在第1行的'status'列被截断枚举 上述错误可能是在添加新的枚举值时引起的，如果旧值存在于记录中并且修改后的ENUM不包含它

- ShaileshKumarMPatel

Answer 8

只要你相信，就有可能。嘿嘿，试试这段代码。

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)");
  }

在添加新值之前

添加新值后

- Alj

---

我不认为这会给我们带来新的东西。问题纯粹来自于MySQL的角度。你的回答涉及了无关紧要的PHP内容，而且你也没有尝试解释其中任何一部分。这是一个毫无用处的信息资源。 - Jonathan