ClickHouse复制海量数据并修改指定列的高效实现方案

当然有更高效的方案，不用反复执行几十上百次INSERT语句，核心思路是生成1到100的序列值，和原表做交叉连接（CROSS JOIN），让原表的每一行都和100个不同的test_id配对，一次插入就能生成100倍数据。

下面针对主流数据库给出具体实现：

PostgreSQL #

利用generate_series函数直接生成1-100的序列：

INSERT INTO 100x_table (test_id, col1, col2, col3, ...)
SELECT gs.test_id, t.col1, t.col2, t.col3, ...
FROM original_table t
CROSS JOIN generate_series(1, 100) AS gs(test_id);

MySQL（8.0+） #

用递归CTE生成序列：

WITH RECURSIVE seq AS (
    SELECT 1 AS test_id
    UNION ALL
    SELECT test_id + 1 FROM seq WHERE test_id < 100
)
INSERT INTO 100x_table (test_id, col1, col2, col3, ...)
SELECT s.test_id, t.col1, t.col2, t.col3, ...
FROM original_table t
CROSS JOIN seq s;

如果是MySQL 5.x版本（不支持CTE），可以用UNION手动生成1-100的序列（比如SELECT 1 UNION SELECT 2 ... UNION SELECT 100），再和原表交叉连接。

SQL Server #

2022+版本 #

直接用GENERATE_SERIES函数：

INSERT INTO 100x_table (test_id, col1, col2, col3, ...)
SELECT gs.test_id, t.col1, t.col2, t.col3, ...
FROM original_table t
CROSS JOIN GENERATE_SERIES(1, 100) AS gs(test_id);

旧版本（2019及以前） #

用递归CTE生成序列，注意开启递归上限：

WITH seq AS (
    SELECT 1 AS test_id
    UNION ALL
    SELECT test_id + 1 FROM seq WHERE test_id < 100
)
INSERT INTO 100x_table (test_id, col1, col2, col3, ...)
SELECT s.test_id, t.col1, t.col2, t.col3, ...
FROM original_table t
CROSS JOIN seq s
OPTION (MAXRECURSION 0);

关键注意事项 #

• 明确指定列名：不要用SELECT *，必须手动列出除test_id外的所有列，避免原表的test_id列和生成的序列值冲突，同时保证插入顺序和目标表结构匹配。
• 性能考量：如果原表数据量极大，100倍后的数据可能超出数据库单批次插入的承受能力，可以把序列拆分成多个区间（比如分10次，每次生成10个序列值），但依然比单次插1倍高效很多。
• 约束检查：如果目标表的test_id列有自增、唯一约束等，需要提前调整（比如关闭自增，或者确认序列值符合约束要求）。

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