SET is a string object that can have zero or more values, each of which must be
chosen from a list of permitted values specified when the table is created.
column values that consist of multiple set members are specified with members separated by commas ("
,"). A consequence of this
SET member values should not themselves contain commas.
For example, a column specified as
SET('one', 'two') NOT NULL can have any of these
column can have a maximum of 64 distinct members. A table can have no more than 255 unique element list
definitions among its
columns considered as a group. For more information on this limit, see Section
D.10.5, "Limits Imposed by
.frm File Structure".
Duplicate values in the definition cause a warning, or an error if strict SQL mode is enabled.
Trailing spaces are automatically deleted from
SET member values in the table
definition when a table is created.
When retrieved, values stored in a
SET column are displayed using the lettercase
that was used in the column definition. Note that
SET columns can be assigned a
character set and collation. For binary or case-sensitive collations, lettercase is taken into account when
assigning values to the column.
SET values numerically, with the low-order bit of the stored value
corresponding to the first set member. If you retrieve a
SET value in a numeric
context, the value retrieved has bits set corresponding to the set members that make up the column value. For
example, you can retrieve numeric values from a
SET column like this:
If a number is stored into a
SET column, the bits that are set in the binary
representation of the number determine the set members in the column value. For a column specified as
SET('a','b','c','d'), the members have the following decimal and binary values.
||Decimal Value||Binary Value|
If you assign a value of
9 to this column, that is
1001 in binary, so the first and fourth
'd' are selected and the resulting
For a value containing more than one
SET element, it does not matter what order the
elements are listed in when you insert the value. It also does not matter how many times a given element is
listed in the value. When the value is retrieved later, each element in the value appears once, with elements
listed according to the order in which they were specified at table creation time. For example, suppose that a
column is specified as
CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
If you insert the values
INSERT INTO myset (col) VALUES-> ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');Query OK, 5 rows affected (0.01 sec)Records: 5 Duplicates: 0 Warnings: 0
Then all these values appear as
'a,d' when retrieved:
SELECT col FROM myset;+------+| col |+------+| a,d || a,d || a,d || a,d || a,d |+------+5 rows in set (0.04 sec)
If you set a
SET column to an unsupported value, the value is ignored and a warning
INSERT INTO myset (col) VALUES ('a,d,d,s');Query OK, 1 row affected, 1 warning (0.03 sec)mysql>
SHOW WARNINGS;+---------+------+------------------------------------------+| Level | Code | Message |+---------+------+------------------------------------------+| Warning | 1265 | Data truncated for column 'col' at row 1 |+---------+------+------------------------------------------+1 row in set (0.04 sec)mysql>
SELECT col FROM myset;+------+| col |+------+| a,d || a,d || a,d || a,d || a,d || a,d |+------+6 rows in set (0.01 sec)
If strict SQL mode is enabled, attempts to insert invalid
SET values result in an
SET values are sorted numerically.
NULL values sort
SELECT * FROMmysql>
SELECT * FROM
The first statement finds rows where
set_col contains the
value set member. The second is similar, but not the same: It finds
anywhere, even as a substring of another set member.
The following statements also are permitted:
SELECT * FROMmysql>
SELECT * FROM
The first of these statements looks for values containing the first set member. The second looks for an exact
match. Be careful with comparisons of the second type. Comparing set values to
returns different results than comparing values to
should specify the values in the same order they are listed in the column definition.
To determine all possible values for a
SET column, use
COLUMNS FROM and parse the
definition in the
Type column of the output.
In the C API,
SET values are returned as strings. For information about using
result set metadata to distinguish them from other strings, see Section
21.8.5, "C API Data Structures".