一种更为高效的数据库无限级分类表设计

一般地，大家在数据库设计无限级分类表时只是多添加了一个parent_id字段，这样如果要读取所有分类的话只能进行递归读取数据库表操作，这样的性能可想而知。
先看个表及测试数据：

id	name	parent_id
1	Clothing（衣服）	0
2	Men’s（男装）	1
3	Women’s（女装）	1
4	Suits（套装）	2
5	Dresses（连衣裙）	3
6	Skirts（裙子）	3
7	Blouses（ 女衬衫）	3
8	Slacks（ 长裤）	4
9	Jackets（夹克）	4
10	Evening Gowns（ 晚礼服）	5
11	Sun Dresses（ 太阳裙）	6

为了更高效地查询和使用无限级分类表，我们增加了如下几个字段：

1. id_path：从根节点到本节点的所有id，如上表所示id为1的id_path为“.1.”，id为5的id_path为“.1.3.5.”，所有id用英文句号“.”包围和隔开。
2. parent_id_path：参考id_path，不包括当前id。
3. name_path：对应id_path的分类名称路径，用“/”包围和隔开，如id为1的name_path为“/衣服/”，id为5的name_path为“/衣服/女装/连衣裙/”。
4. order_weight：分类排序权重。
5. alias：分类别名，如果有多重分类放在同一个表，可以使用alias区分，如衣服的分类和区划的分类放在同一个表，衣服的alias为“clothing”，区划的分类为“area”。

id	name	parent_id	id_path	parent_id_path	name_path	order_weight	alias
1	Clothing（衣服）	0	.1.		/Clothing（衣服）/	0	clothing
2	Men’s（男装）	1	.1.2.	.1.	/Clothing（衣服）/Men’s（男装）/	0	clothing
3	Women’s（女装）	1	.1.3.	.1.	/Clothing（衣服）/Women’s（女装）/	0	clothing
4	Suits（套装）	2	.1.2.4.	.1.2.	/Clothing（衣服）/Men’s（男装）/ Suits（套装）/	0	clothing
5	Dresses（连衣裙）	3	.1.3.5.	.1.3.	/Clothing（衣服）/Women’s（女装）/Dresses（连衣裙）/	0	clothing
6	Skirts（裙子）	3	.1.3.6.	.1.3.	/Clothing（衣服）/Women’s（女装）/Skirts（裙子）/	0	clothing
7	Blouses（ 女衬衫）	3	.1.3.7.	.1.3.	/Clothing（衣服）/Women’s（女装）/Blouses（ 女衬衫）/	0	clothing
8	Slacks（ 长裤）	4	.1.2.4.8.	.1.2.4.	/Clothing（衣服）/Men’s（男装）/Suits（套装）/ Slacks（ 长裤）/	0	clothing
9	Jackets（夹克）	4	.1.2.4.9.	.1.2.4.	/Clothing（衣服）/Men’s（男装）/Suits（套装）/Jackets（夹克）/	0	clothing
10	Evening Gowns（ 晚礼服）	5	.1.3.5.10.	.1.3.5.	/Clothing（衣服）/Women’s（女装）/ Dresses（连衣裙）/Evening Gowns（ 晚礼服）/	0	clothing
11	Sun Dresses（ 太阳裙）	6	.1.3.6.11.	.1.3.6.	/Clothing（衣服）/Women’s（女装）/Skirts（裙子）/Sun Dresses（ 太阳裙）/	0	clothing
12	全国	0	.12.		/全国/	0	area
13	北京	12	.12.13.	.12.	/全国/北京/	0	area

这样设计后，

1. 如果要获取某个分类下的所有分类，只需使用查询条件“id_path LIKE ‘{当前分类id_path}%’”即可查询到结果，如要查询所有女装，可以使用查询条件“id_path LIKE ‘.1.3.%’”得到所有女装的分类。
2. 如果要严格按id呈树形层级排序，只需使用“ORDER BY id_path ASC”，得到的查询结果可直接显示到选择列表；或者通过程序按id_path组织成需要的数据格式（用循环，不用递归）。
3. 如果要严格呈树形层级排序，且各级按排序权重order_weight排序，则使用“ORDER BY parent_id_path ASC, order_weight DESC”，然后按id_path组织成需要的数据格式（用循环，不用递归）。
4. 按多个分类id使用“WHERE IN(id1, id2, …)”查询所有当前分类及子分类仍然可以通过程序重新组织成所需要的数据格式，参考2、3。
5. 如果要获取分类的面包屑导航，只要读取id_path和name_path进行处理即可。
6. 如果要编辑移动分类，比如把id为5的“连衣裙”移动到id为6的“裙子”下面，需要把属于“连衣裙”的分类及子分类的id_path的“.1.3.”前缀替换成“.1.3.6.”，把属于“连衣裙”的分类及子分类的name_path的“/Clothing（衣服）/Women’s（女装）/”前缀替换成“/Clothing（衣服）/Women’s（女装）/Skirts（裙子）/”。sql操作为：

SET
parent_id=6,
id_path=REPLACE(id_path, ‘.1.3.’, ‘.1.3.6.’),
parent_id_path=REPLACE(parent_id_path, ‘.1.3.’, ‘.1.3.6.’),
name_path=REPLACE(name_path, ‘/Clothing（衣服）/Women’s（女装）/’, ‘/Clothing（衣服）/Women’s（女装）/Skirts（裙子）/’)

7. 查询所有衣服只要使用查询条件“alias=‘clothing’”。
8. id、name、parent_id、id_path、parent_id_path都可以设置索引。