通过特权位掩码从MySQL数据库中选择用户? [英] SELECT users from MySQL database by privileges bitmask?
问题描述
我有一张用户表，想按位掩码标准选择一些行.我将尝试通过一个小例子来说明我的问题.
I have users table and want to SELECT some rows by bitmask criteria. I'll try to explain my problem with small example.
表用户
user_id int [primary key, auto_increment]
user_email varchar(200)
user_privileges int
注意:该字段具有更多字段，但与该问题无关.
Note: It has more fields but they are irrelevant for this question.
填满的表可能看起来像这样
Filled table may look like this
+---------+--------------------+-----------------+
| user_id | user_email | user_privileges | << binary
+---------+--------------------+-----------------+
| 1 | john@example.com | 165 | 10100101
| 2 | max@example.com | 13 | 00001101
| 3 | trevor@example.com | 33 | 00100001
| 4 | paul@example.com | 8 | 00001000
| 5 | rashid@example.com | 5 | 00000101
+---------+--------------------+-----------------+
现在，我想通过特定的权限位掩码(通过user_privileges列)来选择用户.
Now I want to SELECT users by specific privileges bitmask (by user_privileges column).
例如:
- bitmask = 1 [00000001]将选择用户ID 1、2、3和5
- bitmask = 9 [00001001]仅选择用户ID 2
- bitmask = 5 [00000101]将选择用户ID 1、2和5
- bitmask = 130 [10000010]将选择无
- bitmask=1 [00000001] would select user-ids 1, 2, 3 and 5
- bitmask=9 [00001001] would select user-id 2 only
- bitmask=5 [00000101] would select user-ids 1, 2 and 5
- bitmask=130 [10000010] would select none
我的问题:是否可以通过查询进行操作，或者我必须一个接一个地检查所有用户，然后从PHP代码中检查该值?另外，是否 user_privileges 字段是text，包含十六进制数字而不是整数?我需要工作的mysql查询示例.
My question: Is it possible from query or I have to go all users one-by-one and check this value from PHP code? Also, is it possible if field user_privileges is text, containing hexadecimal numbers, instead of integers? I need working mysql query example.
注意:这只是一个具有8位权限集的简单示例.在实际环境中，它可能具有更大的集合(更大的整数，更多的字节).为每个特权状态创建单独的列可以正常工作，但这不是解决方案.我宁愿使用十六进制值，但整数也可以，东西总比没有好.
Note: This is just a simple example with 8-bit privilege-set. In real environment it may have larger sets (greater integers, more bytes). Creating separate column for each privilege state works fine, but that's not possible solution. I'd rather work with hex values, but integers are fine too, something is better than nothing.
先感谢
推荐答案
[...]要选择一些字段
[...] want to SELECT some fields
错了.您想要选择一些行.列通常称为字段.
Wrong. You want to select some Rows. Columns are usually called fields.
您应该阅读文档:位函数记录了以下内容: mysql.
You are supposed to read the Documentation: Bit Functions are documented for mysql.
因此您可以尝试:
Select * from users WHERE (user_privileges & 1) >0
这篇关于通过特权位掩码从MySQL数据库中选择用户?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
