web123456

Detailed explanation of the use of SET and ENUM types in MySQL

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for setenum
-- ----------------------------
DROP TABLE IF EXISTS `setenum`;
CREATE TABLE `setenum` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `settype` set('we','week','plum','Sun','money','Zhao') DEFAULT NULL,
  `enumtype` enum('ZZZ','South China Sea','Yangtze','Yellow River') DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of setenum
-- ----------------------------
INSERT INTO `setenum` VALUES ('1', 'we, week, money', 'South China Sea');
INSERT INTO `setenum` VALUES ('2', 'Money, Zhao', 'Yellow River');
INSERT INTO `setenum` VALUES ('3', 'we, Zhao', 'South China Sea');
INSERT INTO `setenum` VALUES ('4', 'Li, Sun, Money', 'Yangtze');


set('we','week','plum','Sun','money','Zhao')=111111=63
enum('ZZZ','South China Sea','Yangtze','Yellow River')=100=4

As shown in the following table:

SET type: low (right) → high (left)

we

week

plum

Sun

money

Zhao

1

1

1

1

1

1

From right to left row: 11111=63

 

 

 

 

 

 

we

week

plum

Sun

money

Zhao

1

1

0

0

1

0

From right to left row: 010011=19

 

 

 

 

 

 

we

week

plum

Sun

money

Zhao

0

0

0

0

1

1

From right to left row: 110000=48

 

 

 

 

 

 

we

week

plum

Sun

money

Zhao

1

0

0

0

0

1

Row from right to left: 100001=33

 

 

 

 

 

 

we

week

plum

Sun

money

Zhao

0

0

1

1

1

0

From right to left row: 011100=28



ENUM Type

ZZZ

South China Sea

Yangtze

Yellow River

1

2

3

4

 

 

 

 

ZZZ

South China Sea

Yangtze

Yellow River

1

2

3

4

Yellow River=4=100

 

 

 

 

ZZZ

South China Sea

Yangtze

Yellow River

1

2

3

4

Yangtze River=3=11

 

 

 

 

ZZZ

South China Sea

Yangtze

Yellow River

1

2

3

4

South China Sea=2=10

 

 

 

 

ZZZ

South China Sea

Yangtze

Yellow River

1

2

3

4

ZZZ=1=1




mysql> select * from setenum;
+----+----------+----------+
| id | settype  | enumtype |
+----+----------+----------+
|  1| we, Zhou, Qian | Nanhai      |
|  2| Qian, Zhao     | Yellow River     |
|  3| we, Zhao     | Nanhai     |
|  4| Li, Sun, Qian | Changjiang
+----+----------+----------+
4 rows in set

mysql> select settype,settype+0,bin(settype+0),enumtype,enumtype+0,bin(enumtype+0) from setenum;
+----------+-----------+----------------+----------+------------+-----------------+
| settype  | settype+0 | bin(settype+0) | enumtype | enumtype+0 | bin(enumtype+0) |
+----------+-----------+----------------+----------+------------+-----------------+
| we,we,we,money |19 | 10011| Nanhai2 | 10              |
| Qian, Zhao48 | 110000| Yellow River4 | 100             |
| we, Zhao     |33 | 100001| Nanhai2 | 10              |
|Li,Sun,Qian |28 | 11100| Yangtze River3 | 11              |
+----------+-----------+----------------+----------+------------+-----------------+
4 rows in set

mysql> select * from setenum where settype=33;
+----+---------+----------+
| id | settype | enumtype |
+----+---------+----------+
|  3| we, Zhao    | Nanhai     |
+----+---------+----------+
1 row in set

mysql> select * from setenum where enumtype=2;
+----+----------+----------+
| id | settype  | enumtype |
+----+----------+----------+
|  1| we, Zhou, Qian | Nanhai      |
|  3| we, Zhao     | Nanhai     |
+----+----------+----------+
2 rows in set

--Binary query is not supported
mysql> select * from setenum where settype=b'010011';
Empty set

mysql> select * from setenum where settype=b'10011';
Empty set

mysql> select * from setenum where enumtype=b'100';
Empty set


mysql> SELECT * FROM setenum WHERE settype LIKE '%Zhao%';
+----+---------+----------+
| id | settype | enumtype |
+----+---------+----------+
|  2| Qian, Zhao    | Yellow River     |
|  3| we, Zhao    | Nanhai     |
+----+---------+----------+
2 rows in set

--Same as FIND_IN_SET function
mysql> SELECT * FROM setenum WHERE FIND_IN_SET('Zhao',settype)>0;
+----+---------+----------+
| id | settype | enumtype |
+----+---------+----------+
|  2| Qian, Zhao    | Yellow River     |
|  3| we, Zhao    | Nanhai     |
+----+---------+----------+
2 rows in set


--When a query is only part of a collection of a value, it is different from the FIND_IN_SET function
mysql> SELECT * FROM setenum WHERE FIND_IN_SET('e',settype)>0;
Empty set

mysql> SELECT * FROM setenum WHERE settype LIKE '%e%';
+----+----------+----------+
| id | settype  | enumtype |
+----+----------+----------+
|  1| we, Zhou, Qian | Nanhai      |
|  3| we, Zhao     | Nanhai     |
+----+----------+----------+
2 rows in set


mysql> SELECT * FROM setenum WHERE settype LIKE 'Zhao';
Empty set

mysql> SELECT * FROM setenum WHERE settype = 'Zhao';
Empty set

mysql> SELECT * FROM setenum WHERE settype LIKE 'we, Zhao';
+----+---------+----------+
| id | settype | enumtype |
+----+---------+----------+
|  3| we, Zhao    | Nanhai     |
+----+---------+----------+
1 row in set

mysql> SELECT * FROM setenum WHERE settype = 'we, Zhao';
+----+---------+----------+
| id | settype | enumtype |
+----+---------+----------+
|  3| we, Zhao    | Nanhai     |
+----+---------+----------+
1 row in set


--If you change the order of the collection, it will still be invalid
mysql> SELECT * FROM setenum WHERE settype LIKE 'Zhao,we';
Empty set

mysql> SELECT * FROM setenum WHERE settype = 'Zhao,we';
Empty set



mysql> SELECT * FROM setenum WHERE enumtype LIKE '%ocean%';
 
+----+----------+----------+
| id | settype  | enumtype |
+----+----------+----------+
|  1| we, Zhou, Qian | Nanhai      |
|  3| we, Zhao     | Nanhai     |
+----+----------+----------+
2 rows in set

mysql> SELECT * FROM setenum WHERE enumtype = 'South China Sea';
+----+----------+----------+
| id | settype  | enumtype |
+----+----------+----------+
|  1| we, Zhou, Qian | Nanhai      |
|  3| we, Zhao     | Nanhai     |
+----+----------+----------+
2 rows in set

mysql> SELECT * FROM setenum WHERE enumtype = 'ocean';
Empty set



--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

set('we','week','plum','Sun','money','Zhao')=111111=63

mysql> select settype,settype+0,bin(settype+0),enumtype,enumtype+0,bin(enumtype+0) from setenum where id=1;
+----------+-----------+----------------+----------+------------+-----------------+
| settype  | settype+0 | bin(settype+0) | enumtype | enumtype+0 | bin(enumtype+0) |
+----------+-----------+----------------+----------+------------+-----------------+
| we,we,we,money |19 | 10011| Nanhai2 | 10              |
+----------+-----------+----------------+----------+------------+-----------------+
1 row in set

mysql> update setenum set settype = 2 where id=1;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select settype,settype+0,bin(settype+0),enumtype,enumtype+0,bin(enumtype+0) from setenum where id=1;
+---------+-----------+----------------+----------+------------+-----------------+
| settype | settype+0 | bin(settype+0) | enumtype | enumtype+0 | bin(enumtype+0) |
+---------+-----------+----------------+----------+------------+-----------------+
| Zhou2 | 10| Nanhai2 | 10              |
+---------+-----------+----------------+----------+------------+-----------------+
1 row in set

--Modify the settype to make its members 'we', 'zhou', 'Li' are true
mysql> update setenum set settype =settype|1|4|6 where id=1;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

--|1|4|6 represents binary OR operation 1 or 100 or 110 = 111 = 7
--The actual result is the same as 1|6: 1 or 110 = 111 = 7

we

week

plum

Sun

money

Zhao

1

1

1

0

0

0

From right to left row: 000111=7



mysql> select settype,settype+0,bin(settype+0),enumtype,enumtype+0,bin(enumtype+0) from setenum where id=1;
+----------+-----------+----------------+----------+------------+-----------------+
| settype  | settype+0 | bin(settype+0) | enumtype | enumtype+0 | bin(enumtype+0) |
+----------+-----------+----------------+----------+------------+-----------------+
| we, Zhou, Li |7 | 111| Nanhai2 | 10              |
+----------+-----------+----------------+----------+------------+-----------------+
1 row in set

--The actual result is the same as 1|6: 1 or 110 = 111 = 7
mysql> update setenum set settype =settype|1|6 where id=1;
Query OK, 0 rows affected
Rows matched: 1  Changed: 0  Warnings: 0

mysql> select settype,settype+0,bin(settype+0),enumtype,enumtype+0,bin(enumtype+0) from setenum where id=1;
+----------+-----------+----------------+----------+------------+-----------------+
| settype  | settype+0 | bin(settype+0) | enumtype | enumtype+0 | bin(enumtype+0) |
+----------+-----------+----------------+----------+------------+-----------------+
| we, Zhou, Li |7 | 111| Nanhai2 | 10              |
+----------+-----------+----------------+----------+------------+-----------------+
1 row in set


--settype|1|6 settype can be omitted, the result is the same
mysql> update setenum set settype = 1|6 where id=1;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select settype,settype+0,bin(settype+0),enumtype,enumtype+0,bin(enumtype+0) from setenum where id=1;
+----------+-----------+----------------+----------+------------+-----------------+
| settype  | settype+0 | bin(settype+0) | enumtype | enumtype+0 | bin(enumtype+0) |
+----------+-----------+----------------+----------+------------+-----------------+
| we, Zhou, Li |7 | 111| Nanhai2 | 10              |
+----------+-----------+----------------+----------+------------+-----------------+
1 row in set


-- & represents and operation, 1 and 110 = 0, note that 0 is different from NULL
mysql> update setenum set settype = 1 & 6 where id=1;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select settype,settype+0,bin(settype+0),enumtype,enumtype+0,bin(enumtype+0) from setenum where id=1;
+---------+-----------+----------------+----------+------------+-----------------+
| settype | settype+0 | bin(settype+0) | enumtype | enumtype+0 | bin(enumtype+0) |
+---------+-----------+----------------+----------+------------+-----------------+
|         |         0 | 0| Nanhai2 | 10              |
+---------+-----------+----------------+----------+------------+-----------------+
1 row in set


--& means and operation, ~ means inverse operation, 6=110, ~6=001, 1 and 001 = 1
mysql> update setenum set settype = null where id=1;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select settype,settype+0,bin(settype+0),enumtype,enumtype+0,bin(enumtype+0) from setenum where id=1;
+---------+-----------+----------------+----------+------------+-----------------+
| settype | settype+0 | bin(settype+0) | enumtype | enumtype+0 | bin(enumtype+0) |
+---------+-----------+----------------+----------+------------+-----------------+
| NULL    | NULL      | NULL| Nanhai2 | 10              |
+---------+-----------+----------------+----------+------------+-----------------+
1 row in set

mysql> update setenum set settype = 1 & ~6 where id=1;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select settype,settype+0,bin(settype+0),enumtype,enumtype+0,bin(enumtype+0) from setenum where id=1;
+---------+-----------+----------------+----------+------------+-----------------+
| settype | settype+0 | bin(settype+0) | enumtype | enumtype+0 | bin(enumtype+0) |
+---------+-----------+----------------+----------+------------+-----------------+
| we      |         1 | 1| Nanhai2 | 10              |
+---------+-----------+----------------+----------+------------+-----------------+
1 row in set



-- 5 and ~1 = 101 and ~1 = 101 and 111110 = 100 = 4
mysql> update setenum set settype = null where id=1;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update setenum set settype = 5 & ~1 where id=1;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

we

week

plum

Sun

money

Zhao

0

0

1

0

0

0

Row from right to left: 000100=4



mysql> select settype,settype+0,bin(settype+0),enumtype,enumtype+0,bin(enumtype+0) from setenum where id=1;
+---------+-----------+----------------+----------+------------+-----------------+
| settype | settype+0 | bin(settype+0) | enumtype | enumtype+0 | bin(enumtype+0) |
+---------+-----------+----------------+----------+------------+-----------------+
|Li4 | 100| Nanhai2 | 10              |
+---------+-----------+----------------+----------+------------+-----------------+
1 row in set