mysql 無限級分類實現思路

第一種方案：
使用遞迴演算法，也是使用頻率最多的，大部分開來源程式也是這麼處理，不過一般都只用到四級分類。這種演算法的資料庫結構設計最為簡單。category表中一個欄位id，一個欄位fid(父id)。這樣可以根據WHERE id = fid來判斷上一級內容，運用遞迴至最頂層。
分析：通過這種資料庫設計出的無限級，可以說讀取的時候相當費勁，所以大部分的程式最多3-4級分類，這就足以滿足需求，從而一次性讀出所有的資料，再對得到數組或者對象進行遞迴。本身負荷還是沒太大問題。但是如果分類到更多級，那是不可取的辦法。
這樣看來這種分類有個好處，就是增刪改的時候輕鬆了…然而就二級分類而言，採用這種演算法就應該算最優先了。

第二種方案：
設定fid欄位類型為varchar，將父類id都集中在這個欄位裡，用符號隔開，比如：1,3,6
這樣可以比較容易得到各上級分類的ID，而且在查詢分類下的資訊的時候，
可以使用：SELECT * FROM category WHERE pid LIKE “1,3%”。

分析：相比於遞迴演算法，在讀取資料方面優勢非常大，但是若尋找該分類的所有 父分類 或者 子分類 查詢的效率也不是很高，至少也要二次query，從某種意義看上，個人覺得不太符合資料庫範式的設計。倘若遞增到無限級，還需考慮欄位是否達到要求，而且在修改分類和轉移分類的時候操作將非常麻煩。
暫時，在自己項目中用的就是類似第二種方案的解決辦法。就該方案在我的項目中存在這樣的問題， 如果當所有資料記錄達到上萬甚至10W以上後，一次性將所以分類，有序分級的現實出來，效率很低。極有可能是項目處理資料代碼效率低帶來的。現在正在改良。
第三種方案：
　　無限級分類----改進前序走訪樹
那麼理想中的樹型結構應具備哪些特點呢？資料存放區冗餘小、直觀性強；方便返回整個樹型結構資料；可以很輕鬆的返回某一子樹（方便分層載入）；快整獲以某節點的祖譜路徑；插入、刪除、移動節點效率高等等。帶著這些需求我尋找了很多資料，發現了一種理想的樹型結構資料存放區及操作演算法，改進的前序走訪樹模型（The Nested Set Model）。
原理：
我們先把樹按照水平方式擺開。從根節點開始（“Food”），然後他的左邊寫上1。然後按照樹的順序（從上到下）給“Fruit”的左邊寫上2。這樣，你沿著樹的邊界走啊走（這就是“遍曆”），然後同時在每個節點的左邊和右邊寫上數字。最後，我們回到了根節點“Food”在右邊寫上18。下面是標上了數位樹，同時把遍曆的順序用箭頭標出來了。

我們稱這些數字為左值和右值（如，“Food”的左值是1，右值是18）。正如你所見，這些數字按時了每個節點之間的關係。因為“Red”有3和6兩個值，所以，它是有擁有1-18值的“Food”節點的後續。同樣的，我們可以推斷所有左值大於2並且右值小於11的節點，都是有2-11的“Fruit” 節點的後續。這樣，樹的結構就通過左值和右值儲存下來了。這種數遍整棵樹算節點的方法叫做“改進前序走訪樹”演算法。

表結構設計：

那麼我們怎樣才能通過一個SQL語句把所有的分類都查詢出來呢，而且要求如果是子類的話前面要打幾個空格以表現是子分類。要想查詢出所有分類很好辦：SELECT * FROM category WHERE lft>1 AND lft<18 ORDER BY lft這樣的話所有的分類都出來了，但是誰是誰的子類卻分不清，那麼怎麼辦呢?我們仔細看圖不難發現如果相鄰的兩條記錄的右值第一條的右值比第二條的大那麼就是他的父類，比如food的右值是18而fruit的右值是11 那麼food是fruit的父類，但是又要考慮到多級目錄。於是有了這樣的設計，我們用一個數組來儲存上一條記錄的右值，再把它和本條記錄的右值比較，如果前者比後者小，說明不是父子關係，就用array_pop彈出數組，否則就保留，之後根據數組的大小來列印空格。這樣就解決了這個問題。代碼如下
表結構： 複製代碼 代碼如下:--
-- 表的結構 `category`
--
CREATE TABLE IF NOT EXISTS `category` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`type` int(11) NOT NULL COMMENT '1為文章類型2為產品類型3為下載類型',
`title` varchar(50) NOT NULL,
`lft` int(11) NOT NULL,
`rgt` int(11) NOT NULL,
`lorder` int(11) NOT NULL COMMENT '排序',
`create_time` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=10 ;
--
-- 匯出表中的資料 `category`
--
INSERT INTO `category` (`id`, `type`, `title`, `lft`, `rgt`, `lorder`, `create_time`) VALUES
(1, 1, '頂級欄目', 1, 18, 1, 1261964806),
(2, 1, '公司簡介', 14, 17, 50, 1264586212),
(3, 1, '新聞', 12, 13, 50, 1264586226),
(4, 2, '公司產品', 10, 11, 50, 1264586249),
(5, 1, '榮譽資質', 8, 9, 50, 1264586270),
(6, 3, '資料下載', 6, 7, 50, 1264586295),
(7, 1, '人才招聘', 4, 5, 50, 1264586314),
(8, 1, '留言板', 2, 3, 50, 1264586884),
(9, 1, '總裁', 15, 16, 50, 1267771951);

/**
* 顯示樹,把所有的節點都顯示出來。
* 1、先得到根結點的左右值（預設根節點的title為“頂級目錄”）。
* 2、查詢左右值在根節點的左右值範圍內的記錄，並且根據左值排序。
* 3、如果本次記錄右值大於前次記錄的右值則為子分類，輸出時候加空格。
* @return array
**/
function display_tree(){

//獲得root左邊和右邊的值
$arr_lr = $this->category->where("title = '頂級欄目'")->find();
//print_r($arr_lr);
if($arr_lr){
$right = array();
$arr_tree = $this->category->query("SELECT id, type, title, rgt FROM category WHERE lft >= ". $arr_lr['lft'] ." AND lft <=".$arr_lr['rgt']." ORDER BY lft");
foreach($arr_tree as $v){
if(count($right)){
while ($right[count($right) -1] < $v['rgt']){
array_pop($right);
}
}
$title = $v['title'];
if(count($right)){
$title = '|-'.$title;
}
$arr_list[] = array('id' => $v['id'], 'type' => $type, 'title' => str_repeat(' ', count($right)).$title, 'name' =>$v['title']);
$right[] = $v['rgt'];
}
return $arr_list;
}
}

好了 只要這樣所有的分類都可以一次性查詢出來了，而不用通過遞迴了。
下面的問題是怎樣進行插入、刪除和修改操作
插入:插入操作很簡單找到其父節點，之後把左值和右值大於父節點左值的節點的左右值加上2，之後再插入本節點，左右值分別為父節點左值加一和加二，可以用一個預存程序來操作： 複製代碼 代碼如下:CREATE PROCEDURE `category_insert_by_parent`(IN pid INT,IN title VARCHAR(20), IN type INT, IN l_order INT, IN pubtime INT)
BEGIN
DECLARE myLeft INT;
SELECT lft into myLeft FROM category WHERE id= pid;
UPDATE qy_category SET rgt = rgt + 2 WHERE rgt > myLeft;
UPDATE qy_category SET lft = lft + 2 WHERE lft > myLeft;
INSERT INTO qy_category(type, title, lft, rgt, lorder, create_time) VALUES(type ,title, myLeft + 1, myLeft + 2, l_order, pubtime);
commit;
END

刪除操作：
刪除的原理：1.得到要刪除節點的左右值，並得到他們的差再加一，@mywidth = @rgt - @lft + 1;
2.刪除左右值在本節點之間的節點
3.修改條件為大於本節點右值的所有節點，操作為把他們的左右值都減去@mywidth
預存程序如下： 複製代碼 代碼如下:CREATE PROCEDURE `category_delete_by_key`(IN id INT)
BEGIN
SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
FROM category
WHERE id = id;
DELETE FROM category WHERE lft BETWEEN @myLeft AND @myRight;
UPDATE nested_category SET rgt = rgt - @myWidth WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft - @myWidth WHERE lft > @myRight;

修改：
要命的修改操作，本人看了很久也沒有看出什麼規律出來，只要出此下策，先刪除再插入，只要調用上面2個預存程序就可以了！
總結：查詢方便，但是增刪改操作有點繁瑣，但是一般分類此類操作不是很多，還是查詢用的多，再說弄個預存程序也方便！

　　上面第三種方案具體講解類容是從http://home.phpchina.com/space.php?uid=45095&do=blog&id=184675拷貝過來，方便以後自己查看。 暫時從各方面及理論上考慮 偏向於第三方案。不過還沒有做過測試，到底效率怎麼樣。
期待更好的解決方案！