[MySql] 대용량 테스트 데이터 생성하기

 

 

주제

• 프로시저를 이용한 더미 데이터 생성

 

---

 

1. 프로시저를 이용한 더미 데이터 생성 

use kingpiggy_study;

/*
 * 더미 데이터 생성 예제
 * 
 */


-- DROP TABLE IF EXISTS `tb_kpg_code_group`
-- DROP TABLE IF EXISTS `tb_kpg_code`
-- DROP TABLE IF EXISTS `tb_kpg_items`
-- TRUNCATE `tb_kpg_code_group`;
-- TRUNCATE `tb_kpg_code`;
-- TRUNCATE `tb_kpg_items`;


-- 1. Create Table
CREATE TABLE `tb_kpg_code_group` (
    `code_group_id` bigint(20) NOT NULL AUTO_INCREMENT,
    `code_group_nm` varchar(255) DEFAULT NULL,
    PRIMARY KEY (`code_group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `tb_kpg_code` (
    `code_id` bigint(20) NOT NULL AUTO_INCREMENT,
    `code_group_id` bigint(20) NOT NULL,
    `code_nm` varchar(255) DEFAULT NULL,
    PRIMARY KEY (`code_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `tb_kpg_items` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `title` varchar(255) DEFAULT NULL,
    `description` varchar(255) DEFAULT NULL,
    `code_id` bigint(20) DEFAULT NULL,
    `base_date` varchar(255) DEFAULT NULL,
    `amount` decimal(10, 2) DEFAULT NULL,
    `created_at` datetime(6) DEFAULT NULL,
    `created_by` varchar(50) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- 2. Insert Sample Code
INSERT INTO `tb_kpg_code_group`(code_group_nm)
VALUES('음악');

INSERT INTO `tb_kpg_code`(code_group_id, code_nm)
VALUES(1, 'K-Pop'), (1, 'J-Pop'), (1, 'Pop Song');


-- 3. Create Procedure
DROP PROCEDURE IF EXISTS make_dummy_data_by_size;
CREATE PROCEDURE kingpiggy_study.make_dummy_data_by_size (
    dataSize INT
)
BEGIN
    DECLARE i INT DEFAULT 0;
   
    WHILE i < dataSize DO
    	CASE MOD(i, 3)
    		WHEN 0 THEN
                INSERT INTO tb_kpg_items(title, description, code_id, base_date, amount, created_at, created_by)
                VALUES(CONCAT('K-Pop_', i), CONCAT('desc_', i), 1, DATE_FORMAT(DATE(MAKEDATE(YEAR(NOW()), 1) + INTERVAL FLOOR(RAND() * 365) DAY), '%Y-%m-%d'), ROUND(RAND()*10000, -2), NOW(), 'SYSTEM');
            WHEN 1 THEN
                INSERT INTO tb_kpg_items(title, description, code_id, base_date, amount, created_at, created_by)
                VALUES(CONCAT('J-Pop_', i), CONCAT('desc_', i), 2, DATE_FORMAT(DATE(MAKEDATE(YEAR(NOW()), 1) + INTERVAL FLOOR(RAND() * 365) DAY), '%Y-%m-%d'), ROUND(RAND()*10000, -2), NOW(), 'SYSTEM');
            WHEN 2 THEN
                INSERT INTO tb_kpg_items(title, description, code_id, base_date, amount, created_at, created_by)
                VALUES(CONCAT('PopSong_', i), CONCAT('desc_', i), 3, DATE_FORMAT(DATE(MAKEDATE(YEAR(NOW()), 1) + INTERVAL FLOOR(RAND() * 365) DAY), '%Y-%m-%d'), ROUND(RAND()*10000, -2), NOW(), 'SYSTEM');
        END CASE;
		
        SET i = i + 1;
    END WHILE;
END;


-- 4. Call Procedure
CALL make_dummy_data_by_size(100000);


-- 5. select
SELECT * FROM tb_kpg_items ORDER BY id DESC;