Detailed description of the set type in MySQL

[Set type]
Set is a string object that can have zero or multiple values. Its values come from the allowed column values specified during table creation. When the set column value of multiple set members is specified, each member is separated by commas. Therefore, set Member values cannot contain commas.

For example, a column specified as set ('one', 'two') not null can have any of the following values:
''
'One'
'Two'
'One, two'
A set can have up to 64 different members. When a table is created, spaces at the end of the set Member value are automatically deleted, the values saved in the Set column are displayed using the case in the column definition. Note that you can assign character sets and checking rules to the set column. For binary or case-sensitive checking rules, Case sensitivity should be taken into consideration when assigning values to columns.

[Insert method]
For values that contain multiple set elements, the sequence listed in the element is not important when values are inserted. It does not matter how many times a given element column in the value. When this value is retrieved later, each element in the value appears once. The elements are listed in the order specified during table creation. The set values are sorted in numerical order. The null value is placed before the non-null set value. For example, assume that a column is specified as set ('A', 'B', 'C', 'D '):

Mysql> Create Table myset (COL set ('A', 'B', 'C', 'D '));

Insert values 'a, D', 'd, A', 'a, D, D', 'a, 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

All these values are displayed as 'a, d ':

Mysql> select Col from myset;

+ ------ +

| Col |

+ ------ +

| A, d |

| A, d |

| A, d |

| A, d |

| A, d |

+ ------ +

5 rows in SET (0.04 Sec)

If the set column is set 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)

[Query]
In general, you can use the find_in_set () function or the like operator to search for the set value:

Mysql> select * From tbl_name where find_in_set ('value', set_col)> 0;

Mysql> select * From tbl_name where set_col like '% value % ';

Find the row where set_col contains the value set Member in the 1st statements. 2nd is similar, but different: it finds the row where set_col contains value elsewhere, or even in the substring of another set member.

The following statement is also valid:

Mysql> select * From tbl_name where set_col & 1;

Mysql> select * From tbl_name where set_col = 'val1, val2 ';

The first statement is used to search for values that contain 1st set members. The first statement looks for an exact matched value. Pay attention to the comparison of the 2nd categories. Compare the returned results of the set value with 'val1, val2' and the same 'val2, val1. The order of the specified value should be the same as that of the column in the column definition.

To determine all possible values for the set column, use show columns fromtbl_name likeset_col and parse the set definition of the 2nd column in the output.