An ENUM such as orders.status stores one value from a fixed list as its position, from 1, in one byte. A SET stores any combination of up to 64 members as a bitmask. BIT(M) stores M bits, and BOOL is merely TINYINT(1):
SELECT DISTINCT status, status + 0 AS idx, status > 'paid' AS text_cmp, status > 2 AS idx_cmp
FROM orders ORDER BY status;
CREATE TABLE flags (perms SET('read','write','delete'), bits BIT(4), active BOOL);
INSERT INTO flags VALUES ('write,read', b'1010', TRUE), ('delete,read,read', 5, 7);
SELECT perms, perms + 0 AS mask, FIND_IN_SET('write', perms) AS has_write,
BIN(bits) AS bin, bits + 0 AS num, active FROM flags;+-----------+-----+----------+---------+ | status | idx | text_cmp | idx_cmp | +-----------+-----+----------+---------+ | pending | 1 | 1 | 0 | | paid | 2 | 0 | 0 | | shipped | 3 | 1 | 1 | | cancelled | 4 | 0 | 1 | +-----------+-----+----------+---------+ +-------------+------+-----------+------+------+--------+ | perms | mask | has_write | bin | num | active | +-------------+------+-----------+------+------+--------+ | read,write | 3 | 2 | 1010 | 10 | 1 | | read,delete | 5 | 0 | 101 | 5 | 7 | +-------------+------+-----------+------+------+--------+
ORDER BY status sorts by list position. Against a string MySQL 524 compares the text, against a number the index, and inserting the unknown status 'refunded' fails with ERROR 1265, a misleading "Data truncated for column 'status'". The SET reordered its members and dropped the duplicate, and BOOL stored 7, which fails WHERE active = TRUE because TRUE is 1. Read BIT with BIN() or + 0. Appending an ENUM member is instant, but inserting one mid-list rebuilds the table, so a growing list belongs in a lookup table (ER Model to Tables).