[MySQL] Chat GPT로 샘플 데이터베이스 만들기

 

**DB 지식이 많이 부족하여, 사실과 다른 부분이 있을 수 있습니다. 언제든지 피드백 주시면 감사하겠습니다.

 

 

 

 MySQL을 Grafana와 연동하여 시각화하는 방법들을 정리해두고 싶어 샘플 데이터베이스가 필요했다.

실제로 쓰고 있는 데이터베이스를 쓸 순 없으니까 말이다.

 

 사실 ERD를 그려보거나 데이터를 모델링하는 것은 나에게는 아직 벅찬 일이기도 하고 지금 하려고 하는 본질도 아니기에 이번에는 Chat GPT를 아주 적극적으로 활용해보기로 했다.

 

일단 던져봤는데 딱 내가 원하느 수준으로 뱉아내는 GPT 선생님

 

 

1. 필요사항 분석

최대한 현재 회사에서 다루고 있는 데이터와 유사하려면, 시간과 숫자값이 적당히 혼재되어 있는 데이터베이스가 필요했다. 오라클에서는 나오는 dept, emp와 같은 유명한 샘플데이터를 쓰기엔 적합하지 않았다. 그래서 거래데이터를 포함해 E-Commerce 데이터베이스가 가장 적합하다고 느꼈다.

 

2. 데이터 모델링

총 4개 테이블로 제품, 주문, 주문상세, 고객 테이블로 구성하였다.

-- 제품 테이블
CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  price DECIMAL(10, 2),
  description TEXT
);

-- 주문 테이블
CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  total_price DECIMAL(10, 2),
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

-- 주문 상세 테이블
CREATE TABLE order_details (
  id INT PRIMARY KEY,
  order_id INT,
  product_id INT,
  quantity INT,
  price DECIMAL(10, 2),
  FOREIGN KEY (order_id) REFERENCES orders(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

-- 고객 테이블
CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100),
  address VARCHAR(200)
);

여기 연습용 DB에서는 굳이 별도의 id값이 필요할 것 같지 않아서 id값은 Auto_increment로 설정하여, index처럼 쓰기로 했다.

 

 

 

3. 더미데이터 추가

더미데이터도 알아서 제시해준다. 

사실 더미데이터는 찾아보니 꽤 유용한 사이트가 있었는데 그 중 하나가 mockaroo이다. 좀 더 디테일하게 커스텀하면서 무작위 값을 넣어줄 수 있는데, 이걸 쓰기도 아직은 벅차보여서 chat GPT에서 마저 물어보기로 했다.

 

제품, 고객 데이터는 따로 충돌할 만한 컬럼이 없으니 일단 이 두 테이블부터 밀어 넣어본다.

-- 제품 데이터 삽입
INSERT INTO products (id, name, price, description)
VALUES (1, '휴대폰', 1000000.00, '고성능 스마트폰'),
       (2, '노트북', 2000000.00, '휴대성과 성능을 겸비한 노트북'),
       (3, '텔레비전', 3000000.00, '고화질 LED TV'),
       (4, '스마트워치', 500000.00, '건강과 활동을 관리하는 스마트워치'),
       (5, '스피커', 100000.00, '고음질 스피커'),
       (6, '게임 콘솔', 4000000.00, '다양한 게임을 즐길 수 있는 콘솔'),
       (7, '이어폰', 50000.00, '고음질 이어폰'),
       (8, '카메라', 1500000.00, '고화질 사진을 찍을 수 있는 카메라'),
       (9, '모니터', 800000.00, '고해상도의 모니터'),
       (10, '키보드', 200000.00, '편리한 타이핑을 위한 키보드');

-- 고객 데이터 삽입
INSERT INTO customers (id, name, email, address)
VALUES (1, '홍길동', 'hong@example.com', '서울시 강남구'),
       (2, '김철수', 'kim@example.com', '서울시 종로구'),
       (3, '이영희', 'lee@example.com', '부산시 해운대구'),
       (4, '박민수', 'park@example.com', '대구시 중구'),
       (5, '정수진', 'jung@example.com', '인천시 남구'),
       (6, '강호준', 'kang@example.com', '대전시 서구'),
       (7, '신지우', 'shin@example.com', '광주시 동구'),
       (8, '김지현', 'kim@example.com', '울산시 북구'),
       (9, '이준호', 'lee@example.com', '경기도 성남시'),
       (10, '최현우', 'choi@example.com', '강원도 원주시');

 

이제 진짜 더미 데이터를 넣는데 몇 가지 조건들을 제시했다.

주문테이블에서는 23년 7월 10일부터 9월 30일까지 무작위로 넣어 총 80개의 레코드를 제시했다.

주문 상세 테이블에는 수량을 평균 1.8개로 맞춰달라고 했다. (지금와서 보니 최대값이 3으로 제한된 수식을 짰군...)

-- 주문 테이블에 더미 데이터 삽입
INSERT INTO orders (customer_id, order_date, total_price)
SELECT
  FLOOR(RAND() * 10) + 1 AS customer_id,
  DATE_ADD('2023-07-10', INTERVAL FLOOR(RAND() * 83) DAY) + INTERVAL FLOOR(RAND() * 10) HOUR AS order_date,
  NULL AS total_price
FROM
  information_schema.tables AS t1,
  information_schema.tables AS t2
LIMIT 80;

-- 주문 상세 테이블에 더미 데이터 삽입
INSERT INTO order_details (order_id, product_id, quantity, price)
SELECT
  orders.id AS order_id,
  FLOOR(RAND() * 5) + 1 AS product_id,
  ROUND(RAND() * 2 + 0.8, 1) AS quantity,
  FLOOR(RAND() * 20000) + 5000 AS price
FROM
  orders
WHERE
  HOUR(orders.order_date) < 22
ORDER BY
  orders.id;

 

그런데 여기서 사실 내가 요구사항을 똑바로 정리하지 않은 탓이지만,

주문 상세 테이블에 price값도 랜덤으로 넣는 바람에 수정이 필요했다. 그리고 수정 후 null으로 넣어두었던 주문 테이블의 total_price값도 넣어준다.

 

-- order_details 테이블의 price 값을 업데이트하는 쿼리
UPDATE order_details
JOIN products ON products.id = order_details.product_id
SET order_details.price = products.price;

-- orders 테이블의 total_amount 값을 업데이트하는 쿼리
UPDATE orders
SET total_price = (
  SELECT SUM(quantity * price)
  FROM order_details
  WHERE order_details.order_id = orders.id
);

이렇게 까지 넣어주면 더미데이터가 완성된다.

이제 이걸로 조회 쿼리도 연습해보고, 시각화 툴을 공부하기 위한 DB로 써먹을 예정이다.

 

 

4. ERD

나름 귀여운 DB가 완성되었다.

 

새롭게 알게 된 점

FK 충돌과 Auto-increment의 초기화

맨날 Select문으로 추출만 하는 일만 하다보니 이쪽 부분은 전혀 생각못했는데, 더미데이터를 넣어주는 순서도 중요했다.

생각해보면 PK가 없는데 FK를 넣을 순 없으니까 당연한 소리이다.

 

그런데 나는 이상한 곳에서 막혔는데 몇 번의 데이터 적재과정에서 테이블을 삭제하면서 id 컬럼의 제약조건에 Auto-increment 해두었다는 사실을 잊은 채 PK-FK의 관계가 설정된 테이블 간 서로 값이 맞지 않아서 FK 충돌이 났다. 

그래서 Auto-increment 값을 초기화를 해주고 해결했다.,

 

무려 11년 전, 조상님이 남기신 질문

https://stackoverflow.com/questions/8923114/how-to-reset-auto-increment-in-mysql

How to reset AUTO_INCREMENT in MySQL

 

#value값에 시작값을 넣어주면 된다. 처음부터 다시 시작하고 싶다면 1을 넣어준다.
ALTER TABLE tablename AUTO_INCREMENT = value;

 

이것도 실제 현업에서는 함부로 하면 안되는 아주 위험한 행동이라고 한다.

마찬가지 맥락으로 여러 FK로 연결되어 있을 수 있으니 말이다.