有时候要建立联系的两张表很类似时,可以写在一张表中,就以省、市、县为例:
mysql> create table areas(
-> id INT PRIMARY KEY,
-> name VARCHAR(20),
-> pid INT,
-> FOREIGN KEY(pid) references areas(id))character set utf8;
查看一下
desc areas;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| pid | int(11) | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+-------+
添加以下数据:
mysql> select * from areas;
+----+--------+------+
| id | name | pid |
+----+--------+------+
| 1 | 山西 | NULL |
| 2 | 陕西 | NULL |
| 3 | 太原 | 1 |
| 4 | 大同 | 1 |
| 5 | 西安 | 2 |
| 6 | 咸阳 | 2 |
| 7 | 曲阳县 | 3 |
| 8 | 天镇县 | 4 |
+----+--------+------+
这里1,2是省份 ,3,4,5,6是市,最后曲阳在太原、天镇在大同。以此为例:
mysql> select a1.name,a2.name,a3.name from areas as a1
-> join areas as a2 on a1.pid=a2.id
-> join areas as a3 on a2.pid=a3.id;
+--------+------+------+
| name | name | name |
+--------+------+------+
| 曲阳县 | 太原 | 山西 |
| 天镇县 | 大同 | 山西 |
+--------+------+------+
可以在一张表中自关联直接查找,再做稍微修改``
mysql> select a1.name as county,a2.name as city,a3.name as province from areas as a1
-> join areas as a2 on a1.pid=a2.id
-> join areas as a3 on a2.pid=a3.id;
+--------+------+----------+
| county | city | province |
+--------+------+----------+
| 曲阳县 | 太原 | 山西 |
| 天镇县 | 大同 | 山西 |
+--------+------+----------+
把对应的县、市、省 列名修改 就?了