如何从mysql中的枚举中删除值？

I have got a table with an ENUM in it containing 3 values: one, two, three. The table is already containing data, but ENUMvalue two isn't used at the moment, so I want to delete it from the ENUM. The result would be an ENUM with only 2 values: one, three.

我有一个包含ENUM的表,其中包含3个值:一,二,三。该表已包含数据,但此时未使用ENUM值2,因此我想从ENUM中删除它。结果将是仅具有2个值的ENUM:一,三。

Can I just use an ALTER TABLE statement and say like:

我可以使用ALTER TABLE语句并说出:

ALTER TABLE 'tablename' CHANGE 'enumname' ENUM('one', 'three');

4 个解决方案

#1

Try this :-

尝试这个 :-

alter table tablename
change `columname`  `columname` enum ('one', 'three');

Its working fine.

它的工作正常。

#2

Enums are saved as integers so all your rows with enum="one" is actually equal to 1, enum="two" is 2 and enum="tree" is 3.

枚举被保存为整数,因此enum =“one”的所有行实际上等于1,enum =“two”为2,enum =“tree”为3。

Seems MySQL is doing all the magic to change those values if enum names match so you just need to make sure there are no rows with 'two'

如果枚举名称匹配,似乎MySQL正在做所有改变这些值的魔力所以你只需要确保没有'two'的行

http://sqlfiddle.com/#!2/ef76d/1

CREATE TABLE `tablename` (`name` VARCHAR(32), `enumname` ENUM('one', 'two', 'three'));
INSERT INTO `tablename` VALUES ('one', 'one'),('two', 'two'),('three', 'three');
DELETE FROM `tablename` WHERE `enumname`=2;
ALTER TABLE `tablename` MODIFY `enumname` ENUM('one', 'three');

#3

Yes you can do that. Here is an example :

是的,你可以这么做。这是一个例子:

ALTER TABLE `database`.`tablename`
CHANGE `enumname` `enumname` ENUM('one','two')
CHARSET utf8 COLLATE utf8_general_ci NULL;

#4

Yes you can. Try it on your testenvironment fist, because it can take a while for big tables?

是的你可以。试试你的测试环境,因为大桌子需要一段时间吗?

#1