MYSQL無限級分類表設計及自我串連

標籤：sql   昆明   為什麼   weight   城市   join   logs   arch   聯動   

我們有這樣一個需求：

做一個城市聯動，比如說貴州省，省下面有眾多市，市下面有眾多區縣，區縣下面有眾多鎮，如果用多張表來儲存，必然是能夠簡單的實現聯動效果，但是多表的聯集查詢必然會影響到一些效率，此時可以考慮

用一張表來實現。

還有當我們要分的級數是不確定的，或者是很多的，那麼建立多張表也是不合理的設計。

如何用一張表來儲存呢?

該表的建立代碼如下：

 1 create table city(id smallint unsigned auto_increment primary key, 2 city_name varchar(20) not null ,parent_id smallint unsigned not null); 3  4 insert into city(city_name,parent_id) values("貴州",0); 5 insert into city(city_name,parent_id) values("貴陽",1); 6 insert into city(city_name,parent_id) values("遵義",1); 7 insert into city(city_name,parent_id) values("銅仁",1); 8 insert into city(city_name,parent_id) values("雲南",0); 9 insert into city(city_name,parent_id) values("昆明",4);10 insert into city(city_name,parent_id) values("麗江",5);11 insert into city(city_name,parent_id) values("四川",0);12 insert into city(city_name,parent_id) values("成都",8);13 insert into city(city_name,parent_id) values("浙江",0);14 insert into city(city_name,parent_id) values("寧波",10);15 insert into city(city_name,parent_id) values("河南",0);16 insert into city(city_name,parent_id) values("鄭州",12);17 insert into city(city_name,parent_id) values("開封",12);18 insert into city(city_name,parent_id) values("安陽",12);

我用了三個欄位，其中parent_id儲存的是父級的id，相當於是一個指標，指向父級。比如貴州，id為1，其中有貴陽，遵義，銅仁的parent_id為1，表示的含義為貴州省下有這三個城市。

為什麼要有parent_id來指向父節點呢？我們來看看下面這張表。

就這張表而言，你能看出什麼來？能看出成都是四川下的一個 市嗎？所以parent_id欄位是必不可少的。

整張表是一個森林的資料結構，那麼我們怎麼查詢到一個省下有多少個城市呢?

select p.id,p.city_name,s.city_name from city as p left join city as s on p.id=s.parent_id;

結果如下：

其中

select p.id,p.city_name,s.city_name表示查詢出來的表只顯示著三個欄位，p代表父表，s代表子表。那麼明明只有一張表，從何而來父表與子表呢？這就是所謂的自身串連。

你可以在腦海中想像另外一張表，該表和city表一模一樣，然後像操作兩張表一樣將他們通過條件串連起來，此時我的條件就是p.id=s.parent_id;

left join表示左串連，意思就是父表的資料全部顯示，子表的資料如果不存在則顯示為空白，比如上面顯示的，鄭州，我們通過p.id=s.parent_id沒有查詢到父表的id與子表的parent_id相同，於是父表的資料（13，鄭州）

完全顯示出來，子表的資料顯示為空白。

MYSQL無限級分類表設計及自我串連