存在的问题
存在一个情况,比如周一到周日7天的开关,每天都可以单独开启,但又不想使用多个字段,那么可以使用位运算符帮忙,一个字段搞定( 适用于属性较少的一对多 的场景)。
位运算语法
& : 按位与,二进制位同时都为1的位设为1。
| : 按位或,二进制位有一个位为1就为1.
^ : 按位异或,对应位的二进制数不同时,对应位的结果才为1;如果两个对应位数都为0或者都为1,则对应位的结果为0。
位运算语法例子:
mysql> select LPAD(bin(POW(2,1-1)), 7, 0) as a1,\
LPAD(bin(POW(2,1-1)|POW(2,3-1)), 7, 0) as a13;
+---------+---------+
| a1 | b13 |
+---------+---------+
| 0000001 | 0000101 |
+---------+---------+
1 row in set (0.00 sec)
mysql> select LPAD(bin(POW(2,1-1)), 7, 0) as b1, \
LPAD(bin(POW(2,4-1)|POW(2,7-1)), 7, 0) as b47;
+---------+---------+
| b1 | b47 |
+---------+---------+
| 0000001 | 1001000 |
+---------+---------+
1 row in set (0.00 sec)
mysql> select LPAD(bin(POW(2,1-1)&POW(2,1-1)), 7, 0) as c, \
LPAD(bin((POW(2,1-1)|POW(2,3-1))&(POW(2,4-1)|POW(2,7-1))), 7, 0) as d;
+---------+---------+
| c | d |
+---------+---------+
| 0000001 | 0000000 |
+---------+---------+
1 row in set (0.00 sec)
可以看到&运算和预期是一致的,另外两种可以自己试下。
准备的测试环境:mysql_cook_book
docker run \
-e MYSQL_DATABASE=cookbook \
-e MYSQL_USER=cbuser \
-e MYSQL_PASSWORD=cbpass! \
-e MYSQL_ROOT_PASSWORD=Passw0rd! \
--rm --name mysql_cook_book_tmp \
-itd \
-w /etc/mysql/conf.d/ \
virhuiai/mysql_cook_book:mysql-cnf-v2
如果需要对外开放端口:
docker run \
-e MYSQL_DATABASE=cookbook \
-e MYSQL_USER=cbuser \
-e MYSQL_PASSWORD=cbpass! \
-e MYSQL_ROOT_PASSWORD=Passw0rd! \
--rm --name mysql_cook_book_tmp \
-p 3306:3306 \
-itd \
-w /etc/mysql/conf.d/ \
virhuiai/mysql_cook_book:mysql-cnf-v2
# 等一会再进入容器,需要启动
docker exec -it mysql_cook_book_tmp /bin/bash
# 将密码配置复制过去
cp mysql.cnf.cbuser mysql.cnf
# 进入mysql
mysql
创建示例表
可以使用DataGrip来,之前买过全家桶,最近过期了,下个试用的先用,后面再用docker的 phpMyAdmin 吧。计划后期再写个相关 phpMyAdmin 的教程吧。
配置下Data Source:
现在就创建好连接了:
在cookbook库上右击,New,Table:
当然也可以直接用语句创建表:
create table week_num_switch
(
id bigint auto_increment,
week_num_switch bit(7) default 0 null,
constraint week_num_switch_pk
primary key (id)
);
BIT数据类型简介
MySQL提供了允许您存储位值的 BIT 类型。 BIT(m) 可以存储多达 m 位的值, m 的范围在 1 到 64 之间。
如果省略,默认值为 1 。
mysql> select bin(1),bin(2),bin(3);
+--------+--------+--------+
| bin(1) | bin(2) | bin(3) |
+--------+--------+--------+
| 1 | 10 | 11 |
+--------+--------+--------+
1 row in set (0.00 sec)
mysql> SELECT POW(2,1-1), POW(2,2-1) , POW(2,3-1), POW(2,1-1) + POW(2,2-1);
+------------+------------+------------+-------------------------+
| POW(2,1-1) | POW(2,2-1) | POW(2,3-1) | POW(2,1-1) + POW(2,2-1) |
+------------+------------+------------+-------------------------+
| 1 | 2 | 4 | 3 |
+------------+------------+------------+-------------------------+
周几,就是几减1次方。
准备测试数据
insert into week_num_switch (week_num_switch) values (b'1');
insert into week_num_switch (week_num_switch) values (b'10');
insert into week_num_switch (week_num_switch) values (b'11');
insert into week_num_switch (week_num_switch) values (POW(2,1-1) + POW(2,2-1));
mysql> select * from week_num_switch;
+----+-----------------+
| id | week_num_switch |
+----+-----------------+
| 1 | |
| 2 | |
| 3 | |
| 4 | |
+----+-----------------+
4 rows in set (0.00 sec)
这样是看不到的,要改用:
+----+-------------------+
| id | week_num_switch+0 |
+----+-------------------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 3 |
+----+-------------------+
4 rows in set (0.00 sec)
用bit类型直接查看不到的也有个好处,要不使用int类型也是可以的,但直接看到数值了,多人开发也许就被人误改了。
查看二进制的值:
mysql> select id, bin(week_num_switch) from week_num_switch;
+----+----------------------+
| id | bin(week_num_switch) |
+----+----------------------+
| 1 | 1 |
| 2 | 10 |
| 3 | 11 |
| 4 | 11 |
+----+----------------------+
4 rows in set (0.00 sec)
如果要查看补0的,使用LPAD函数,下面会提到。
查询符合一个的
目标,查询只有周三有开的 :
现在我们再插入几条没有周一的,比如周三和周五:
insert into week_num_switch (week_num_switch) values
(POW(2,3-1)),
(POW(2,5-1)),
(POW(2,3-1) + POW(2,5-1));
mysql> select id, bin(week_num_switch) from week_num_switch;
+----+----------------------+
| id | bin(week_num_switch) |
+----+----------------------+
| 1 | 1 |
| 2 | 10 |
| 3 | 11 |
| 4 | 11 |
| 5 | 100 |
| 6 | 10000 |
| 7 | 10100 |
+----+----------------------+
7 rows in set (0.00 sec)
看不是很明显, 右侧补0 ,我们有7位,参数第二个为7:
mysql> select id, LPAD(bin(week_num_switch), 7, 0) from week_num_switch;
+----+----------------------------------+
| id | LPAD(bin(week_num_switch), 7, 0) |
+----+----------------------------------+
| 1 | 0000001 |
| 2 | 0000010 |
| 3 | 0000011 |
| 4 | 0000011 |
| 5 | 0000100 |
| 6 | 0010000 |
| 7 | 0010100 |
+----+----------------------------------+
7 rows in set (0.00 sec)
那么,查询周三的就是 :
mysql> select (POW(2,3-1));
+--------------+
| (POW(2,3-1)) |
+--------------+
| 4 |
+--------------+
mysql> select id, LPAD(bin(week_num_switch), 7, 0) \
-> from week_num_switch \
-> where week_num_switch&(POW(2,3-1))=(POW(2,3-1));
+----+----------------------------------+
| id | LPAD(bin(week_num_switch), 7, 0) |
+----+----------------------------------+
| 5 | 0000100 |
| 7 | 0010100 |
+----+----------------------------------+
2 rows in set (0.00 sec)
从右向左第三位是1,查到了。
查询多个符合的
查询多个符合的,两个方式。先看下当前数据库内容:
mysql> select id, LPAD(bin(week_num_switch), 7, 0) from week_num_switch;
+----+----------------------------------+
| id | LPAD(bin(week_num_switch), 7, 0) |
+----+----------------------------------+
| 1 | 0000001 |
| 2 | 0000010 |
| 3 | 0000011 |
| 4 | 0000011 |
| 5 | 0000100 |
| 6 | 0010000 |
| 7 | 0010101 |
+----+----------------------------------+
7 rows in set (0.00 sec)
选择id为7的吧,有周一周三和周五的打开了(从右向左数),我们查周一和周三的。
方法一的语法:
where 字段&(值1|值2...)=(值1|值2...)
select id, LPAD(bin(week_num_switch), 7, 0) \
from week_num_switch \
where week_num_switch&(POW(2,1-1)|POW(2,3-1))=(POW(2,1-1)|POW(2,3-1));
+----+----------------------------------+
| id | LPAD(bin(week_num_switch), 7, 0) |
+----+----------------------------------+
| 7 | 0010101 |
+----+----------------------------------+
1 row in set (0.00 sec)
方法二:
where 字段&(值1+值2...)=(值1+值2...)
select id, LPAD(bin(week_num_switch), 7, 0) \
from week_num_switch \
where week_num_switch&(POW(2,1-1)+POW(2,3-1))=(POW(2,1-1)+POW(2,3-1));
+----+----------------------------------+
| id | LPAD(bin(week_num_switch), 7, 0) |
+----+----------------------------------+
| 7 | 0010101 |
+----+----------------------------------+
1 row in set (0.00 sec)
这种情况下| 和 + 的结果是一样的。
查询多个只要符合一个
mysql> select id, LPAD(bin(week_num_switch), 7, 0) \
-> from week_num_switch \
-> where week_num_switch&POW(2,1-1)=(POW(2,1-1)) \
-> or week_num_switch&POW(2,3-1)=(POW(2,3-1));
+----+----------------------------------+
| id | LPAD(bin(week_num_switch), 7, 0) |
+----+----------------------------------+
| 1 | 0000001 |
| 3 | 0000011 |
| 4 | 0000011 |
| 5 | 0000100 |
| 7 | 0010101 |
+----+----------------------------------+
5 rows in set (0.00 sec)
更新-添加
用 | 符号可以添加。
| : 按位或,二进制位有一个位为1就为1.
mysql> SELECT (4|2|1);
+---------+
| (4|2|1) |
+---------+
| 7 |
+---------+
1 row in set (0.00 sec)
现有的数据:
mysql> select id, LPAD(bin(week_num_switch), 7, 0) from week_num_switch;
+----+----------------------------------+
| id | LPAD(bin(week_num_switch), 7, 0) |
+----+----------------------------------+
| 1 | 0000001 |
| 2 | 0000010 |
| 3 | 0000011 |
| 4 | 0000011 |
| 5 | 0000100 |
| 6 | 0010000 |
| 7 | 0010100 |
+----+----------------------------------+
7 rows in set (0.00 sec)
其中id为7的周一是没有设置的,我们给它加个周一打开的状态:
mysql> update week_num_switch \
set week_num_switch=week_num_switch|POW(2,1-1) where id=7;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
再查下现在状态:
select id, LPAD(bin(week_num_switch), 7, 0) from week_num_switch where id=7;
mysql> select id, LPAD(bin(week_num_switch), 7, 0) from week_num_switch where id=7;
+----+----------------------------------+
| id | LPAD(bin(week_num_switch), 7, 0) |
+----+----------------------------------+
| 7 | 0010101 |
+----+----------------------------------+
1 row in set (0.00 sec)
id为7的数据从 0010100 变成了 0010101 ,从右向左的第一位被改为0了。
去掉
用 ^ 符号可以去除。
^ : 按位异或,对应位的二进制数不同时,对应位的结果才为1;如果两个对应位数都为0或者都为1,则对应位的结果为0。
mysql> SELECT (4|2|1) ^ 1;
+-------------+
| (4|2|1) ^ 1 |
+-------------+
| 6 |
+-------------+
1 row in set (0.00 sec)
类似前面的添加一样,这边就省了。