SQL多列插入：基于网页管道分隔参数生成全组合插入方案

Alright, let's walk through how to solve this problem—taking pipe-separated parameters from a web page, generating every possible combination of them, and inserting those combinations into your SQL table's columns. I'll cover common SQL dialects and edge cases you might run into:

# Step 1: Split Pipe-Separated Parameters into Individual Rows #

First, you need to break down each pipe-delimited string into separate rows. Different SQL databases have built-in tools for this; here's how to use them:

MySQL (8.0+) #

Use the STRING_SPLIT function (available in MySQL 8.0 and later). If you're on an older version, skip to the recursive CTE example further down:

-- Split parameter A into rows
SELECT TRIM(value) AS param_a FROM STRING_SPLIT('val1|val2|val3', '|');
-- Split parameter B into rows
SELECT TRIM(value) AS param_b FROM STRING_SPLIT('x|y', '|');
-- Split parameter C into rows (even single-value strings work here)
SELECT TRIM(value) AS param_c FROM STRING_SPLIT('foo', '|');

PostgreSQL #

Combine string_to_array with unnest to turn the split array into rows:

-- Split parameter A into rows
SELECT TRIM(unnest(string_to_array('val1|val2|val3', '|'))) AS param_a;
-- Split parameter B into rows
SELECT TRIM(unnest(string_to_array('x|y', '|'))) AS param_b;
-- Split parameter C into rows
SELECT TRIM(unnest(string_to_array('foo', '|'))) AS param_c;

SQL Server (2016+) #

Use STRING_SPLIT (similar to MySQL's implementation):

-- Split parameter A into rows
SELECT TRIM(value) AS param_a FROM STRING_SPLIT('val1|val2|val3', '|');
-- Split parameter B into rows
SELECT TRIM(value) AS param_b FROM STRING_SPLIT('x|y', '|');
-- Split parameter C into rows
SELECT TRIM(value) AS param_c FROM STRING_SPLIT('foo', '|');

# Step 2: Generate All Possible Combinations & Insert #

Once each parameter is split into rows, use a cross join to create every possible combination of the values, then insert them directly into your table.

Example for MySQL #

INSERT INTO your_target_table (column_a, column_b, column_c)
SELECT 
    a.param_a,
    b.param_b,
    c.param_c
FROM 
    (SELECT TRIM(value) AS param_a FROM STRING_SPLIT('val1|val2|val3', '|')) a
CROSS JOIN 
    (SELECT TRIM(value) AS param_b FROM STRING_SPLIT('x|y', '|')) b
CROSS JOIN 
    (SELECT TRIM(value) AS param_c FROM STRING_SPLIT('foo', '|')) c;

Example for PostgreSQL #

INSERT INTO your_target_table (column_a, column_b, column_c)
SELECT 
    a.param_a,
    b.param_b,
    c.param_c
FROM 
    (SELECT TRIM(unnest(string_to_array('val1|val2|val3', '|'))) AS param_a) a
CROSS JOIN 
    (SELECT TRIM(unnest(string_to_array('x|y', '|'))) AS param_b) b
CROSS JOIN 
    (SELECT TRIM(unnest(string_to_array('foo', '|'))) AS param_c) c;

Example for SQL Server #

INSERT INTO your_target_table (column_a, column_b, column_c)
SELECT 
    a.param_a,
    b.param_b,
    c.param_c
FROM 
    (SELECT TRIM(value) AS param_a FROM STRING_SPLIT('val1|val2|val3', '|')) a
CROSS JOIN 
    (SELECT TRIM(value) AS param_b FROM STRING_SPLIT('x|y', '|')) b
CROSS JOIN 
    (SELECT TRIM(value) AS param_c FROM STRING_SPLIT('foo', '|')) c;

# Pro Tips & Edge Cases to Handle #

• Trim Whitespace: I added TRIM() to clean up accidental spaces around values (like val1 | val2). If your parameters are guaranteed to be space-free, you can skip this.
• Empty Values: If a parameter string has empty entries (like val1||val3), add a WHERE clause to exclude blank rows:

  -- Example in MySQL
  SELECT TRIM(value) AS param_a FROM STRING_SPLIT('val1||val3', '|') WHERE TRIM(value) != '';
  

• Older MySQL Versions (Pre-8.0): If you don't have STRING_SPLIT, use a recursive CTE to split strings without creating a custom function:

  WITH RECURSIVE split_a AS (
      SELECT 
          1 AS pos,
          SUBSTRING_INDEX('val1|val2|val3', '|', 1) AS param_a,
          SUBSTRING('val1|val2|val3', LENGTH(SUBSTRING_INDEX('val1|val2|val3', '|', 1)) + 2) AS remaining
      UNION ALL
      SELECT 
          pos + 1,
          SUBSTRING_INDEX(remaining, '|', 1),
          SUBSTRING(remaining, LENGTH(SUBSTRING_INDEX(remaining, '|', 1)) + 2)
      FROM split_a
      WHERE remaining != ''
  )
  SELECT TRIM(param_a) AS param_a FROM split_a;
  

  Cross join these recursive CTEs for each parameter to generate combinations, just like the earlier examples.

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