MySQL 데이터 INSERT - 다대다 관계 (bulk insert)

💬 다대다 관계에서 MySQL 쿼리를 통해 한 번의 요청으로 MySQL에 두개 테이블에 데이터를 추가해야 하는 상황이 생긴다.

🔎 쇼핑몰 홈페이지를 만든다고 했을 때 로그인한 사용자가 여러 품목, 수량을 선택해 구매 요청을 하는 경우 클라이언트에서는 다음과 같은 데이터를 body에 담아 POST 요청을 하게 된다.

const handleOrder = (orders, totalPrice) => {
    if (totalPrice) {
      const payload = JSON.stringify({
        orders,
        totalPrice,
      });

      return fetch(`http://localhost:4000/users/${userId}/orders`, {
        method: 'POST',
        headers: {
          'Content-Type': 'application/json',
        },
        body: payload,
      })
        .then(orders.forEach((el) => dispatch(removeFromCart(el.itemId))))
        .then(dispatch(notify('주문이 완료되었습니다.')))
        .then(
          dispatch(
            fetchData(`http://localhost:4000/users/${userId}/orders`, setOrders)
          )
        )
        .then(() => {
          history.push('/orderlist');
        })
        .catch((err) => console.log(err));
    } else {
      dispatch(notify('상품을 한 개 이상 선택해주세요.'));
    }
  };

🔎 서버에서는 Params(userId), body로 다음과 같은 데이터를 받게 되고 이를 데이터베이스에 저장해야 함

		{
          orders: [
            { itemId: 1, quantity: 2 },
            { itemId: 2, quantity: 5 },
          ],
          totalPrice: 79800,
        }

User가 1번 item 2개, 2번 item 5개를 선택해 총 79800원의 상품을 구매

🔎 MySQL 스키마 구성

users TABLE

id	username
1	김코딩

orders TABLE

id	user_id	total_price	created_at

items TABLE

id	name	price	image
'1'	'노른자 분리기'	'9900'	'../images/egg.png'
'2'	'2020년 달력'	'12000'	'../images/2020.jpg'
'3'	'개구리 안대'	'2900'	'../images/frog.jpg'
'4'	'뜯어온 보도블럭'	'4900'	'../images/block.jpg'
'5'	'칼라 립스틱'	'2900'	'../images/lip.jpg'
'6'	'잉어 슈즈'	'3900'	'../images/fish.jpg'
'7'	'웰컴 매트'	'6900'	'../images/welcome.jpg'
'8'	'강시 모자'	'9900'	'../images/hat.jpg'

order_items TABLE

id	order_id	item_id	order_quantity

🔎 즉 위와 같은 요청이 있을 시

1. orders Table 에 user_id와 total_price를 INSERT 해주고 난 후,
2. 동시에 orders Table의 해당 id 값을 order_items Table에 INSERT + orders안에 배열 값을 차례대로 INSERT 해줘야 한다.

📌 다음과 같은 형태를 통해 이를 해결할 수 있다.

    post: (userId, orders, totalPrice, callback) => {
	
 	// 1. orders Table에 user_id, total_price INSERT 
      const queryString = `INSERT INTO orders (user_id, total_price) VALUES (${userId}, ${totalPrice})`

      db.query(queryString, (err, result) => {
       // 위의 쿼리를 실행하고 난 후 콜백을 통해 result를 받게 되면 그 안에는 여러 객체들이 저장되어 있다.
      // 그 중 insertId는 해당 쿼리를 실행하고 난 후의 primary key 값을 반환한다.
        if(result){
          const queryString2 = `INSERT INTO order_items (order_id, item_id, order_quantity) VALUES ?`;
          const params2 = orders.map(el => {
            return [result.insertId, el.itemId, el.quantity];
          });
          // 이와 같은 형태로 이중 배열에 담아 쿼리를 전달하게 되면 연속적인 쿼리를 보낼 수 있다.(3중 배열)
          db.query(queryString2, [params2], (err, result)=>{
            callback(err, result);
          })
        }
        else{        
          callback(err, result)
        }
      });
    }

쿼리를 실행하고 난 후 반환하는 result 값을 console.log로 찍어보면

{
  fieldCount: 0,
  affectedRows: 1,
  insertId: 2,
  serverStatus: 2,
  warningCount: 0,
  message: '',
  protocol41: true,
  changedRows: 0
}

여기서 insertId는 해당 쿼리를 실행한 id(PK)

🔥 이와 같이 3중 배열로 2개 테이블에 데이터를 넣어주는 형태를 bulk insert 라고 한다