Home | 簡體中文 | 繁體中文 | 雜文 | 打賞(Donations) | ITEYE 博客 | OSChina 博客 | Facebook | Linkedin | 知乎專欄 | Search | About

4.3. 分類表設計

4.3.1. 樹形分類表

			
 +-----------+
 | 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
		
		

4.3.2. 多對多分類

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

		
      +------------+
      | 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      |
      +------------+
		
		

4.3.3. 快速檢索子分類設計

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

			
 +-----------+
 | 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)
			
			

4.3.4. 計算節點數量

			
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
			

4.3.5. Example

例 4.1. identity_card 身份證歸屬地表

CREATE TABLE `identity_card` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '唯一主鍵',
	`pid` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '父ID',
	`path` VARCHAR(50) NOT NULL COMMENT '路徑',
	`number` VARCHAR(18) NOT NULL COMMENT '身份證號碼段',
	`zone` VARCHAR(50) NOT NULL COMMENT '行政區域',
	`status` ENUM('Y','N') NOT NULL DEFAULT 'N' COMMENT '狀態',
	`modified` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '創建與修改時間',
	PRIMARY KEY (`id`),
	INDEX `FK_identity_card_identity_card` (`pid`),
	INDEX `path` (`path`),
	INDEX `number` (`number`),
	CONSTRAINT `FK_identity_card_identity_card` FOREIGN KEY (`pid`) REFERENCES `identity_card` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
)
COMMENT='identity card number'
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
				
"id"	"pid"	"path"	"number"	"zone"	"status"	"modified"
"1012"	"1"	"1.1012"	"330000"	"浙江省"	"Y"	"2012-05-16 17:18:14"
"1041"	"1012"	"1.1012.1041"	"330300"	"溫州市"	"Y"	"2012-05-16 17:44:18"
"1052"	"1041"	"1.1012.1041.1052"	"330381"	"瑞安市"	"Y"	"2012-05-16 17:44:25"
"1367"	"1"	"1.1367"	"360000"	"江西省"	"Y"	"2012-05-16 16:57:23"
"1451"	"1367"	"1.1367.1451"	"360900"	"宜春市"	"Y"	"2012-05-16 17:44:58"
"1990"	"1"	"1.1990"	"430000"	"湖南省"	"Y"	"2012-05-16 16:50:50"
"1991"	"1990"	"1.1990.1991"	"430100"	"長沙市"	"Y"	"2012-05-16 16:50:54"
"2124"	"1990"	"1.1990.2124"	"431300"	"婁底市"	"Y"	"2012-05-16 16:54:45"