如何向users表插入10亿条唯一数据？现有SQL仅生成16条唯一值

你写的SQL之所以只能生成寥寥几条唯一值，是因为子查询里t1和t2的笛卡尔积总共才20行（4行×5行）——哪怕你写了LIMIT 1000000，实际也只能取出这20行数据，你看到16条可能是执行时的重复或者小异常，但核心问题是这种方式根本撑不起千万甚至十亿级的数据量。

下面给你几种可行的解决方案，适配不同版本的MySQL：

方法1：递归CTE（MySQL 8.0+ 适用） #

递归CTE能高效生成连续数字序列，是生成大量唯一ID的首选方式：

-- 先关闭不必要的检查提升插入速度
SET unique_checks = 0;
SET foreign_key_checks = 0;
SET autocommit = 0;

INSERT INTO users (username, email, validts, confirmed)
WITH RECURSIVE numbers(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 1000000000
)
SELECT 
    CONCAT('user', n) AS username,
    CONCAT('user', n, '@example.com') AS email,
    UNIX_TIMESTAMP() + 2592000 AS validts,
    0 AS confirmed
FROM numbers;

COMMIT;
-- 恢复默认设置
SET unique_checks = 1;
SET foreign_key_checks = 1;
SET autocommit = 1;

⚠️ 注意：直接生成10亿行递归可能会耗尽内存，建议分批生成，比如每次插100万行，循环执行：

DELIMITER //
CREATE PROCEDURE insert_users_batch()
BEGIN
    DECLARE start_num INT DEFAULT 1;
    DECLARE batch_size INT DEFAULT 1000000;
    DECLARE total_rows INT DEFAULT 1000000000;
    
    SET unique_checks = 0;
    SET foreign_key_checks = 0;
    SET autocommit = 0;
    
    WHILE start_num <= total_rows DO
        INSERT INTO users (username, email, validts, confirmed)
        WITH RECURSIVE numbers(n) AS (
            SELECT start_num
            UNION ALL
            SELECT n + 1 FROM numbers WHERE n < start_num + batch_size - 1
        )
        SELECT 
            CONCAT('user', n) AS username,
            CONCAT('user', n, '@example.com') AS email,
            UNIX_TIMESTAMP() + 2592000 AS validts,
            0 AS confirmed
        FROM numbers;
        
        SET start_num = start_num + batch_size;
        COMMIT;
    END WHILE;
    
    SET unique_checks = 1;
    SET foreign_key_checks = 1;
    SET autocommit = 1;
END //
DELIMITER ;

-- 调用存储过程执行批量插入
CALL insert_users_batch();

方法2：多层笛卡尔积（低版本MySQL 适用） #

如果你的MySQL不支持递归CTE，可以用多层笛卡尔积来放大行数，每层10行的话，9层就能生成10^9行：

-- 生成10亿行数据，建议分批执行
INSERT INTO users (username, email, validts, confirmed)
SELECT 
    CONCAT('user', (@row := @row + 1)) AS username,
    CONCAT('user', @row, '@example.com') AS email,
    UNIX_TIMESTAMP() + 2592000 AS validts,
    0 AS confirmed
FROM 
    (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t1,
    (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t2,
    (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t3,
    (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t4,
    (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t5,
    (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t6,
    (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t7,
    (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t8,
    (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t9,
    (SELECT @row := 0) t_init
LIMIT 1000000000;

同样建议拆分成分批任务，比如每次插100万行，避免一次性占满系统资源。

必看的优化要点 #

• 临时关闭检查：插入前关闭unique_checks、foreign_key_checks，禁用自动提交，能把插入速度提升好几倍，完成后记得恢复。
• 分批插入：10亿条数据一次性插入会直接把磁盘IO和内存拉爆，建议每次插100万-1000万行，循环执行。
• 索引先删后建：如果username或email有唯一索引，插入前先删掉，插完再重建——不然每次插入都要更新索引，速度慢到离谱。
• 提前确认空间：10亿条数据按每条100字节算，至少要100GB以上的磁盘空间，别插一半才发现磁盘满了。

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