mysql 位运算 一对多_MySQL位运算的应用

我们知道，PHP当中的错误级别常量，是根据二进制位特性而确定的一个个整数，可以简单的通过位运算定制PHP的错误报告。我们也可以将其应用到 MySQL 当中！

你是否遇到过下面这样的情况？

一、用户可能有若干不同属性或不同状态，然后你可能会在数据表中通过一个个字段去标记和实现，比如：

id

int(11)

用户ID(自增)

username

char(50)

用户名

password

char(50)

密码

...

...

...

confirm_info

tinyint(1)

是否确认资料 [0/1]

confirm_contract

tinyint(1)

是否确认协议 [0/1]

freeze

tinyint(1)

冻结状态 [0/1]

payer

tinyint(1)

笔记是否付费用户 [0/1]

high_quality

tinyint(1)

标记是否优质用户 [0/1]

二、用户拥有多个用户组(角色)，常见的实现方法有：

1、增加一个字段，专门存它的用户组，用逗号隔开

id

int(11)

用户ID(自增)

username

char(50)

用户名

password

char(50)

密码

...

...

...

confirm_info

tinyint(1)

是否确认资料 [0/1]

confirm_contract

tinyint(1)

是否确认协议 [0/1]

freeze

tinyint(1)

冻结状态 [0/1]

payer

tinyint(1)

笔记是否付费用户 [0/1]

high_quality

tinyint(1)

标记是否优质用户 [0/1]

role_id

varchar(255)

如：1,3,11

使用 FIND_IN_SET() 或 LIKE 进行数据查询。这种存储方式，虽然比较直观，结构也比较接单，但是，但是查询和维护数据都不方便

2、增加一个关联表

user_id

int(11)

用户ID

role_id

int(11)

角色ID

...

...

...

通过JOIN，连表查询。这种存储方式，似乎很好扩展，无论是增加了用户组，还是重新给用户划分用户组，直接操作这张关联表就行。但是，连表查询终究是需要操作多张表的，效率肯定不如单表查询，而且如果用户属性多了(不仅限于分组)，那就得关联更多的表或者查多次，使查询变得更加复杂！

我们知道，PHP当中的错误级别常量，是根据二进制位特性而确定的一个个整数，可以简单的通过位运算定制PHP的错误报告。我们也可以将其应用到 MySQL 当中，还是以上面的用户表为例，我们只用一字段 status，专门记录用户状态，其结构如下：

id

int(11)

用户ID(自增)

username

char(50)

用户名

password

char(50)

密码

...

...

...

status

int(11)

用户状态位

然后我们给每一种状态设定一个对应的值，这个值需要使用2的N次方来表示，比如：

1

已确认资料

2

已确认协议

4

已冻结

8

付费用户

16

优质用户

查询技巧示例：

1、查询所有已冻结的用户：

SELECT * FROM `user` WHERE `status` & 4 = 4

2、查询所有未确认协议的用户

SELECT * FROM `user` WHERE `status` & 2 = 0

3、设置ID等于186的用户为优质用户

UPDATE `user` SET `status` = `status` | 16 WHERE `id` = 186

4、取消ID等于186的优质用户身份

UPDATE `user` SET `status` = `status` ^ 16 WHERE `id` = 186

5、查询已冻结的优质付费用户(4 + 8 + 16 = 28)

SELECT * FROM `user` WHERE `status` & 28 = 28

同理，上述应用场景二中的分组问题，也可以使用同样的方法，优化表结构！然后使用位运算，实现各种条件的查询！