SET Data Type
In this page:
Understanding SET
SET lets a single column hold zero, one, or several values simultaneously chosen from a predefined list, unlike ENUM which allows only one — useful for something like a list of tags or permissions on a row.
Example: Understanding SET
CREATE TABLE users (permissions SET('read', 'write', 'admin'));
INSERT INTO users VALUES ('read,write');Querying SET Elements
Because a SET column can contain multiple values at once, a plain equality check often won't match what you expect; the FIND_IN_SET() function is the correct way to test whether one specific value is present.
Example: Querying SET Elements
CREATE TABLE users (permissions SET('read', 'write', 'admin'));
INSERT INTO users VALUES ('read,write');
SELECT * FROM users WHERE FIND_IN_SET('write', permissions);Binary representation of SET
Internally, MySQL stores a SET as a bitmap where each defined option occupies one bit (1, 2, 4, 8, and so on), which is what makes combining or testing multiple values so storage-efficient.
Example: Binary representation of SET
-- 'read'=1, 'write'=2, 'admin'=4 -- combined values sum their bits
CREATE TABLE users (permissions SET('read', 'write', 'admin'));
INSERT INTO users VALUES ('read,admin'); -- stored internally as 1 + 4 = 5Updating SET Columns
You can add or remove individual options from a SET column's current value using string functions or direct bitwise operations, without needing to know or retype every other value already stored.
Example: Updating SET Columns
CREATE TABLE users (id INT, permissions SET('read', 'write', 'admin'));
INSERT INTO users VALUES (1, 'read');
UPDATE users SET permissions = CONCAT(permissions, ',write') WHERE id = 1;Invalid SET Values
Just like ENUM, inserting a value that isn't part of the SET's defined list will fail outright under strict SQL mode, protecting the column from silently accumulating typos or invalid tags.
Example: Invalid SET Values
CREATE TABLE users (permissions SET('read', 'write', 'admin'));
INSERT INTO users VALUES ('delete'); -- rejected in strict SQL modeChapter Quiz — Complete all 8 topics to unlock
0/8 topics done
Complete these topics first: