MySQL如何对两个数据集的元素交叉组合执行批量插入操作

实现方法 #

可以实现，直接用MySQL的CROSS JOIN（笛卡尔积）即可生成两个数据集的所有交叉组合，一次性完成批量插入，以下是具体方案：

方案1：数据集为临时值，未存储在表中

假设你要插入的目标表为target_table，对应存储DataSet A值的字段为col_a，存储DataSet B值的字段为col_b，语句如下：

INSERT INTO target_table (col_a, col_b)
SELECT a.val, b.val
FROM (
    SELECT 1 AS val UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4 UNION ALL
    SELECT 5 UNION ALL
    SELECT 6 UNION ALL
    SELECT 7
) a
CROSS JOIN (
    SELECT 13 AS val UNION ALL
    SELECT 15 UNION ALL
    SELECT 16 UNION ALL
    SELECT 17
) b;

执行后会一次性插入7*4=28条所有组合的数据。

如果你的MySQL版本在8.0.19及以上，可以用更简洁的行构造器语法，省略大量UNION ALL：

INSERT INTO target_table (col_a, col_b)
SELECT a.column_0, b.column_0
FROM (VALUES ROW(1), ROW(2), ROW(3), ROW(4), ROW(5), ROW(6), ROW(7)) a
CROSS JOIN (VALUES ROW(13), ROW(15), ROW(16), ROW(17)) b;

方案2：两个数据集已存储在现有表中

假设DataSet A的值存在表dataset_a的val字段，DataSet B的值存在表dataset_b的val字段，语句可以简化为：

INSERT INTO target_table (col_a, col_b)
SELECT a.val, b.val FROM dataset_a CROSS JOIN dataset_b;

注意事项 #

如果需要避免重复插入，可以配合INSERT IGNORE或者ON DUPLICATE KEY UPDATE使用，前提是目标表已经在col_a和col_b上建立了唯一联合索引。

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