[arp242]

You're not wrong, but unfortunately many teams don't.

Probably my favourite "ya'll don't understand how databases work" was where they "reserved" space for MySQL enums; for example for the "active" column it would be something like:

  enum(
      'active',
      'deleted',
      '_futureval1',
      '_futureval2',
      '_futureval3',
      '_futureval4',
      '_futureval5',
      '_futureval6',
      '_futureval7',
      '_futureval8',
      '_futureval9'
  )

Enums don't work like that at all; it's just a mapping of int to a string value for readability, and you don't need to "reserve" space to add future values just like you don't need to for ints. Adding a new enum value is easy and cheap. Removing them is not as it requires a full scan of all rows to verify they're used. Even worse, you couldn't easily rename enum labels (at the time, don't know if you can now), making it all worse than useless.

Since this was all on large tables and the effort to fix it was relatively large, but without adding much business value, we never fixed it. We were basically stuck with it. It sure as hell annoyed me every time I looked at it.

I'm not an DBA either, but spending about 5 seconds on the documentation for "enum" would have prevented this. This really doesn't require a PhD in SQL.

[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.