GNU Info

(mysql.info)SET

---

Prev: ENUM Up: String types

---

Enter node , (file) or (file)node

The `SET' Type
..............

A `SET' is a string object that can have zero or more values, each of
which must be chosen from a list of allowed 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 cannot themselves
contain commas.

For example, a column specified as `SET("one", "two") NOT NULL' can have
any of these values:

     ""
     "one"
     "two"
     "one,two"

A `SET' can have a maximum of 64 different members.

MySQL stores `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:

     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.  Suppose a column is specified as `SET("a","b","c","d")'.
Then the members have the following bit values:

`SET'          *Decimal       *Binary value*
*member*       value*         
`a'            `1'            `0001'
`b'            `2'            `0010'
`c'            `4'            `0100'
`d'            `8'            `1000'

If you assign a value of `9' to this column, that is `1001' in binary,
so the first and fourth `SET' value members `"a"' and `"d"' are
selected and the resulting value is `"a,d"'.

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
will appear once, with elements listed according to the order in which
they were specified at table creation time.  For example, if a column
is specified as `SET("a","b","c","d")', then `"a,d"', `"d,a"', and
`"d,a,a,d,d"' will all appear as `"a,d"' when retrieved.

`SET' values are sorted numerically.  `NULL' values sort before
non-`NULL' `SET' values.

Normally, you perform a `SELECT' on a `SET' column using the `LIKE'
operator or the `FIND_IN_SET()' function:

     mysql> SELECT * FROM tbl_name WHERE set_col LIKE '%value%';
     mysql> SELECT * FROM tbl_name WHERE FIND_IN_SET('value',set_col)>0;

But the following will also work:

     mysql> SELECT * FROM tbl_name WHERE set_col = 'val1,val2';
     mysql> SELECT * FROM tbl_name WHERE set_col & 1;

The first of these statements looks for an exact match.  The second
looks for values containing the first set member.

If you want to get all possible values for a `SET' column, you should
use: `SHOW COLUMNS FROM table_name LIKE set_column_name' and parse the
`SET' definition in the second column.