拆分列生成二进制矩阵：MySQL中substring方法使用问题求助

Hey there! I get it—trying to use substring methods from that "split one column into multiple" answer didn’t pan out for your binary matrix goal, and that makes sense. Substring works great for splitting text into fixed parts, but binary matrices are all about aggregating categorical data into 0/1 flags. Let’s break this down with practical, actionable examples.

First, Let’s Define the Scenario #

Let’s assume your raw data looks like this (a super common setup for this use case):

• Table name: user_preferences
• Columns: user_id (unique identifier) and category (the single column you want to convert)

Sample data:

Your goal is a binary matrix where each category becomes a column, with values 1 if the user has that category, 0 otherwise.

Method 1: Static SQL (For Fixed Categories) #

If you know all possible categories upfront, use conditional aggregation—this is the most straightforward approach. We’ll use CASE statements to flag each category, then MAX() to collapse rows per user:

SELECT
  user_id,
  MAX(CASE WHEN category = 'books' THEN 1 ELSE 0 END) AS books,
  MAX(CASE WHEN category = 'movies' THEN 1 ELSE 0 END) AS movies,
  MAX(CASE WHEN category = 'music' THEN 1 ELSE 0 END) AS music
FROM user_preferences
GROUP BY user_id;

How This Works: #

• GROUP BY user_id groups all rows for a single user together
• Each CASE statement checks if the category matches, returning 1 if true, 0 otherwise
• MAX() ensures we keep the 1 (if the user has that category at least once) instead of 0

The output will be your desired binary matrix:

Method 2: Dynamic SQL (For Changing/Unknown Categories) #

If your categories might grow over time and you don’t want to update the SQL manually, use dynamic SQL to auto-generate the category columns:

-- Step 1: Build the CASE statement string for all unique categories
SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN category = ''',
      category,
      ''' THEN 1 ELSE 0 END) AS `',
      category,
      '`'
    )
  ) INTO @sql
FROM user_preferences;

-- Step 2: Assemble the full query
SET @sql = CONCAT('SELECT user_id, ', @sql, ' FROM user_preferences GROUP BY user_id');

-- Step 3: Execute the dynamic query
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

How This Works: #

• GROUP_CONCAT stitches together all the CASE blocks for every unique category in your column
• The final query is built dynamically, so it will automatically include new categories as they’re added

What If Your Column Has Comma-Separated Values? #

If your single column has multiple values per row (e.g., user_id=1, category="books,movies"), first split the values into separate rows using a recursive CTE, then apply the aggregation above:

-- Split comma-separated categories into rows
WITH RECURSIVE split_categories AS (
  SELECT
    user_id,
    SUBSTRING_INDEX(category, ',', 1) AS single_category,
    SUBSTRING(category, LOCATE(',', category) + 1) AS remaining_categories
  FROM user_preferences_multi
  WHERE category IS NOT NULL AND category != ''
  UNION ALL
  SELECT
    user_id,
    SUBSTRING_INDEX(remaining_categories, ',', 1) AS single_category,
    SUBSTRING(remaining_categories, LOCATE(',', remaining_categories) + 1) AS remaining_categories
  FROM split_categories
  WHERE remaining_categories IS NOT NULL AND remaining_categories != ''
)
-- Now build the binary matrix from the split rows
SELECT
  user_id,
  MAX(CASE WHEN single_category = 'books' THEN 1 ELSE 0 END) AS books,
  MAX(CASE WHEN single_category = 'movies' THEN 1 ELSE 0 END) AS movies,
  MAX(CASE WHEN single_category = 'music' THEN 1 ELSE 0 END) AS music
FROM split_categories
GROUP BY user_id;

The key takeaway here is that substring methods are for splitting text into columns, but binary matrices require aggregating categorical data—conditional aggregation is the right tool for the job.

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