+-----------+
 | category  |
 |-----------|
 |id         | <---+
 |title      |     |
 |description|    1:n
 |status     |     |
 |parent_id  | o---+
 +-----------+
			
			

		
CREATE TABLE `category` (
	`id` SMALLINT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(10) NOT NULL,
	`description` VARCHAR(255) NULL,
	`status` ENUM('enable','desable') NOT NULL DEFAULT 'enable',
	`parent_id` SMALLINT(10) UNSIGNED NOT NULL DEFAULT '0',
	PRIMARY KEY (`id`),
	CONSTRAINT `FK1` FOREIGN KEY (`parent_id`) REFERENCES `category` (`id`)
)
COMMENT='goods category'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT
		
		

多對多分類,主要用於滿足，一個產品/文章屬於多個分類的需求。

		
      +------------+
      | category   |
      |------------|
 +--> |id          | <---+
 |    |title       |     |     +----------------------+
1:n   |description |    1:n    | categroy_has_product |
 |    |status      |     |     +----------------------+
 +--o |parent_id   |     |     | id                   |
      +------------+     +---o | category_id          |
                         +---o | product_id           |
      +------------+     |     +----------------------+
      | product    |    1:n
      +------------+     |
      |id          | <---+
      |price       |
      |quantity    |
      |...         |
      |status      |
      +------------+
		
		

上面我剛剛講過怎樣實現“不限子樹的分類樹”，我們可以實現不限層次的無線分類表。

			
 +-----------+
 | category  |
 |-----------|
 |id         | <---+
 |title      |     |
 |description|    1:n
 |status     |     |
 |parent_id  | o---+
 +-----------+
			
			

問題出來了，當我需要讀取一個分類（任意分類）下的所有子分類，怎樣實現，很多人會說用“遞歸”。 當然“遞歸”可是現實我們的需求，在幾百個分類的項目中，使用遞歸也不是不可以的，但是當數量非常龐大時怎麼辦？

當然有更好的解決方案，請看下面

			
 +-----------+
 | category  |
 |-----------|
 |id         | <---+
 |title      |     |
 |description|    1:n
 |status     |     |
 |parent_id  | o---+
 |path       |
 +-----------+
			
			

			
+-------------------------------------------------------------------------+
| category                                                                |
+----+-----------+-----------------------+--------+-----------+-----------+
| id | name      | description           | status | parent_id | path      |
+----+-----------+-----------------------+--------+-----------+-----------+
|  1 | 中國    | 中華人民共和家                                    | Y      |      NULL | 1/        |
|  4 | 廣東省 | 廣東省                                                      | Y      |         1 | 1/4       |
|  5 | 深圳市 | NULL                      | Y      |         4 | 1/4/5     |
|  6 | 寶安區 | NULL                      | Y      |         5 | 1/4/5/6   |
|  7 | 龍華鎮 | NULL                      | Y      |         6 | 1/4/5/6/7 |
+----+-----------+-----------------------+--------+-----------+-----------+
			
			

			
CREATE TABLE `category` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '分類ID',
	`name` VARCHAR(50) NOT NULL COMMENT '分類名稱',
	`description` VARCHAR(200) NULL DEFAULT NULL COMMENT '分類描述',
	`status` ENUM('Y','N') NOT NULL DEFAULT 'Y' COMMENT '分類狀態有繼承性',
	`parent_id` INT(10) NULL DEFAULT '1' COMMENT '分類父ID',
	`path` VARCHAR(255) NOT NULL COMMENT '分類遞歸路徑索引',
	INDEX `PK` (`id`),
	INDEX `relation` (`id`, `parent_id`),
	INDEX `FK_category_category` (`parent_id`),
	INDEX `path` (`path`)
)
COMMENT='分類表'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT
AUTO_INCREMENT=0

insert into category(`name`,`description`,`status`,`parent_id`,`path`) values('中國','中華人民共和家','Y',null,'1/')
			
			

			
ALTER TABLE `category`
	ADD CONSTRAINT `FK_category_category` FOREIGN KEY (`parent_id`) REFERENCES `category` (`id`)
			
			

抽取廣東子樹

			
select * from category where path like '1/4%';
			
			

			
mysql> select * from category where path like '1/4%';
+----+-----------+-------------+--------+-----------+-----------+
| id | name      | description | status | parent_id | path      |
+----+-----------+-------------+--------+-----------+-----------+
|  4 | 廣東省 | 廣東省   | Y      |         1 | 1/4       |
|  5 | 深圳市 | NULL        | Y      |         4 | 1/4/5     |
|  6 | 寶安區 | NULL        | Y      |         5 | 1/4/5/6   |
|  7 | 龍華鎮 | NULL        | Y      |         6 | 1/4/5/6/7 |
+----+-----------+-------------+--------+-----------+-----------+
4 rows in set (0.00 sec)
			
			

			
DROP TABLE IF EXISTS `test`;
CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) DEFAULT NULL,
  `pid` int(11) DEFAULT NULL,
  `name` char(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test` (`id`, `pid`, `name`) VALUES
	(1, 0, 'A'),
	(2, 1, 'B'),
	(3, 1, 'C'),
	(4, 0, 'D'),
	(5, 0, 'E'),
	(6, 5, 'F');

select (select t2.name from test t2 where t2.id=t1.pid) as name, count(pid) as sum from test t1 where t1.pid <> 0 group by t1.pid;
			
			

統計所有節點包括數量為零的

select t1.name, (select count(t2.name) from test t2 where t2.pid=t1.id) as sum from test t1