Mysql nested set model [province city example]

The parent category contains its subcategories. In a data table, we use the left value and right value representing the node's nested relationship to represent the nested set model.
The data layering feature. We use lft and rgt to replace left and right because left and right are reserved words in MySQL.
Http://dev.mysql.com/doc/mysql/en/reserved-words.html, which has a detailed reserved mysqlword.

So how do we determine the left and right values? We start from the leftmost side of the outer node and start from left to right:

Create table 'region '(
'Id' int (11) not null auto_increment,
'Name' varchar (30) default NULL,
'Parent _ id' int (11) default NULL,
'Lft 'int (10) unsigned default NULL,
'Rgt 'int (10) unsigned default NULL,
Primary key ('id ')
) ENGINE = InnoDB AUTO_INCREMENT = 11 default charset = utf8;

Insert into 'region '('id', 'name', 'parent _ id', 'lft', 'rgt ') values (1, 'China', 0, 1, 20 );
Insert into 'region '('id', 'name', 'parent _ id', 'lft', 'rgt ') values (2, 'beijing', 1, 2, 5 );
Insert into 'region '('id', 'name', 'parent _ id', 'lft', 'rgt ') values (3, 'beijing', 2, 3, 4 );
Insert into 'region '('id', 'name', 'parent _ id', 'lft', 'rgt ') values (4, 'shanghai, 9 );
Insert into 'region '('id', 'name', 'parent _ id', 'lft', 'rgt ') values (5, 'shanghai, 8 );
Insert into 'region '('id', 'name', 'parent _ id', 'lft', 'rgt ') values (6, 'zhejiang, 19 );
Insert into 'region '('id', 'name', 'parent _ id', 'lft', 'rgt ') values (7, 'jinhua City', 6, 15, 16 );
Insert into 'region '('id', 'name', 'parent _ id', 'lft', 'rgt ') values (8, 'wenzhou City', 6, 17, 18 );
Insert into 'region '('id', 'name', 'parent _ id', 'lft', 'rgt ') values (9, 'hangzhou', 6, 11, 12 );
Insert into 'region '('id', 'name', 'parent _ id', 'lft', 'rgt ') values (10, 'ningbo', 6, 13, 14 );

Query all node shards:

SELECT CONCAT(REPEAT('  ', COUNT(parent.id)-1), node.name) AS name, node.id,node.lft,node.rgt, COUNT(parent.id)
FROM region AS node, region AS parent
where  node.lft BETWEEN parent.lft AND parent.rgt
group by node.id
ORDER BY node.lft;

Query the Node path:

Select parent. name, parent. id from region as node, region as parent
Where node. lft BETWEEN parent. lft and parent. rgt and node. name = 'jinhua ';

Reference

Http://dev.mysql.com/tech-resources/articles/hierarchical-data.html