Can I alter ENUM value from lowercase to studlyCase without reindex?

Question

I have a several big tables, and enum in each of them (example):

ENUM ('webmasters', 'stackexchange', 'stackoverflow')

Documenation says, that return value is in lettercase of ENUM definition, so I have a problem now, because I need return values in studlyCase:

ENUM ('webMasters', 'stackExchange', 'stackOverflow')

Actually I need to change only one value from big list of defined enum values, also there is yet no data in the tables with this value. And if it is matter - the value is almost in the end of enum definition.

The tables are very big - tens millions of data. So here is my question - Can I alter the tables so the index wil not be recalculated?

Answer 1

Enumerations are physically stored in the table has a number which maps to the positional value of the enumeration.

You can modify your column to change the enumeration and shouldn't experience issues with stored data values (it is always better to be safe than sorry - make a backup of the table just in case).

Enumerations come with disadvantages that you might not be aware of. The following is a stackexchange.com link discussing the merits of enumerations.

Advantages and Disadvantages to using ENUM vs Integer types?

Here we create a table with one column of enumeration.

mysql> create table mathisr.test (c1 enum  ('webmasters', 'stackexchange', 'stackoverflow'));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values (1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test;
+---------------+
| c1            |
+---------------+
| webmasters    |
| stackexchange |
| stackoverflow |
+---------------+
3 rows in set (0.00 sec)

Notice, the enumeration presents new values after we modify the column to express the value with different case.

mysql> alter table test modify c1 enum   ('webMasters', 'stackExchange', 'stackOverflow');
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from test;
+---------------+
| c1            |
+---------------+
| webMasters    |
| stackExchange |
| stackOverflow |
+---------------+
3 rows in set (0.00 sec)

The following command demonstrates adding a value within the enumeration...

mysql mathisr> alter table test modify c1 ENUM  ('webMasters', 'stackExchange', 'somethingNew', 'stackOverflow');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test;
+---------------+
| c1            |
+---------------+
| webMasters    |
| stackExchange |
| stackOverflow |
+---------------+
3 rows in set (0.00 sec)

When we look at the data we will see MySQL actually changed the stored number to accommodate the new value inserted into the middle of the enumeration. The introduction of the new value would have had indexing implications.

mysql> alter table test modify c1 tinyint;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test;
+------+
| c1   |
+------+
|    1 |
|    2 |
|    4 |
+------+
3 rows in set (0.00 sec)