如何用SQL生成任意数值列表并批量插入测试数据？

针对你提供的student表，要批量插入name='hoge'、age为自定义数值列表的数据，无需手动编写多条VALUES语句，以下是主流数据库的实现方案，灵活度类似Python的列表推导式：

MySQL 实现 #

方法1：直接生成自定义数值列表（适合任意无规律值） #

通过UNION ALL构造包含目标age值的临时数据集，再用INSERT...SELECT批量插入：

INSERT INTO student (name, age)
SELECT 'hoge' AS name, age
FROM (
    SELECT 10 AS age UNION ALL
    SELECT 20 UNION ALL
    SELECT 30 UNION ALL
    SELECT 100 UNION ALL
    SELECT 200
) AS age_list;

修改age值只需调整子查询中的SELECT语句，新增或删除数值都很方便。

方法2：递归CTE生成混合规律+特殊值（MySQL 8.0+支持） #

如果既有规律序列（如10、20...100）又有特殊值（如200），用递归CTE更高效：

INSERT INTO student (name, age)
WITH RECURSIVE age_seq AS (
    SELECT 10 AS age  -- 起始值
    UNION ALL
    SELECT age + 10 FROM age_seq WHERE age < 100  -- 规律递增，终止条件
    UNION ALL
    SELECT 200  -- 额外添加的非规律值
)
SELECT 'hoge' AS name, age FROM age_seq;

PostgreSQL 实现 #

方法1：VALUES子句构造列表（最简洁） #

PostgreSQL支持直接将VALUES作为派生表查询，语法更接近Python列表：

INSERT INTO student (name, age)
SELECT 'hoge', age
FROM (VALUES (10), (20), (30), (100), (200)) AS age_list(age);

方法2：结合数组与unnest函数 #

适合批量处理大量自定义值：

INSERT INTO student (name, age)
SELECT 'hoge', unnest(array[10,20,30,100,200]);

方法3：递归CTE处理规律+特殊值 #

和MySQL逻辑一致，写法略有差异：

WITH RECURSIVE age_seq AS (
    SELECT 10 AS age
    UNION ALL
    SELECT age + 10 FROM age_seq WHERE age < 100
    UNION ALL
    SELECT 200
)
INSERT INTO student (name, age)
SELECT 'hoge', age FROM age_seq;

SQL Server 实现 #

方法1：UNION ALL构造自定义列表 #

INSERT INTO student (name, age)
SELECT 'hoge' AS name, age
FROM (
    SELECT 10 AS age UNION ALL
    SELECT 20 UNION ALL
    SELECT 30 UNION ALL
    SELECT 100 UNION ALL
    SELECT 200
) AS age_list;

方法2：VALUES派生表（SQL Server 2022+支持） #

INSERT INTO student (name, age)
SELECT 'hoge', age
FROM (VALUES (10), (20), (30), (100), (200)) AS age_list(age);

方法3：递归CTE生成混合序列 #

WITH age_seq AS (
    SELECT 10 AS age
    UNION ALL
    SELECT age + 10 FROM age_seq WHERE age < 100
    UNION ALL
    SELECT 200
)
INSERT INTO student (name, age)
SELECT 'hoge', age FROM age_seq;

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