MySQL Tutorial/Data Types/Set

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

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>

Looking for an exact match in a set column

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

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)

Looking for values containing the first set member

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)

MySQL stores SET and ENUM as numbers

They can be manipulated using numeric operations.

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>

Search for SET values using the FIND_IN_SET() function

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

Search for SET values using the LIKE operator

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

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>

SET column type

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)

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.

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>

Using the SET type

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>