Design and application of MySQL bit arithmetic database

1. Basic Concepts

If you don't know what a bitwise operation is, then you should look at the basic C-language tutorial first.
With operations A & B,
or operation a | B
Xor operation a ^ B,

Or
You can also think of arithmetic as the + method
For example
1|2 = 3 (1+2 = 3)
1|2|4 = 7 (1+2+4 = 7)

To interpret an XOR operation as a-method
For example
3^2 = 1 (3-2 = 1)
3^1 = 2 (3-1 = 2)

At last, it is judged by the operation
For example
3&2 = 1 (3 = 1 + 2, consisting of 1 and 2, so judge 3&2 = 1)
3&4 = 0 (3 not made up of 4, so judge 3&4 = 0)

So what is the use of bit arithmetic, such as permissions in Unix systems, usually we know that the permissions are divided into R read, w write, x execution, where their weights are 4,2,1, so if the user wants to have these three permissions must CHOMD 7, that 7=4+2+1 indicates that the user has RWX permissions, if only want this user has r,x permission then CHOMD 5 can

It says there's a database involved.

Usually our data table may contain various state attributes, such as the blog table, we need to have a field to indicate whether it is public, whether there is a password set, whether the administrator blocked, whether it is pinned, and so on. Also encountered in the post OPS, planning requires adding new functionality and causing you to add new fields.

This results in later maintenance difficulties, increased database size, and increased indexing. At this point, the use of bit arithmetic can be cleverly solved.

For example

<?php
Define (' B_public ', 1); Public
Define (' B_password ', 2); Encryption
Define (' B_lock ', 4); Blocked
Define (' B_top ', 8); Top
?>

--Public blogs for status or operations
UPDATE Blog SET status = Status | 1;
--Encrypt the blog to the status or operation
UPDATE Blog SET status = Status | 2;
--Block Blog
UPDATE Blog SET status = Status | 4;
--Unlock Blog
UPDATE Blog SET status = status ^ 4;
--Query All the blogs that are pinned
SELECT * FROM blog WHERE status & 8;

Although space is saved, there is no way to use the index for the Status field (readers can try to use the index on their own), so how to use to optimize the query is the most important.

2. Use in Permissions

Two tables need to be created:

First table: Permissions owned by Righttags

--The structure of the table ' Righttags '
CREATE TABLE IF not EXISTS ' righttags ' (

' Pkid ' int (ten) unsigned not NULL auto_increment,
' Rightsname ' varchar (CHARACTER) SET UTF8 not NULL,
' Rightstag ' int (ten) is not NULL,
PRIMARY KEY (' Pkid ')
) Engine=innodb DEFAULT charset=latin1 auto_increment=7;

--Dump the data in the table ' Righttags '

INSERT into ' righttags ' (' Pkid ', ' rightsname ', ' Rightstag ') VALUES
(1, ' eat ', 1),
(2, ' poop ', 2),
(3, ' Tai Ba ', 4),
(4, ' Girls ', 8),
(5, ' Born son ', 16),
(6, ' Pack Mistress ', 32);

Table Two: User Information table Rightusers

--The structure of the table ' Rightusers '

CREATE TABLE IF not EXISTS ' rightusers ' (
' Pkid ' int (ten) not NULL auto_increment,
' Fuser ' varchar (+) CHARACTER SET UTF8 not NULL,
' Userrights ' int (ten) is not NULL,
PRIMARY KEY (' Pkid ')
) Engine=innodb DEFAULT charset=latin1 auto_increment=5;

--Dump the data in the table ' Rightusers '

INSERT into ' rightusers ' (' Pkid ', ' Fuser ', ' userrights ') VALUES
(1, ' Zhang San ', 7),
(2, ' John Doe ', 9),
(3, ' boss Wang ', 63),
(4, ' Zhu Maozi ', 0);

2.1 Access to determine if permission is available

Get all the people who can "Tai Ba"

Select ' Righttags ' from ' righttags ' WHERE ' rightsname ' = ' Tai Ba '; (Isolated righttags is 4)

SELECT * from rightusers WHERE userrights &4 = 4;

Whether this person can Tai Ba (with or without a recordset)

Select ' Righttags ' from ' righttags ' WHERE ' rightsname ' = ' Tai Ba '; (Isolated righttags is 4)

SELECT * from rightusers where fuser= ' John Doe ' and userrights&4=4 (no result set)

SELECT * from Rightusers where fuser= ' Zhang San ' and userrights&4=4 (with result set)

2.2 Setting permissions

SELECT sum (rightstag) from Righttags WHERE rightsname in (' Eat ', ' poop ', ' Tai Ba '); (First look at the sum of the permissions you need to set is 7)

Update rightusers set userrights=7 where fuser= ' Zhang San '; (Change permissions for Zhang San users)

2.3 View all permissions for a person

SELECT userrights from rightusers WHERE FUser = ' John Doe '; (First look at the John Doe user's permission value is 9)

SELECT * from Righttags WHERE 9 & rightstag = Rightstag; (then see John Doe's Set of permissions: "Eat", "pick up the girls")

Reference Address:
SQL Server bit Operation http://luanxiyuan.iteye.com/blog/1935113

MySQL bit other operation http://blog.sae.sina.com.cn/archives/3506 (note the last query of this article the blogger wrote the wrong)

Design and application of MySQL bit arithmetic database