如何生成百万级有序SQL测试数据用于排序算法性能验证？

针对生成百万条id严格递增的有序数据需求，不同主流数据库有高效实现方式，以下是具体写法：

MySQL 方案 #

8.0+版本（推荐，基于递归CTE） #

MySQL 8.0及以上支持递归公共表表达式（CTE），能快速生成批量有序数据：

-- 创建目标表（若未创建）
CREATE TABLE genre (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name CHAR(50) NOT NULL
);

-- 插入百万条有序数据，名称用前缀+编号生成
INSERT INTO genre (name)
WITH RECURSIVE seq AS (
    SELECT 1 AS num
    UNION ALL
    SELECT num + 1 FROM seq WHERE num < 1000000
)
SELECT CONCAT('Genre_', num) FROM seq;

低于8.0版本（存储过程实现） #

若版本不支持CTE，可通过存储过程循环插入：

DELIMITER //
CREATE PROCEDURE InsertMillionGenres()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= 1000000 DO
        INSERT INTO genre (id, name) VALUES (i, CONCAT('Genre_', i));
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

-- 调用存储过程生成数据
CALL InsertMillionGenres();

注：存储过程循环插入速度较慢，优先选择CTE方案。

PostgreSQL 方案 #

PostgreSQL自带generate_series函数，可直接生成序列数据，效率极高：

CREATE TABLE genre (
    id INT PRIMARY KEY,
    name CHAR(50) NOT NULL
);

-- 插入百万条有序数据
INSERT INTO genre (id, name)
SELECT 
    generate_series(1, 1000000),
    CONCAT('Genre_', generate_series(1, 1000000));

SQL Server 方案 #

使用递归CTE生成，需关闭递归层数限制：

CREATE TABLE genre (
    id INT PRIMARY KEY IDENTITY(1,1),
    name CHAR(50) NOT NULL
);

-- 递归生成百万条数据
WITH seq AS (
    SELECT 1 AS num
    UNION ALL
    SELECT num + 1 FROM seq WHERE num < 1000000
)
INSERT INTO genre (name)
SELECT CONCAT('Genre_', num) FROM seq
OPTION (MAXRECURSION 0);

复用示例分类名称的扩展方案 #

如果需要循环使用你给出的7个分类名称（而非编号前缀），同时保证id有序，可通过临时表+取模实现：
以MySQL为例：

-- 创建临时分类名称表
CREATE TEMPORARY TABLE genre_names (name CHAR(50));
INSERT INTO genre_names VALUES 
('Adventure'), ('Animation'), ('Children'), ('Comedy'), ('Fantasy'), ('Romance'), ('Drama');

-- 生成百万条有序数据，循环复用分类名称
INSERT INTO genre (name)
WITH RECURSIVE seq AS (
    SELECT 1 AS num
    UNION ALL
    SELECT num + 1 FROM seq WHERE num < 1000000
)
SELECT gn.name
FROM seq
JOIN (
    SELECT name, ROW_NUMBER() OVER (ORDER BY name) AS rn 
    FROM genre_names
) gn ON (seq.num - 1) % (SELECT COUNT(*) FROM genre_names) = gn.rn - 1;

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