Removing enum values from mysql column

Question

I have a table with a enum column called action. The permitted values currently are: act1,act2,act3,act4. I want act3 and act4 to be removed and my table's current state does not contain any rows with act3 or act4.

When I'm trying to modify the column with the new set of values it's throwing an error Data Truncated for column action.

Please suggest how do I remove the required values.

Maybe don't use enum at all? http://komlenic.com/244/8-reasons-why-mysqls-enum-data-type-is-evil/ — fancyPants, Sep 26 '12 at 08:10

Florian Parain · Accepted Answer · 2012-09-26T12:28:44.737

11

Using ALTER TABLE for adding enum values is ok and described in the MySQL documentation.

However, for removing enum values the better option is to create a new column to do the change.

ALTER TABLE your_table ADD new_action_column ENUM('act1', 'act2') ... ;
UPDATE your_table SET new_action_column = action;
ALTER TABLE your_table DROP action;
ALTER TABLE your_table CHANGE new_action_column action ENUM('act1', 'act2') ... ;

Edit

By the way. Using ENUM is not the best idea, you should use INT instead.

8 Reasons Why MySQL's ENUM Data Type Is Evil

I suggest you to use a mapping like

+------+-----+
| ENUM | INT |
+======+=====+
| act1 |  0  |
+------+-----+
| act2 |  1  |
+------+-----+

edited Sep 26 '12 at 12:28

answered Sep 26 '12 at 08:56

Florian Parain

1,089
8
14

1

"you should use INT instead." - Why? – Barmar Sep 26 '12 at 09:32
@Barmar [8 Reasons Why MySQL's ENUM Data Type Is Evil](http://komlenic.com/244/8-reasons-why-mysqls-enum-data-type-is-evil/) Tombom already mentioned it. I suggest to use a mapping between your enum values 'act1' and 'act2' and int values 0, 1. – Florian Parain Sep 26 '12 at 09:39
@FlorianParain I wonder why there should be any advantageous not altering the existing column but rather adding a temporary one. Can you reason your statement? Thanks! – magic_al Jan 29 '16 at 13:01

score 4 · Answer 2 · answered Sep 26 '12 at 09:02

4

First run a query.

UPDATE table_name SET action = '' WHERE action IN ( 'act3', 'act4' );

after this run this query.

ALTER TABLE table_name CHANGE action action ENUM( 'act1', 'act2' );

there is no need to drop your table or drop your field. but you are required to delete or update all data having the values, which you want to remove.

answered Sep 26 '12 at 09:02

Bhoopesh Pathak

179
1
11

He said there aren't any rows with the values he's going to remove. – Barmar Sep 26 '12 at 09:31
I am not required to make the above steps as my table does not contain rows with 'act3' and 'act4'. The error is still there. – mickeymoon Sep 26 '12 at 10:12

score 2 · Answer 3 · answered Dec 04 '16 at 11:58

The other two answers already covered the question in larger detail, but here is a simple issue why you may not be able to simply do ALTER TABLE. If you have an ENUM('BAR','FOO','REMOVEME') and it gives an Error saying something along the lines of Data truncated somethingsomething, you might already have an entry set to the very Enum member you want to remove. So you'd first need to do something like

UPDATE yourtable SET enumrow='FOO' WHERE yourenumrow = 'REMOVEME';

This way, all entries that had REMOVEME will now be FOO and the table can be altered using

ALTER TABLE yourtable CHANGE yourenumrow yourenumrow ENUM('FOO','BAR') DEFAULT NULL;

Removing enum values from mysql column

3 Answers3