TEXT转INT失败：如何关联披萨食谱与配料表查询配料名称？

表结构与需求 #

现有表结构 #

CREATE TABLE pizza_recipes (
  "pizza_id" INTEGER,
  "toppings" TEXT
);
INSERT INTO pizza_recipes
  ("pizza_id", "toppings")
VALUES
  (1, '1, 2, 3, 4, 5, 6, 8, 10'),
  (2, '4, 6, 7, 9, 11, 12');

CREATE TABLE pizza_toppings (
  "topping_id" INTEGER,
  "topping_name" TEXT
);
INSERT INTO pizza_toppings
  ("topping_id", "topping_name")
VALUES
  (1, 'Bacon'),
  (2, 'BBQ Sauce'),
  (3, 'Beef'),
  (4, 'Cheese'),
  (5, 'Chicken'),
  (6, 'Mushrooms'),
  (7, 'Onions'),
  (8, 'Pepperoni'),
  (9, 'Peppers'),
  (10, 'Salami'),
  (11, 'Tomatoes'),
  (12, 'Tomato Sauce')

查询需求 #

获取pizza_id为1和2对应的所有配料名称。

错误原因分析 #

你之前尝试的CAST/CONVERT语句全部报错，核心原因是：
pizza_recipes.toppings字段是包含逗号和空格的多ID字符串（比如'1, 2, 3, 4, 5, 6, 8, 10'），直接对整个字符串执行整数转换，会因为格式不合法（存在非数字字符）而失败。

正确解决方案 #

核心思路是：先将逗号分隔的toppings字符串拆分成独立的单个topping_id行，再与pizza_toppings表关联获取配料名称。以下是主流数据库的实现方式：

1. SQL Server 2016及以上版本 #

使用内置STRING_SPLIT函数拆分字符串，结合TRIM去除ID前后的空格：

SELECT 
    pr.pizza_id,
    pt.topping_name
FROM pizza_recipes pr
CROSS APPLY STRING_SPLIT(pr.toppings, ',') AS split_toppings
JOIN pizza_toppings pt 
    ON TRIM(split_toppings.value) = CAST(pt.topping_id AS VARCHAR)
WHERE pr.pizza_id IN (1, 2)
ORDER BY pr.pizza_id, pt.topping_id;

2. MySQL 8.0及以上版本 #

使用递归CTE拆分逗号分隔字符串：

WITH RECURSIVE split_toppings AS (
    SELECT 
        pizza_id,
        toppings,
        1 AS pos,
        SUBSTRING_INDEX(toppings, ',', 1) AS topping_id_str
    FROM pizza_recipes
    WHERE pizza_id IN (1, 2)
    UNION ALL
    SELECT 
        pizza_id,
        toppings,
        pos + 1,
        SUBSTRING_INDEX(SUBSTRING_INDEX(toppings, ',', pos + 1), ',', -1) AS topping_id_str
    FROM split_toppings
    WHERE pos <= LENGTH(toppings) - LENGTH(REPLACE(toppings, ',', ''))
)
SELECT 
    st.pizza_id,
    pt.topping_name
FROM split_toppings st
JOIN pizza_toppings pt 
    ON TRIM(st.topping_id_str) = pt.topping_id
ORDER BY st.pizza_id, pt.topping_id;

3. PostgreSQL #

使用string_to_array和unnest函数拆分字符串：

SELECT 
    pr.pizza_id,
    pt.topping_name
FROM pizza_recipes pr
CROSS JOIN UNNEST(string_to_array(pr.toppings, ',')) AS split_toppings(topping_id_str)
JOIN pizza_toppings pt 
    ON TRIM(split_toppings.topping_id_str)::INT = pt.topping_id
WHERE pr.pizza_id IN (1, 2)
ORDER BY pr.pizza_id, pt.topping_id;

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