(mysql.info) set

Info Catalog

(mysql.info) enum

(mysql.info) string-types

 
 11.4.5 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 should not 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.
 
 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.
 
 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. For a column specified as `SET('a','b','c','d')', the
 members have the following decimal and binary 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
 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 `SET('a','b','c','d')':
 
      mysql> CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
 
 If you insert the values `'a,d'', `'d,a'', `'a,d,d'', `'a,d,a'', and
 `'d,a,d'':
 
      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 of these values appear as `'a,d'' when retrieved:
 
      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
 non-`NULL' `SET' values.
 
 Normally, you search for `SET' values using the `FIND_IN_SET()'
 function or the `LIKE' operator:
 
      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
 SET_COL contains VALUE anywhere, even as a substring of another set
 member.
 
 The following statements also are legal:
 
      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 values to `'VAL1,VAL2''
 returns different results than comparing values to `'VAL2,VAL1''.  You
 should specify the values in the same order they are listed in the
 column definition.
 
 If you want to determine all possible values for a `SET' column, use
 `SHOW COLUMNS FROM TBL_NAME LIKE SET_COL' and parse the `SET'
 definition in the `Type' column of the output.
 

Info Catalog

(mysql.info) enum

(mysql.info) string-types