Pre-General Availability Draft: 2017-10-20
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.
SET column values that consist of multiple
set members are specified with members separated by commas
,). A consequence of this is that
SET member values should not themselves
For example, a column specified as
NOT NULL can have any of these values:
'' 'one' 'two' 'one,two'
SET column can have a maximum
of 64 distinct members.
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
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
column like this:
mysql> SELECT set_col+0 FROM tbl_name;
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
members have the following decimal and binary values.
|Decimal Value||Binary Value|
If you assign a value of
9 to this column,
1001 in binary, so the first and
SET value members
'd' are selected
and the resulting value is
For a value containing more than one
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
mysql> CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
If you insert the values
mysql> 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
mysql> 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 is issued:
mysql> 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 error.
SET values are sorted numerically.
NULL values sort before
mysql> SELECT * FROM tbl_name WHERE FIND_IN_SET('value',set_col)>0; mysql> SELECT * FROM tbl_name WHERE set_col LIKE '%value%';
The first statement finds rows where
set_col contains the
value set member. The second is
similar, but not the same: It finds rows where
value anywhere, even as a substring
of another set member.
The following statements also are permitted:
mysql> SELECT * FROM tbl_name WHERE set_col & 1; mysql> SELECT * FROM tbl_name WHERE set_col = 'val1,val2';
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
returns different results than comparing values to
You should specify the values in the same order they are listed
in the column definition.
To determine all possible values for a
SHOW COLUMNS FROM
and parse the
SET definition in the
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 28.7.5, “C API Data Structures”.