MySQL 更改一列 ENUM 值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15642695/
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
MySQL Change a column ENUM value
提问by Sangram Anand
I have a MySQL table "content" which has a column page_typeof type ENUM. The ENUMvalues are NEWS& PRESS_RELEASE. I need to replace NEWSwith FEATURED_COVERAGE:
我有一个 MySQL 表“ content”，它有一列page_type类型ENUM。该ENUM值是NEWS＆PRESS_RELEASE。我需要替换NEWS为FEATURED_COVERAGE：
ALTER TABLE `content` CHANGE `pagetype` `pagetype` ENUM('FEATURED_COVERAGE','PRESS_RELEASE') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
But now the records in the table, which earlier had page_type NEWSare now empty, and there is no way that I can identify which records are NEWS, so that I can rename those to FEATURED_COVERAGE.
但是现在表中的记录，以前有 page_typeNEWS现在是空的，我无法识别哪些记录是NEWS，因此我可以将它们重命名为FEATURED_COVERAGE.
How to resolve such issues?
如何解决此类问题？
回答by Abimaran Kugathasan
If I understand your question, you want to rename the existing enum value NEWSto FEATURED_COVERAGE. If so, you need to follow below steps,
如果我理解您的问题，您想将现有的枚举值重命名NEWS为FEATURED_COVERAGE. 如果是这样，您需要按照以下步骤操作，
Alter the table and add the new enum value to the column, so that you will have 3 enums
ALTER TABLE `content` CHANGE `pagetype` `pagetype` ENUM('FEATURED_COVERAGE','PRESS_RELEASE', 'NEWS') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;Set the old enum value to new value for all records.
UPDATE `content` set `pagetype` = 'FEATURED_COVERAGE' where `pagetype` = 'NEWS';Alter the table and drop the old enum value.
ALTER TABLE `content` CHANGE `pagetype` `pagetype` ENUM('FEATURED_COVERAGE','PRESS_RELEASE') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
更改表并将新的枚举值添加到列中，这样您将拥有 3 个枚举
ALTER TABLE `content` CHANGE `pagetype` `pagetype` ENUM('FEATURED_COVERAGE','PRESS_RELEASE', 'NEWS') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;将所有记录的旧枚举值设置为新值。
UPDATE `content` set `pagetype` = 'FEATURED_COVERAGE' where `pagetype` = 'NEWS';更改表并删除旧的枚举值。
ALTER TABLE `content` CHANGE `pagetype` `pagetype` ENUM('FEATURED_COVERAGE','PRESS_RELEASE') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
回答by Vatev
MySQL's enum always has a hidden option which is 0 as integer and '' as string. When you try to assign an invalid value it uses the hidden one.
MySQL 的枚举总是有一个隐藏选项，它是 0 作为整数和 '' 作为字符串。当您尝试分配无效值时，它会使用隐藏的值。
Assuming all your empty values were 'NEWS' before the update you can change them with
假设您的所有空值在更新之前都是“新闻”，您可以使用
UPDATE content SET pagetype = 'FEATURED_COVERAGE' WHERE pagetype = 0
回答by georgecj11
I think default might have helped.
我认为默认可能有所帮助。
ALTER TABLE `content`
CHANGE `pagetype` `pagetype` ENUM('FEATURED_COVERAGE','PRESS_RELEASE')
CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULt 'FEATURED_COVERAGE';
Now you have to blindly to an update
现在你必须盲目地更新
And never use an enum column if your value set is changing.
如果您的值集正在更改，请不要使用枚举列。
回答by alwaysLearn
Since you have changed only one enum it will not be difficult for you
由于您只更改了一个枚举，因此对您来说并不困难
Try using this
尝试使用这个
UPDATE content SET pagetype = 'FEATURED_COVERAGE' WHERE pagetype = 0
