MySQL查询结果处理：将分隔字符串转为多列的技术求助

Hey Jeff, let's work through this MySQL indexing problem you're tackling. You mentioned using SUBSTRING_INDEX() works but requires manual repetition—here are cleaner, scalable solutions to handle your delimited items field and map values to your predefined variables (tree=1234, car=4567, house=7890).

First, let's recap your sample data for clarity:

Solution 1: Split Delimited Values to Rows + Map to Variable Names #

If you want to break down the items field into individual rows and automatically map each value to its corresponding variable name, use a recursive CTE + mapping table (no manual SUBSTRING_INDEX() repetition):

-- Define your value-to-variable mapping (can be a permanent table too)
WITH value_mapping AS (
    SELECT '1234' AS item_value, 'tree' AS variable_name UNION ALL
    SELECT '4567', 'car' UNION ALL
    SELECT '7890', 'house'
),
-- Split the delimited items into separate rows
split_items AS (
    SELECT 
        ID,
        pagetitle,
        SUBSTRING_INDEX(SUBSTRING_INDEX(items, '||', numbers.n), '||', -1) AS item_value
    FROM your_table
    -- Join with a number set (adjust the count to match max delimited values in items)
    JOIN (
        SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3
    ) numbers ON CHAR_LENGTH(items) - CHAR_LENGTH(REPLACE(items, '||', '')) >= n - 1
)
-- Combine split values with their variable names
SELECT 
    si.ID,
    si.pagetitle,
    vm.variable_name,
    si.item_value
FROM split_items si
JOIN value_mapping vm ON si.item_value = vm.item_value;

Result: #

Solution 2: Split to Columns (Fixed Number of Values) #

If your items field always has exactly 2 delimited values, simplify the SUBSTRING_INDEX() usage and map values directly to columns:

SELECT 
    ID,
    pagetitle,
    -- Extract values
    SUBSTRING_INDEX(items, '||', 1) AS item1,
    SUBSTRING_INDEX(items, '||', -1) AS item2,
    -- Map to variable names
    CASE SUBSTRING_INDEX(items, '||', 1)
        WHEN '1234' THEN 'tree'
        WHEN '4567' THEN 'car'
        WHEN '7890' THEN 'house'
    END AS item1_variable,
    CASE SUBSTRING_INDEX(items, '||', -1)
        WHEN '1234' THEN 'tree'
        WHEN '4567' THEN 'car'
        WHEN '7890' THEN 'house'
    END AS item2_variable
FROM your_table;

Result: #

Solution 3: Pivot to Variable Columns #

If you want to display each variable as a column (showing the value if present, NULL otherwise), use conditional aggregation:

WITH value_mapping AS (
    SELECT '1234' AS item_value, 'tree' AS variable_name UNION ALL
    SELECT '4567', 'car' UNION ALL
    SELECT '7890', 'house'
),
split_items AS (
    SELECT 
        ID,
        pagetitle,
        SUBSTRING_INDEX(SUBSTRING_INDEX(items, '||', numbers.n), '||', -1) AS item_value
    FROM your_table
    JOIN (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3) numbers 
        ON CHAR_LENGTH(items) - CHAR_LENGTH(REPLACE(items, '||', '')) >= n - 1
)
SELECT 
    si.ID,
    si.pagetitle,
    MAX(CASE WHEN vm.variable_name = 'tree' THEN si.item_value END) AS tree,
    MAX(CASE WHEN vm.variable_name = 'car' THEN si.item_value END) AS car,
    MAX(CASE WHEN vm.variable_name = 'house' THEN si.item_value END) AS house
FROM split_items si
JOIN value_mapping vm ON si.item_value = vm.item_value
GROUP BY si.ID, si.pagetitle;

Result: #

Key Takeaways #

• Use the CTE method for variable numbers of delimited values (no manual code changes if your items field grows).
• Use the simplified SUBSTRING_INDEX() + CASE for fixed-value counts (faster, more readable).
• The mapping table approach is more maintainable than hardcoding CASE statements—update the mapping instead of rewriting queries when variables change.

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