MySQL Infinite class classification table design and self-connection

We have such a requirement:

To do a city linkage, such as Guizhou Province, the province below there are many cities, the city below there are many counties and counties, the county below there are many towns, if more than one table to store, is bound to be able to easily realize the linkage effect, but the multi-table joint query will inevitably affect some efficiency, at this time can consider

Use a table to achieve.

And when we want to divide the series is uncertain, or a lot of, then the establishment of more than one table is also unreasonable design.

How to use a table to store it?

The build code for this table is as follows:

1 Create TableCity (IDsmallintunsigned auto_incrementPrimary Key,2City_namevarchar( -) not NULL, parent_idsmallintUnsigned not NULL);3 4 Insert  intoCity (CITY_NAME,PARENT_ID)Values("Guizhou",0);5 Insert  intoCity (CITY_NAME,PARENT_ID)Values("Guiyang",1);6 Insert  intoCity (CITY_NAME,PARENT_ID)Values("Zunyi",1);7 Insert  intoCity (CITY_NAME,PARENT_ID)Values("Tongren",1);8 Insert  intoCity (CITY_NAME,PARENT_ID)Values("Yunnan",0);9 Insert  intoCity (CITY_NAME,PARENT_ID)Values("Kunming",4);Ten Insert  intoCity (CITY_NAME,PARENT_ID)Values("Lijiang",5); One Insert  intoCity (CITY_NAME,PARENT_ID)Values("Sichuan",0); A Insert  intoCity (CITY_NAME,PARENT_ID)Values("Chengdu",8); - Insert  intoCity (CITY_NAME,PARENT_ID)Values("Zhejiang",0); - Insert  intoCity (CITY_NAME,PARENT_ID)Values("Ningbo",Ten); the Insert  intoCity (CITY_NAME,PARENT_ID)Values("Henan",0); - Insert  intoCity (CITY_NAME,PARENT_ID)Values("Zhengzhou", A); - Insert  intoCity (CITY_NAME,PARENT_ID)Values("Kaifeng", A); - Insert  intoCity (CITY_NAME,PARENT_ID)Values("Anyang", A);

I used three fields, where parent_id stored the ID of the parent, which is the equivalent of a pointer to the parent. For example, Guizhou, the ID of 1, including Guiyang, Zunyi, Tongren parent_id 1, said the meaning of Guizhou to save the three cities.

Why should we have parent_id to point to the parent node? Let's take a look at the table below.

What can you see in terms of this chart? Can you see that Chengdu is a city under Sichuan? So the parent_id field is essential.

The whole table is a forest data structure, so how can we find out how many cities to save?

Select  from  as  Left Join  as  on p.id =s.parent_id;

The results are as follows:

which

Select P.id,p.city_name,s.city_name indicates that the queried table shows only three fields, p represents the parent table, and S represents the child table. So obviously there is only one table, where does the parent table and the child table come from? This is called the self-connection.

You can think of another table in your mind, which is exactly the same as the city table, and then connect them through the conditions as if they were two tables, when my condition is p.id=s.parent_id;

The left join means that the data of the parent table is all displayed, the data of the child table is empty if it does not exist, as shown above, Zhengzhou, we do not query to the parent table by p.id=s.parent_id the ID of the child table is the same as the parent_id, So the data of the parent table (13, Zhengzhou)

Fully displayed, the data for the child table is displayed as empty.

MySQL Infinite class classification table design and self-connection