How is enum stored in MariaDB

Question

I have created a database where one of the fields of a table is defined as enum('M', 'B'). The tool that I am using, converts it to a variant type. I would like to override it and treat it like a character. So the question is, is it stored as a character in the database?

Answer 1

ENUM values are represented internally as integers, where each enumeration value is represented by an integer index starting at 1.

When strict SQL Mode is not enabled and an invalid value is inserted into an ENUM column, MariaDB does not throw an error. Instead, it stores a special empty string '' with an index value of 0.

Consider the following example,

CREATE TABLE myTable (
  id INT NOT NULL auto_increment PRIMARY KEY,
  myCol enum('M', 'B') );
INSERT INTO myTable (myCol) VALUES ('M'), ('B');

The following queries would produce the same result

SELECT * FROM myTable WHERE myCol='B';
SELECT * FROM myTable WHERE myCol=2;
id  myCol
2    B

---

If you use ENUM in a numeric context, MariaDB will treat it as an integer:

SELECT id, myCol+0 FROM myTable;
id  myCol+0
1     1
2     2
3     0

Invalid values have index value 0 , we can query those values by using

SELECT * FROM myTable WHERE myCol = 0;
id  myCol
3

See example

See ENUM for detailed explanation

---

Answer 2

Ergest has a good answer; here is a small addition:

If there are up to 255 options, ENUM takes 1 byte; for more options (up to the limit of 64K), it takes 2 bytes.

Having more than a few options is impractical since you may forever do an ALTER TABLE to add a new option.

Note that the order of Enum options is critical for discovering the underlying numeric value and for ordering. Using Ergest's example, 'M' < 'B' because 'M' comes before 'B' (hence has a lower implementation number). This differs from 'M' > 'B' in every collation.