mysql位元運算 資料庫設計及應用

標籤：

 

1. 基本概念

如果你不知道什麼是位元運算的話， 那麼請你先去看看基礎的C語言教程吧。
與運算 a & b ,
或運算 a | b ,
異或運算 a ^ b ,

或者
你也可以將 與運算理解為 + 法
例如
1|2 = 3 （1+2 = 3）
1|2|4 = 7 （1+2+4 = 7）

將 異或運算理解為 - 法
例如
3^2 = 1 （3-2 = 1）
3^1 = 2 （3-1 = 2）

最後將 與運算 作為判斷
例如
3&2 = 1 (3 = 1 + 2, 由 1和2組成 ，所以判斷3&2 = 1 )
3&4 = 0 ( 3 沒有由 4組成,所以判斷3&4 = 0)

那麼位元運算有何用處呢， 例如 UNIX系統中的許可權， 通常我們所知 許可權分為 r 讀, w 寫, x 執行,其中 它們的權值分別為4,2,1， 所以 如果使用者要想擁有這三個許可權 就必須 chomd 7 , 即 7=4+2+1 表明 這個使用者具有rwx許可權，如果只想這個使用者具有r,x許可權 那麼就 chomd 5即可

說道此處就要涉及到資料庫了。

通常 我們的資料表中 可能會包含各種狀態屬性， 例如 blog表中 ， 我們需要有欄位表示其是否公開，是否有設定密碼，是否被管理員封鎖，是否被置頂等等。 也會遇到在後期營運中，策劃要求增加新的功能而造成你需要增加新的欄位。

這樣會造成後期的維護困難，資料庫增大，索引增大的情況。 這時使用位元運算就可以巧妙的解決。

例如

<?php
define(‘B_PUBLIC‘,1); // 公開
define(‘B_PASSWORD‘,2); // 加密
define(‘B_LOCK‘,4); // 封鎖
define(‘B_TOP‘,8); // 置頂
?>

-- 公開blog 給status進行或運算
UPDATE blog SET status = status | 1;
-- 加密blog 給status進行或運算
UPDATE blog SET status = status | 2;
-- 封鎖blog
UPDATE blog SET status = status | 4;
-- 解鎖blog
UPDATE blog SET status = status ^ 4;
--查詢所有被置頂的blog
SELECT * FROM blog WHERE status & 8;

雖然節省了空間，但是由於沒有辦法對status欄位使用索引（讀者可以自己試下能否使用索引），所以如何使用來最佳化查詢才是最重點的。

2. 在許可權中的使用

需要建立兩張表：

第一張表：所擁有的許可權 righttags

-- 表的結構 `righttags`
CREATE TABLE IF NOT EXISTS `righttags` (

`Pkid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`RightsName` varchar(20) CHARACTER SET utf8 NOT NULL,
`RightsTag` int(10) NOT NULL,
PRIMARY KEY (`Pkid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

-- 轉存表中的資料 `righttags`

INSERT INTO `righttags` (`Pkid`, `RightsName`, `RightsTag`) VALUES
(1, ‘吃飯‘, 1),
(2, ‘拉屎‘, 2),
(3, ‘打炮‘, 4),
(4, ‘泡妞‘, 8),
(5, ‘生子‘, 16),
(6, ‘包二奶‘, 32);

第二張表：使用者資訊表 rightusers

-- 表的結構 `rightusers`

CREATE TABLE IF NOT EXISTS `rightusers` (
`Pkid` int(10) NOT NULL AUTO_INCREMENT,
`Fuser` varchar(30) CHARACTER SET utf8 NOT NULL,
`UserRights` int(10) NOT NULL,
PRIMARY KEY (`Pkid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

-- 轉存表中的資料 `rightusers`

INSERT INTO `rightusers` (`Pkid`, `Fuser`, `UserRights`) VALUES
(1, ‘張三‘, 7),
(2, ‘李四‘, 9),
(3, ‘王老闆‘, 63),
(4, ‘朱麻子‘, 0);

2.1 訪問判斷是否有許可權

擷取所有可以“打炮”的人

SELECT `RightTags` FROM `righttags` WHERE `RightsName` = "打炮";   （查出RightTags是4）

SELECT * FROM RightUsers WHERE UserRights &4 =4;

此人是否可以打炮(有無記錄集) 

SELECT `RightTags` FROM `righttags` WHERE `RightsName` = "打炮";    （查出RightTags是4）

select * from RightUsers where FUser=‘李四‘ and UserRights&4=4     （沒有結果集）

select * from RightUsers where FUser=‘張三‘ and UserRights&4=4     （有結果集）

2.2 設定許可權

SELECT sum( RightsTag ) FROM RightTags WHERE RightsName IN (‘吃飯‘, ‘拉屎‘, ‘打炮‘); （首先查看需要設定許可權的總和是 7）

update RightUsers set UserRights=7 where FUser=‘張三‘; （更改張三使用者的許可權）

2.3 查看某人的所有許可權 

SELECT UserRights FROM RightUsers WHERE FUser = ‘李四‘;   (首先查看李四使用者的許可權值是 9)

SELECT * FROM RightTags WHERE 9 & RightsTag = RightsTag;  (再查看李四的所擁有的許可權集："吃飯","泡妞")

參考地址：
sqlserver位操作   http://luanxiyuan.iteye.com/blog/1935113

mysql位其他動作   http://blog.sae.sina.com.cn/archives/3506  （注意該文的最後一個查詢博主寫錯了）

mysql位元運算 資料庫設計及應用