MySQL Tutorial/Data Types/Set

If you set a SET column to an unsupported value, the value is ignored and a warning is issued

   <source lang="sql">

mysql> mysql> CREATE TABLE myset (col SET("a", "b", "c", "d")); Query OK, 0 rows affected (0.03 sec) 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.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> mysql> SELECT col FROM myset; +------+ | col | +------+ | a,d | | a,d | | a,d | | a,d | | a,d | +------+ 5 rows in set (0.00 sec) mysql> mysql> INSERT INTO myset (col) VALUES ("a,d,d,s"); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> mysql> SHOW WARNINGS; +---------+------+------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------+ | Warning | 1265 | Data truncated for column "col" at row 1 | +---------+------+------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT col FROM myset; +------+ | col | +------+ | a,d | | a,d | | a,d | | a,d | | a,d | | a,d | +------+ 6 rows in set (0.00 sec) mysql> mysql> mysql> drop table myset; Query OK, 0 rows affected (0.00 sec) mysql></source>

Looking for an exact match in a set column

Comparing set values to "val1,val2" returns different results than comparing values to "val2,val1".

   <source lang="sql">

mysql> mysql> mysql> SELECT * FROM tbl_name WHERE set_col = "val1,val2"; mysql> mysql> mysql> CREATE TABLE myset (col SET("a", "b", "c", "d")); Query OK, 0 rows affected (0.02 sec) 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.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> SELECT col FROM myset; +------+ | col | +------+ | a,d | | a,d | | a,d | | a,d | | a,d | +------+ 5 rows in set (0.00 sec) mysql> mysql> SELECT * FROM myset WHERE col = "a,d"; +------+ | col | +------+ | a,d | | a,d | | a,d | | a,d | | a,d | +------+ 5 rows in set (0.00 sec) mysql> mysql> drop table myset; Query OK, 0 rows affected (0.00 sec)</source>

Looking for values containing the first set member

   <source lang="sql">

mysql> mysql> CREATE TABLE myset (col SET("a", "b", "c", "d")); Query OK, 0 rows affected (0.05 sec) 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.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> SELECT col FROM myset; +------+ | col | +------+ | a,d | | a,d | | a,d | | a,d | | a,d | +------+ 5 rows in set (0.00 sec) mysql> mysql> SELECT * FROM myset WHERE col & 1; +------+ | col | +------+ | a,d | | a,d | | a,d | | a,d | | a,d | +------+ 5 rows in set (0.00 sec) mysql> mysql> drop table myset; Query OK, 0 rows affected (0.00 sec)</source>

MySQL stores SET and ENUM as numbers

They can be manipulated using numeric operations.

   <source lang="sql">

mysql> mysql> CREATE Table Test(

   ->     Advertiser SET("A","B","C")
   -> );

Query OK, 0 rows affected (0.03 sec) mysql> mysql> mysql> INSERT INTO Test (Advertiser) values("A, B"); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> mysql> mysql> SELECT * FROM Test WHERE Advertiser = 1; +------------+ | Advertiser | +------------+ | A | +------------+ 1 row in set (0.02 sec) mysql> mysql> SELECT Advertiser, Advertiser + 0 FROM Test; +------------+----------------+ | Advertiser | Advertiser + 0 | +------------+----------------+ | A | 1 | +------------+----------------+ 1 row in set (0.00 sec) mysql> mysql> drop table Test; Query OK, 0 rows affected (0.00 sec) mysql></source>

Search for SET values using the FIND_IN_SET() function

   <source lang="sql">

SELECT * FROM tbl_name WHERE FIND_IN_SET("value",set_col)>0;</source>

Search for SET values using the LIKE operator

SELECT * FROM tbl_name WHERE set_col LIKE "%value%";

   <source lang="sql">

mysql> mysql> mysql> CREATE TABLE myset (col SET("a", "b", "c", "d")); Query OK, 0 rows affected (0.03 sec) 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.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> SELECT col FROM myset; +------+ | col | +------+ | a,d | | a,d | | a,d | | a,d | | a,d | +------+ 5 rows in set (0.00 sec) mysql> mysql> select * from myset where col like "%a%"; +------+ | col | +------+ | a,d | | a,d | | a,d | | a,d | | a,d | +------+ 5 rows in set (0.00 sec) mysql> mysql> drop table myset; Query OK, 0 rows affected (0.01 sec) mysql></source>

SET column type

   <source lang="sql">

mysql> mysql> CREATE TABLE myTable

   -> (
   ->    ID SMALLINT UNSIGNED,
   ->    Model VARCHAR(40),
   ->    Color ENUM("red", "blue", "green", "yellow"),
   ->    Options SET("rack", "light", "helmet", "lock")
   -> );

Query OK, 0 rows affected (0.03 sec) mysql> mysql> desc myTable; +---------+-------------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------------------------------+------+-----+---------+-------+ | ID | smallint(5) unsigned | YES | | NULL | | | Model | varchar(40) | YES | | NULL | | | Color | enum("red","blue","green","yellow") | YES | | NULL | | | Options | set("rack","light","helmet","lock") | YES | | NULL | | +---------+-------------------------------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> mysql> drop table myTable; Query OK, 0 rows affected (0.02 sec)</source>

SET Type

The SET type stores a list of values.

A SET is a string object that can have zero or more values.

In a SET type, you can choose more than one option to store.

A SET type can contain up to 64 items.

Each of which must be chosen from a list of values.

Multiple set members are separated by commas (",").

SET member values should not contain commas.

   <source lang="sql">

mysql> mysql> mysql> CREATE Table Test(

   ->     Advertiser SET("A","B","C")
   -> );

Query OK, 0 rows affected (0.03 sec) mysql> mysql> mysql> INSERT INTO Test (Advertiser) values("A, B"); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> mysql> mysql> drop table Test; Query OK, 0 rows affected (0.02 sec) mysql></source>

Using the SET type

   <source lang="sql">

mysql> mysql> CREATE TABLE myset (col SET("a", "b", "c", "d")); Query OK, 0 rows affected (0.05 sec) 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.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> SELECT col FROM myset; +------+ | col | +------+ | a,d | | a,d | | a,d | | a,d | | a,d | +------+ 5 rows in set (0.00 sec) mysql> mysql> drop table myset; Query OK, 0 rows affected (0.00 sec) mysql></source>