SELECT users from MySQL database by privileges bitmask?

Question

I have users table and want to SELECT some rows by bitmask criteria. I'll try to explain my problem with small example.

Structure of table users

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
+---------+--------------------+-----------------+

Now I want to SELECT users by specific privileges bitmask (by user_privileges column).

For example:

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

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.

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.

Thanx in advance.

Zane Bien · Answer 1 · 2012-06-15T23:53:10.470

17

SELECT
    *
FROM
    users
WHERE
    (user_privileges & <level>) = <level>

<level> being the access level you want to search on (e.g. 1, 5, 9, 130, etc.)

edited Jun 15 '12 at 23:53

answered Jun 15 '12 at 23:37

Zane Bien

22,685
6
45
57

I don't see how this would work. There can be extra privileges than the level, in which case your equality will fail, but that wasn't the desired result. e.g As example given clearly states. (10100101 & 00000001) != 00000001 but that is a desired result. – Hrishikesh Jun 16 '12 at 00:05
Hmm... I'm not sure if I fully understand. I may be missing something completely as I am rather new to bitwise, but my query selects all of the proper rows based on the OPs example... so what am I not getting? – Zane Bien Jun 16 '12 at 00:48
5

@Hrishikesh : `10100101 & 00000001` equals `00000001`. It's a bitwise and, so for every bitpair where both are `1`, you get `1` back. It's a bitwise `AND`. [see e.g. this calculator](http://www.miniwebtool.com/bitwise-calculator/?data_type=10&number1=10100101&number2=00000001&operator=AND) – Nanne Feb 08 '13 at 16:10

score 15 · Accepted Answer · answered Jun 15 '12 at 23:23

[...] want to SELECT some fields

Wrong. You want to select some Rows. Columns are usually called fields.

You are supposed to read the Documentation: Bit Functions are documented for mysql.

So you can try:

Select * from users WHERE (user_privileges & 1) >0

SELECT users from MySQL database by privileges bitmask?

2 Answers2

Linked