[mbell]

I'm not sure what the current state of things are since I haven't use MySQL recently but this used to be a perfectly valid thing to do.

The issue was that MySQL doesn't use a full int to store enums. If your enum has 8 values, it stores in 1 byte, if it has more than 8, it stores it in 2 bytes. Adding that 9th value thus requires re-writing the entire table. So yes - it can make sense to "reserve space" to avoid a future table re-write.

You also had to be careful to include `ALGORITHM=INPLACE, LOCK=NONE;` in your `ALTER TABLE` statement when changing the enum or it would lock the table and rewrite it.

[evanelias]

> If your enum has 8 values, it stores in 1 byte, if it has more than 8, it stores it in 2 bytes.

You're probably thinking of the SET type, rather than the ENUM type.

> You also had to be careful to include `ALGORITHM=INPLACE, LOCK=NONE;` in your `ALTER TABLE` statement when changing the enum or it would lock the table and rewrite it.

This is a common misconception; that's not how ALGORITHM=INPLACE, LOCK=NONE works. An ALTER TABLE without ALGORITHM and LOCK will, by default, always use the least-disruptive method of alteration possible.

Adding those clauses in MySQL just tells the server "fail immediately if the requested method is not possible". The semantics in MariaDB are similar, just slightly different for INPLACE, where it means "fail immediately if the requested method or a better one is not possible".

[CRConrad]

> You're probably thinking of the SET type, rather than the ENUM type.

Ah oui, très Pascal.

[arp242]

You can store 255 values in one byte, and reserving two bytes is not what that did.

And even if I did, it still leaves the inability to actually rename enums without scanning the full table at least twice (which still doesn't seem possible in MariaDB, unless I missed something there).

If you potentially want great flexibility you shouldn't be using enums in the first place but int and a relational mapping to another table.

[colonwqbang]

A byte would fit at least 255 different values, right? How often is this limit exceeded in practice.