如何使用MySQL将有序列表格式的Instructions字段拆分为多行？

Absolutely! You can absolutely split that Instructions field into a separate table using MySQL, using the "number. " pattern as your delimiter. Here's a step-by-step solution tailored to your scenario:

1. First, Create Your Target Table #

Start by setting up the table that will hold individual instructions, linked back to your original records:

CREATE TABLE recipe_instructions (
    recipe_id INT NOT NULL, -- Matches the ID column in your original table
    step_number INT NOT NULL,
    instruction TEXT NOT NULL,
    PRIMARY KEY (recipe_id, step_number),
    FOREIGN KEY (recipe_id) REFERENCES your_original_table(id) -- Replace with your actual table name
);

2. Use a Recursive CTE to Generate a Number Sequence #

MySQL 8.0+ supports recursive Common Table Expressions (CTEs), which we'll use to generate a list of numbers (one for each possible step). This helps us extract each step one by one.

3. Split and Insert the Instructions #

Combine the number sequence with your original table to split each Instructions field and insert the results into the new table:

WITH RECURSIVE step_numbers(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM step_numbers WHERE n < 100 -- Adjust this number to cover your maximum possible steps
)
INSERT INTO recipe_instructions (recipe_id, step_number, instruction)
SELECT
    original.id AS recipe_id,
    sn.n AS step_number,
    -- Extract the text for each step, trimming extra whitespace
    TRIM(REGEXP_SUBSTR(original.Instructions, CONCAT(sn.n, '\\.\\s*(.*?)(?=(\\s+[0-9]+\\.|$))'))) AS instruction
FROM
    your_original_table original -- Replace with your actual table name
JOIN
    step_numbers sn ON REGEXP_SUBSTR(original.Instructions, CONCAT(sn.n, '\\.')) IS NOT NULL
WHERE
    original.Instructions IS NOT NULL AND original.Instructions != '';

How This Works: #

• The recursive CTE step_numbers generates numbers from 1 to 100 (tweak the upper limit if you have recipes with more steps).
• REGEXP_SUBSTR uses a regular expression to match each step starting with [number]. , then captures all text until it hits the next [number]. or the end of the string. This avoids splitting on numbers inside step text (like the 5mm in your example).
• We join the number sequence to your original table only if a step with that number exists in the Instructions field.
• TRIM cleans up any leading/trailing whitespace from the extracted step text.

Notes for Older MySQL Versions: #

If you're using MySQL < 8.0 (which lacks CTEs and REGEXP_SUBSTR), you'll need to:

• Create a physical number table (with rows for 1,2,3... up to your max step count) instead of using a CTE.
• Use a combination of SUBSTRING_INDEX and string functions to extract each step. It's clunkier, but doable—let me know if you need that version!

Pro Tip: #

Always run the SELECT portion of the query first (without the INSERT) to verify the extracted steps look correct before writing to your new table. That way you can catch any edge cases in your data (like steps with unusual formatting) before committing changes.

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