php - MySQL:根据二进制权限选择导航项

Question

我已经建立了一个基于二进制加法的简单权限管理。

我的数据库中的每个导航条目都遵循以下结构:

等等..

每个用户都有自己的访问级别..

用户 = 3(1+2 - 访问级别)管理员 = 15 (1+2+4+8)

到目前为止一切顺利。我现在的问题是，通过MySQL从表中选择相应的字段。我的第一个方法是选择 access_level <= 用户 access_level 总和的所有条目，但很快我意识到这还包括具有较低 access_level 的其他页面。

Answer 1

您必须使用二进制 AND 来查看该位是否已设置。您还可以使用设置字段。它也是一个位域，对每个位进行命名。

像这样:

WHERE (access_level & 15);

示例 - 创建表格并填充

mysql> CREATE TABLE `access_table` (
    ->   `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   `item_text` VARCHAR(32) DEFAULT NULL,
    ->   `url` VARCHAR(32) DEFAULT NULL,
    ->   `access_level` SET('home','help','backend','admin') DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=INNODB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0,02 sec)

mysql>
mysql> INSERT INTO `access_table` (`id`, `item_text`, `url`, `access_level`)
    -> VALUES
    ->     (1, 'home', '/home', 'home'),
    ->     (2, 'help', '/help', 'help'),
    ->     (3, 'backend', '/backend', 'backend'),
    ->     (4, 'hoadmpage', '/admin', 'admin');
Query OK, 4 rows affected (0,00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql>

示例 - 显示所有行并选择一些通过位

mysql> SELECT *,access_level+0 FROM ACCESS_TABLE;
+----+-----------+----------+--------------+----------------+
| id | item_text | url      | access_level | access_level+0 |
+----+-----------+----------+--------------+----------------+
|  1 | home      | /home    | home         |              1 |
|  2 | help      | /help    | help         |              2 |
|  3 | backend   | /backend | backend      |              4 |
|  4 | hoadmpage | /admin   | admin        |              8 |
+----+-----------+----------+--------------+----------------+
4 rows in set (0,00 sec)

mysql>
mysql> SELECT *,access_level+0
    -> FROM ACCESS_TABLE
    -> WHERE (access_level & 15);
+----+-----------+----------+--------------+----------------+
| id | item_text | url      | access_level | access_level+0 |
+----+-----------+----------+--------------+----------------+
|  1 | home      | /home    | home         |              1 |
|  2 | help      | /help    | help         |              2 |
|  3 | backend   | /backend | backend      |              4 |
|  4 | hoadmpage | /admin   | admin        |              8 |
+----+-----------+----------+--------------+----------------+
4 rows in set (0,00 sec)

mysql> SELECT *,access_level+0
    -> FROM ACCESS_TABLE
    -> WHERE (access_level & 7);
+----+-----------+----------+--------------+----------------+
| id | item_text | url      | access_level | access_level+0 |
+----+-----------+----------+--------------+----------------+
|  1 | home      | /home    | home         |              1 |
|  2 | help      | /help    | help         |              2 |
|  3 | backend   | /backend | backend      |              4 |
+----+-----------+----------+--------------+----------------+
3 rows in set (0,00 sec)

mysql> SELECT *,access_level+0
    -> FROM ACCESS_TABLE
    -> WHERE (access_level & 9);
+----+-----------+--------+--------------+----------------+
| id | item_text | url    | access_level | access_level+0 |
+----+-----------+--------+--------------+----------------+
|  1 | home      | /home  | home         |              1 |
|  4 | hoadmpage | /admin | admin        |              8 |
+----+-----------+--------+--------------+----------------+
2 rows in set (0,00 sec)

mysql>