-- ----------------------------
-- Table structure for setenum
-- ----------------------------
DROP TABLE IF EXISTS `setenum`;
CREATE TABLE `setenum` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`settype` set('we','周','李','孙','钱','赵') DEFAULT NULL,
`enumtype` enum('ZZZ','南海','长江','黄河') DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of setenum
-- ----------------------------
INSERT INTO `setenum` VALUES ('1', 'we,周,钱', '南海');
INSERT INTO `setenum` VALUES ('2', '钱,赵', '黄河');
INSERT INTO `setenum` VALUES ('3', 'we,赵', '南海');
INSERT INTO `setenum` VALUES ('4', '李,孙,钱', '长江');
set('we','周','李','孙','钱','赵')=111111=63
enum('ZZZ','南海','长江','黄河')=100=4
如下表所示：
| SET类型：低位（右） → 高位（左） | |||||
| we |
周 |
李 |
孙 |
钱 |
赵 |
| 1 |
1 |
1 |
1 |
1 |
1 |
| 从右到左排：111111=63 | |||||
|
|
|
|
|
|
|
| we |
周 |
李 |
孙 |
钱 |
赵 |
| 1 |
1 |
0 |
0 |
1 |
0 |
| 从右到左排：010011=19 | |||||
|
|
|
|
|
|
|
| we |
周 |
李 |
孙 |
钱 |
赵 |
| 0 |
0 |
0 |
0 |
1 |
1 |
| 从右到左排：110000=48 | |||||
|
|
|
|
|
|
|
| we |
周 |
李 |
孙 |
钱 |
赵 |
| 1 |
0 |
0 |
0 |
0 |
1 |
| 从右到左排：100001=33 | |||||
|
|
|
|
|
|
|
| we |
周 |
李 |
孙 |
钱 |
赵 |
| 0 |
0 |
1 |
1 |
1 |
0 |
| 从右到左排：011100=28 | |||||
| ENUM类型 | |||
| ZZZ |
南海 |
长江 |
黄河 |
| 1 |
2 |
3 |
4 |
|
|
|
|
|
| ZZZ |
南海 |
长江 |
黄河 |
| 1 |
2 |
3 |
4 |
| 黄河=4=100 | |||
|
|
|
|
|
| ZZZ |
南海 |
长江 |
黄河 |
| 1 |
2 |
3 |
4 |
| 长江=3=11 | |||
|
|
|
|
|
| ZZZ |
南海 |
长江 |
黄河 |
| 1 |
2 |
3 |
4 |
| 南海=2=10 | |||
|
|
|
|
|
| ZZZ |
南海 |
长江 |
黄河 |
| 1 |
2 |
3 |
4 |
| ZZZ=1=1 | |||
mysql> select * from setenum;
+----+----------+----------+
| id | settype | enumtype |
+----+----------+----------+
| 1 | we,周,钱 | 南海 |
| 2 | 钱,赵 | 黄河 |
| 3 | we,赵 | 南海 |
| 4 | 李,孙,钱 | 长江 |
+----+----------+----------+
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,周,钱 | 19 | 10011 | 南海 | 2 | 10 |
| 钱,赵 | 48 | 110000 | 黄河 | 4 | 100 |
| we,赵 | 33 | 100001 | 南海 | 2 | 10 |
| 李,孙,钱 | 28 | 11100 | 长江 | 3 | 11 |
+----------+-----------+----------------+----------+------------+-----------------+
4 rows in set
mysql> select * from setenum where settype=33;
+----+---------+----------+
| id | settype | enumtype |
+----+---------+----------+
| 3 | we,赵 | 南海 |
+----+---------+----------+
1 row in set
mysql> select * from setenum where enumtype=2;
+----+----------+----------+
| id | settype | enumtype |
+----+----------+----------+
| 1 | we,周,钱 | 南海 |
| 3 | we,赵 | 南海 |
+----+----------+----------+
2 rows in set
--不支持二进制查询
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 '%赵%';
+----+---------+----------+
| id | settype | enumtype |
+----+---------+----------+
| 2 | 钱,赵 | 黄河 |
| 3 | we,赵 | 南海 |
+----+---------+----------+
2 rows in set
--与FIND_IN_SET函数同
mysql> SELECT * FROM setenum WHERE FIND_IN_SET('赵',settype)>0;
+----+---------+----------+
| id | settype | enumtype |
+----+---------+----------+
| 2 | 钱,赵 | 黄河 |
| 3 | we,赵 | 南海 |
+----+---------+----------+
2 rows in set
--当查询只是集合某个值的一部分时，与FIND_IN_SET函数不同
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,周,钱 | 南海 |
| 3 | we,赵 | 南海 |
+----+----------+----------+
2 rows in set
mysql> SELECT * FROM setenum WHERE settype LIKE '赵';
Empty set
mysql> SELECT * FROM setenum WHERE settype = '赵';
Empty set
mysql> SELECT * FROM setenum WHERE settype LIKE 'we,赵';
+----+---------+----------+
| id | settype | enumtype |
+----+---------+----------+
| 3 | we,赵 | 南海 |
+----+---------+----------+
1 row in set
mysql> SELECT * FROM setenum WHERE settype = 'we,赵';
+----+---------+----------+
| id | settype | enumtype |
+----+---------+----------+
| 3 | we,赵 | 南海 |
+----+---------+----------+
1 row in set
--如果把集合的顺序改一下，照样无效
mysql> SELECT * FROM setenum WHERE settype LIKE '赵,we';
Empty set
mysql> SELECT * FROM setenum WHERE settype = '赵,we';
Empty set
mysql> SELECT * FROM setenum WHERE enumtype LIKE '%海%';
+----+----------+----------+
| id | settype | enumtype |
+----+----------+----------+
| 1 | we,周,钱 | 南海 |
| 3 | we,赵 | 南海 |
+----+----------+----------+
2 rows in set
mysql> SELECT * FROM setenum WHERE enumtype = '南海';
+----+----------+----------+
| id | settype | enumtype |
+----+----------+----------+
| 1 | we,周,钱 | 南海 |
| 3 | we,赵 | 南海 |
+----+----------+----------+
2 rows in set
mysql> SELECT * FROM setenum WHERE enumtype = '海';
Empty set
--------------------UPDATE 语法--------------------
set('we','周','李','孙','钱','赵')=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,周,钱 | 19 | 10011 | 南海 | 2 | 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) |
+---------+-----------+----------------+----------+------------+-----------------+
| 周 | 2 | 10 | 南海 | 2 | 10 |
+---------+-----------+----------------+----------+------------+-----------------+
1 row in set
--修改settype让其'we'、'周'、'李' 成员为真
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 表示 二进制的OR运算 1 or 100 or 110 = 111 = 7
--实际与 1|6 结果是一样的：1 or 110 = 111 = 7
| we |
周 |
李 |
孙 |
钱 |
赵 |
| 1 |
1 |
1 |
0 |
0 |
0 |
| 从右到左排：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,周,李 | 7 | 111 | 南海 | 2 | 10 |
+----------+-----------+----------------+----------+------------+-----------------+
1 row in set
--实际与 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,周,李 | 7 | 111 | 南海 | 2 | 10 |
+----------+-----------+----------------+----------+------------+-----------------+
1 row in set
--settype|1|6 的settype 可以省略，结果一样
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,周,李 | 7 | 111 | 南海 | 2 | 10 |
+----------+-----------+----------------+----------+------------+-----------------+
1 row in set
-- &表示与运算，1 and 110 = 0，注意0与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 | 南海 | 2 | 10 |
+---------+-----------+----------------+----------+------------+-----------------+
1 row in set
-- &表示与运算，~表示反运算，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 | 南海 | 2 | 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 | 南海 | 2 | 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 |
周 |
李 |
孙 |
钱 |
赵 |
| 0 |
0 |
1 |
0 |
0 |
0 |
| 从右到左排：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) |
+---------+-----------+----------------+----------+------------+-----------------+
| 李 | 4 | 100 | 南海 | 2 | 10 |
+---------+-----------+----------------+----------+------------+-----------------+
1 row in set