利用mysql位运算实现一对多数据关系

当我们在设计数据库的数据表时,经常会遇到一对多的数据结构,如文章的tag等,比如本人之前在设计手机app的后端的时候,就碰到了这个问题,

当时涉及中小学教材的诗歌诗歌表(poetry)和出版社表(poetry_press),因为一首诗可能会涉及多个出版社,而且出版社的数量并不是很多。为了便于搜索,如果把所有出版社的ID用分隔符组合成一个字符串肯定不符合要求。所以一般都是单独使用一个表来存储他们的对应关系,每次修改的时候,还必须先清除所有的对应数据,然后重新插入,非常麻烦,为了保证数据的一致性,还必须用事物。很显然,对于关联数据种类很少的情况下,这两种方法都不好。

在linux里,读写运行权限用421来表示,这就是一个典型的一对多的场景,比如用7就可以表示同时拥有3种权限,7表示读写权限。居于此,我们便设计了两个数据表

mysql> describe poetry;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | int(4) unsigned     | NO   | PRI | NULL    | auto_increment |
| sTitle   | varchar(255)        | NO   |     | NULL    |                |
| sContent | text                | YES  |     | NULL    |                |
| nSort    | tinyint(1) unsigned | NO   |     | 0       |                |
| nStatus  | tinyint(1) unsigned | NO   |     | 0       |                |
| nPress   | int(4) unsigned     | NO   |     | 0       |                |
+----------+---------------------+------+-----+---------+----------------+
6 rows in set (0.03 sec)
mysql> describe poetry_press;
+---------+---------------------+------+-----+---------+----------------+
| Field   | Type                | Null | Key | Default | Extra          |
+---------+---------------------+------+-----+---------+----------------+
| id      | int(4) unsigned     | NO   | PRI | NULL    | auto_increment |
| sName   | varchar(255)        | NO   |     |         |                |
| nSort   | tinyint(1) unsigned | NO   |     | 0       |                |
| nStatus | tinyint(1) unsigned | NO   |     | 0       |                |
+---------+---------------------+------+-----+---------+----------------+
4 rows in set (0.02 sec)

为了说明一下,我们添加点数据,添加后数据如下,

mysql> select * from poetry_press;
+----+--------------+-------+---------+
| id | sName        | nSort | nStatus |
+----+--------------+-------+---------+
|  1 | 人教版       |     0 |       1 |
|  2 | 北师大版     |     0 |       1 |
|  3 | 新课标       |     0 |       1 |
|  4 | 思泉版       |     0 |       1 |
|  5 | 苏教版       |     0 |       1 |
+----+--------------+-------+---------+
5 rows in set (0.02 sec)

那么,假如一首诗属于人教版和北师大版,则加起来数字为2^1+2^2 = 6.如果属于人教版,新课标,苏教版,则为2^1+2^3+2^5 = 42.

我们如果要查询人教版的诗歌,则可以

 SELECT * FROM `poetry` WHERE `nPress` & 2 = 2

如果我们要查既属于人教版,又属于北师大版,则sql语句为

SELECT * FROM `poetry` WHERE `nPress` & 6 = 6

假如我们要给第3条记录添加人北师大版本,则sql语句为

UPDATE `poetry` SET `nPress` = `nPress` | 4 WHERE `id` = 3

假如我们要给第3条记录删除人北师大版本,则sql语句为

UPDATE `poetry` SET `nPress` = `nPress` ^ 2 WHERE `id` = 3