MySQL查询一个分类下的所有子分类和对应的商品

在一些电商或资源导航站点中，我们经常需要查询某个分类下的所有子分类和对应的商品。本文将介绍如何使用MySQL语句来实现这一功能。文章分为以下几个部分，分别讲解查询语句的构建、代码实现、基于ORM框架的查询和性能优化等。

阅读更多：MySQL 教程

基本查询语句

下面是最基本的查询语句，以分类表（category）和商品表（item）为例：

-- 查询分类ID为1的所有子分类和对应的商品
SELECT 
    t1.id AS category_id, 
    t1.name AS category_name, 
    t2.id AS item_id, 
    t2.name AS item_name
FROM 
    category t1 
    LEFT JOIN item t2 ON t1.id = t2.category_id
WHERE 
    t1.parent_id = 1 OR t1.id = 1
ORDER BY 
    t1.id, t2.id;

这个查询语句使用了左连接（LEFT JOIN），将分类表和商品表按照分类ID（id）连接在一起，如果一个分类没有对应的商品，则商品信息显示为NULL。在WHERE子句中使用逻辑运算符OR，同时考虑了两种可能的情况：要么当前分类ID等于给定的分类ID（id = 1），要么当前分类的父分类ID等于给定的分类ID。最后通过ORDER BY子句按照分类ID和商品ID排序。

我们可以通过运行以下SQL语句来创建测试数据：

-- 创建分类表
CREATE TABLE category (
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    parent_id INT DEFAULT NULL
);

-- 创建商品表
CREATE TABLE item (
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    category_id INT NOT NULL
);

-- 添加测试数据
INSERT INTO category (id, name, parent_id) VALUES (1, '手机数码', NULL);
INSERT INTO category (id, name, parent_id) VALUES (2, '手机', 1);
INSERT INTO category (id, name, parent_id) VALUES (3, '数码相机', 1);
INSERT INTO category (id, name, parent_id) VALUES (4, '小米手机', 2);
INSERT INTO category (id, name, parent_id) VALUES (5, '三星手机', 2);
INSERT INTO category (id, name, parent_id) VALUES (6, '佳能相机', 3);
INSERT INTO item (id, name, category_id) VALUES (1, '小米11', 4);
INSERT INTO item (id, name, category_id) VALUES (2, '小米10', 4);
INSERT INTO item (id, name, category_id) VALUES (3, '三星S21', 5);
INSERT INTO item (id, name, category_id) VALUES (4, '佳能EOS R5', 6);

运行查询语句后，我们得到以下结果：

+-------------+---------------+--------+--------------+
| category_id | category_name | item_id |  item_name   |
+-------------+---------------+--------+--------------+
|           1 | 手机数码          |   NULL | NULL         |
|           2 | 手机             |      1 | 小米11          |
|           2 | 手机             |      2 | 小米10          |
|           4 | 小米手机         |      1 | 小米11          |
|           4 | 小米手机         |      2 | 小米10          |
|           5 | 三星手机         |      3 | 三星S21         |
|           6 | 佳能相机         |      4 | 佳能EOS R5     |
|           3 | 数码相机         |      4 | 佳能EOS R5     |
+-------------+---------------+--------+--------------+

代码实现

在代码实现过程中，我们可以使用ORM框架，如Django和Ruby on Rails，使我们的工作更加高效。此外，我们还需考虑如何优化ORM查询的性能。

基于Django的查询

在Django中，我们可以使用ORM语句来完成查询。首先，在models.py文件中定义相应的模型：

from django.db import models

class Category(models.Model):
    name = models.CharField(max_length=50)
    parent = models.ForeignKey('self', on_delete=models.CASCADE, null=True, blank=True)

class Item(models.Model):
    name = models.CharField(max_length=50)
    category = models.ForeignKey(Category, on_delete=models.CASCADE)

接下来，我们可以使用以下查询语句：

def get_category_items(category_id):
    category = Category.objects.get(id=category_id)
    descendant_categories = Category.objects.filter(id__in=category.get_descendants(include_self=True))

    items = Item.objects.filter(category__in=descendant_categories)

    result = {}
    for c in descendant_categories:
        result[c.id] = {'name': c.name, 'items': []}

    for i in items:
        result[i.category.id]['items'].append({'id': i.id, 'name': i.name})

    return result

这个查询语句首先用get方法获取指定ID的分类。然后使用django-mptt中的get_descendants方法找到该分类的所有后代分类（包括自身）。接下来，我们使用filter方法找到所有属于这些后代分类的商品。最后，我们使用字典数据结构来按照分类分组并存储商品信息。

基于Ruby on Rails的查询

在Ruby on Rails中，我们可以使用Active Record语法来实现查询。首先，在models文件夹中创建category.rb和item.rb文件：

class Category < ApplicationRecord
    has_many :items
    belongs_to :parent, class_name: 'Category', optional: true
    has_many :children, class_name: 'Category', foreign_key: 'parent_id'

    def descendants
        children.map {|c| c.descendants.to_a}.flatten << self
    end
end

class Item < ApplicationRecord
  belongs_to :category
end

接下来，在控制器中用以下代码来完成查询：

def get_category_items
    category = Category.find(params[:category_id])
    categories = category.descendants

    items = Item.includes(:category).where(categories: {id: categories.map(&:id)})

    result = Hash.new {|h, k| h[k] = {name: k.name, items: []}}
    categories.each {|c| result[c]}

    items.each {|i| result[i.category][:items] << {id: i.id, name: i.name}}

    render json: result
end

这个查询语句也是先找到指定ID的分类，然后利用Category模型中的descendants方法找到该分类的所有后代分类（不包括自身）。接下来，我们使用includes方法预加载商品对应的分类信息，并使用where方法从中筛选出符合条件的商品。最后，我们使用哈希表来按照分类分组并存储商品信息。

性能优化

随着数据量的增加，我们需要考虑如何优化查询的性能。以下是一些常用的方法：

索引

在MySQL中，使用索引可以极大地提高查询速度。我们可以对分类表中的id和parent_id列以及商品表中的category_id列进行索引。

ALTER TABLE category ADD INDEX id_index (id);
ALTER TABLE category ADD INDEX parent_id_index (parent_id);
ALTER TABLE item ADD INDEX category_id_index (category_id);