如何在MySQL中将产品的递归层级类别列表拼接为字符串？

Since you've opted to ditch pure SQL and go with a script that calls MySQL instead, this approach will let you easily fetch that full category hierarchy (including the product's direct category and all its parent categories) and format it into the comma-separated list you need.

Core Approach #

1. Connect to MySQL: Use your preferred language's MySQL client library to establish a connection.
2. Fetch Products & Direct Categories: Join products with category_products to get each product's immediate category ID.
3. Recursively Fetch Parent Categories: For each category ID, keep querying category_relationships and categories to pull all parent categories until there are no more parents left.
4. Format the Result: Collect all category names (starting from the product's direct category up to the top-level parents) and join them into a single string.

Python Example with mysql-connector #

First, make sure you have the library installed:

pip install mysql-connector-python

Then here's the script:

import mysql.connector
from mysql.connector import Error

def get_parent_categories(category_id, conn):
    """Recursively fetch all parent category names for a given category ID"""
    category_names = []
    cursor = conn.cursor()
    
    # Get current category name first
    cursor.execute("SELECT name FROM categories WHERE id = %s", (category_id,))
    current_cat = cursor.fetchone()
    if current_cat:
        category_names.append(current_cat[0])
    
    # Recursively get parent categories
    while True:
        cursor.execute("""
            SELECT cr.parent_category_id, c.name 
            FROM category_relationships cr
            JOIN categories c ON cr.parent_category_id = c.id
            WHERE cr.child_category_id = %s
        """, (category_id,))
        parent = cursor.fetchone()
        if not parent:
            break
        category_names.append(parent[1])
        category_id = parent[0]
    
    cursor.close()
    return category_names

def main():
    try:
        # Connect to your MySQL database
        conn = mysql.connector.connect(
            host='your_host',
            database='your_db',
            user='your_user',
            password='your_password'
        )
        
        if conn.is_connected():
            cursor = conn.cursor()
            
            # Fetch all products with their direct category ID
            cursor.execute("""
                SELECT p.id, p.name, cp.category_id
                FROM products p
                JOIN category_products cp ON p.id = cp.product_id
            """)
            products = cursor.fetchall()
            
            # Process each product to build the category list
            result = []
            for product in products:
                product_id, product_name, cat_id = product
                category_list = get_parent_categories(cat_id, conn)
                # Join into comma-separated string
                category_str = ", ".join(category_list)
                result.append((product_id, product_name, category_str))
            
            # Print or export the result
            print("| product.id | product.name | category_list |")
            print("*------------*--------------*------------------------------------*")
            for row in result:
                print(f"| {row[0]} | {row[1]} | {row[2]} |")
            
            cursor.close()
    
    except Error as e:
        print(f"Database error: {e}")
    finally:
        if conn.is_connected():
            conn.close()

if __name__ == "__main__":
    main()

Notes #

• Replace your_host, your_db, your_user, and your_password with your actual MySQL credentials.
• The recursive function get_parent_categories starts with the product's direct category, then keeps pulling parents until there are no more entries in category_relationships.
• If you need the category order reversed (e.g., top-level parent first, then child, then product's category), just reverse the category_list before joining: category_str = ", ".join(reversed(category_list)).

内容的提问来源于stack exchange，提问作者bentedder