如何通过SQL批量生成符合约束条件的数据库测试数据？

一、生成utilisateurs表的500+测试数据 #

使用MySQL内置随机函数结合递归CTE，可快速生成符合要求的批量数据，无需手动编写每条SQL。以下是完整SQL语句：

WITH RECURSIVE generate_data(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM generate_data WHERE n < 600 -- 生成600条，可按需调整数量
)
INSERT INTO utilisateurs(prenom, nom, date_de_naissance, email, mot_de_passe, maladie_chronique, latitude, longitude)
SELECT
    -- 随机选取阿尔及利亚常见名字
    ELT(FLOOR(RAND()*8 + 1), 'Ahmed', 'Mohamed', 'Fatima', 'Sara', 'Ali', 'Amina', 'Karim', 'Lila') AS prenom,
    -- 随机选取阿尔及利亚常见姓氏
    ELT(FLOOR(RAND()*6 + 1), 'Benali', 'Bouabdallah', 'Khelil', 'Bouzid', 'Mehdi', 'Cherif') AS nom,
    -- 生成1950-2005年间的合理出生日期
    DATE_ADD('1950-01-01', INTERVAL FLOOR(RAND()*20000) DAY) AS date_de_naissance,
    -- 生成符合格式的阿尔及利亚域名邮箱
    CONCAT(LOWER(prenom), '.', LOWER(nom), FLOOR(RAND()*100), '@example.dz') AS email,
    -- 生成随机加密密码（模拟MD5哈希）
    MD5(RAND()) AS mot_de_passe,
    -- 随机选择慢性病类型，包含"无患病"选项
    ELT(FLOOR(RAND()*4 + 1), 'Aucune', 'Diabète', 'Hypertension', 'Asthme') AS maladie_chronique,
    -- 生成阿尔及利亚范围内的纬度（保留6位小数）
    ROUND(18.969168 + RAND()*(37.101043 - 18.969168), 6) AS latitude,
    -- 生成阿尔及利亚范围内的经度（保留6位小数）
    ROUND(-8.673947 + RAND()*(11.999273 - (-8.673947)), 6) AS longitude
FROM generate_data;

字段逻辑说明： #

• 姓名：从预设的阿尔及利亚常见姓名列表中随机选取，保证数据贴合当地场景。
• 出生日期：覆盖1950到2005年区间，符合真实用户的年龄分布。
• 邮箱：结合姓名+随机数字+阿尔及利亚国家域名.dz，模拟真实邮箱格式。
• 密码：用MD5(RAND())生成随机哈希值，模拟加密后的用户密码。
• 慢性病：提供4种常见选项（含"无患病"），符合实际医疗场景。
• 经纬度：通过计算给定范围的随机值，确保坐标严格落在阿尔及利亚境内。

二、生成utilisateurs_malade表的300+关联测试数据 #

基于已生成的utilisateurs表主键，结合随机函数生成关联数据，确保外键约束有效：

WITH RECURSIVE generate_reports(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM generate_reports WHERE n < 400 -- 生成400条，可按需调整数量
)
INSERT INTO utilisateurs_malade(id_utilisateur, dernirement_malade)
SELECT
    -- 从utilisateurs表随机选取存在的用户ID，避免外键冲突
    (SELECT id_utilisateur FROM utilisateurs ORDER BY RAND() LIMIT 1) AS id_utilisateur,
    -- 生成最近1年内的随机患病日期
    DATE_SUB(CURDATE(), INTERVAL FLOOR(RAND()*365) DAY) AS dernirement_malade
FROM generate_reports;

逻辑说明： #

• 关联用户ID：通过子查询从已存在的用户表中随机选取主键，保证外键关联合法性。
• 患病日期：生成当前日期往前1年内的随机日期，符合"最近患病"的业务逻辑。

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