MySQLで大量テストデータ作成

はじめに

「適当にデータを突っ込みたい」、「簡単な負荷テストをしたい」ってときに毎回いろんなサイトを調べてしまうのでまとめた。

テストデータ作成の基本形

数値

-- 0～99の数値
SELECT FLOOR(RAND() * 100);
-- 1～100の数値
SELECT CEIL(RAND() * 100);
-- 5～10の数値
SELECT FLOOR(RAND() * (10 - 5 + 1)) + 5;

文字

-- 10文字の文字列
SELECT SUBSTRING(SHA1(RAND()),1,10);
-- a-zの1文字
SELECT SUBSTRING('abcdefghijklmnopqrstuvwxyz', CEIL(RAND() * 27 ), 1);
-- a-z,A-Z,1-9の1文字
SELECT SUBSTRING('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', CEIL(RAND() * 62 ), 1);

特定の文字

-- spring,summer,fall,winterのどれか（１）
SELECT 
CASE CEIL(RAND() * 4)
    WHEN 1 THEN 'spring'
    WHEN 2 THEN 'summer'
    WHEN 3 THEN 'fall'
    WHEN 4 THEN 'winter'
END;
-- spring,summer,fall,winterのどれか（２）
SELECT ELT(FLOOR(1 + (RAND() * 4)),'spring', 'summer', 'fall', 'winter');

日付

-- 2010/01/01~2020/12/31の間
SELECT DATE_ADD('2010-01-01', INTERVAL FLOOR(RAND() * (DATEDIFF('2020-12-31', '2010-01-01'))) DAY) AS random_date;
-- 2010/01/01~2020/12/31の間（時間込み）
SELECT DATE_ADD('2010-01-01', INTERVAL FLOOR(RAND() * (DATEDIFF('2020-12-31', '2010-01-01'))) DAY) + INTERVAL FLOOR(RAND() * 24) HOUR + INTERVAL FLOOR(RAND() * 60) MINUTE + INTERVAL FLOOR(RAND() * 60) SECOND AS random_datetime;

大量データ登録

テストテーブル作成

-- 部署テーブル
CREATE TABLE departments (
    department_id INT AUTO_INCREMENT PRIMARY KEY,  -- 部署ID
    department_name VARCHAR(100) NOT NULL          -- 部署名
);

-- 社員テーブル
CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,    -- 社員ID
    name VARCHAR(50) NOT NULL,                     -- 名
    hire_date DATE,                                -- 入社日
    salary DECIMAL(10, 2),                         -- 給与
    department_id INT,                             -- 部署ID
    email VARCHAR(100),                            -- メールアドレス
    phone_number VARCHAR(20),                      -- 電話番号
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

1行登録

INSERT INTO employees (
    name,
    hire_date,
    salary,
    email,
    phone_number
) 
SELECT 
    MD5(RAND()),
    DATE_ADD('2010-01-01', INTERVAL FLOOR(RAND() * (DATEDIFF('2020-12-31', '2010-01-01'))) DAY),
    FLOOR(RAND() * (1000000 - 100000 + 1)) + 100000,
    MD5(RAND()),
    CONCAT(ELT(FLOOR(1 + (RAND() * 3)),"070-", "080-", "090-"), LPAD(FLOOR(RAND() * 10000), 4, '0'), '-', LPAD(FLOOR(RAND() * 10000), 4, '0'));

プロシージャで大量登録

部署テーブルと社員テーブルの整合性を保ったままデータを登録

-- ★プロシージャの作成
DELIMITER //
CREATE PROCEDURE insert_data(
    departments_loop_count INT,    -- 部署テーブルへのレコード作成数
    employees_loop_count INT       -- 従業員テーブルへのレコード作成数
)
BEGIN
    DECLARE departments_insert_id INT DEFAULT 0;
    DECLARE departments_num INT DEFAULT 0;
    DECLARE employees_num INT DEFAULT 0;

    -- 部署テーブルへ登録
    WHILE departments_num < departments_loop_count DO
        INSERT INTO departments (
            department_name
        )
        SELECT
            MD5(RAND());
        
        SET departments_num = departments_num + 1;
        SET employees_num = 0;
        -- 部署テーブルへ登録したときのIDを取得
        SET departments_insert_id = LAST_INSERT_ID();

        -- 従業員テーブルへ登録
        WHILE employees_num < employees_loop_count DO
            INSERT INTO employees (
                name,
                hire_date,
                salary,
                department_id,
                email,
                phone_number
            ) 
            SELECT 
                MD5(RAND()),
                DATE_ADD('2010-01-01', INTERVAL FLOOR(RAND() * (DATEDIFF('2020-12-31', '2010-01-01'))) DAY),
                FLOOR(RAND() * (1000000 - 100000 + 1)) + 100000,
                departments_insert_id,
                MD5(RAND()),
                CONCAT(ELT(FLOOR(1 + (RAND() * 3)),"070-", "080-", "090-"), LPAD(FLOOR(RAND() * 10000), 4, '0'), '-', LPAD(FLOOR(RAND() * 10000), 4, '0'));
            
            SET employees_num = employees_num + 1;
        END WHILE;
    END WHILE;
END //
DELIMITER ;

-- ★プロシージャ実行（5部署登録で1部署につき10人従業員登録）
CALL insert_data (5, 10);