mysql字段掩码,通过特权位掩码从MySQL数据库中选择用户？

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.

解决方案[...] 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