MySQL:基于二进制权限为导航选择项

Question

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

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

​

以此类推。

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

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

到目前一切尚好。我现在的问题是，通过MySQL从表中选择相应的字段。我的第一种方法是使用access_level <= (用户access_level之和)选择所有条目，但很快我意识到这也包括了其他具有较低access_level的页面。

Answer 1

您必须使用二进制文件并查看是否设置了该位。您还可以使用set字段。它也是一个位字段，它为每个位命名。

就像这样：

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>