[TIL] 10/15 MySQL 데이터 삭제-DELETE/DROP/TRUNCATE, 주문하기 API

1. MySQL 데이터 삭제하기

 

1.1. DELETE

 

조건을 걸어주지 않으면 모든 행 삭제 -> 모든 행이 삭제되어도 테이블은 남아있음 

DELETE FROM 테이블명;

 

조건을 걸어주면 해당 조건에 해당하는 행 삭제

DELETE FROM 테이블명 WHERE 조건;

 

 

1.2. DROP

 

테이블을 통째로 삭제

DROP TABLE 테이블명;

 

 

1.3. TRUNCATE

 

모든 행이 삭제됨 -> 테이블은 남아있음

 

TRUNCATE 테이블명;

 

 

1.4. DELETE vs TRUNCATE

 

DELETE 와 TRUNCATE 는 모든 행을 삭제할 때 둘 다 테이블이 남아있다. 그렇다면 무슨 차이가 있을까?

 

DELETE 는 AUTO_INCREMENT 를 걸어놓은 열이 있을 때,

모든 데이터를 지웠어도 AUTO_INCREMENT 기록은 남아있어서 이전 값에서 이어간다. 

 

DELETE 로 모든 행을 지우고, 처음 데이터를 삽입해도 id 값이 5인 것을 볼 수 있다. 

 

 

TRUNCATE 는 AUTO_INCREMENT 를 걸어놓은 열이 있을 때, 

모든 데이터를 지우면 AUTO_INCREMENT 기록도 함께 지워주기 때문에 초기화 되어서 1부터 시작한다.

 

 

1.5. FOREIGN_KEY_CHECKS

 

데이터를 삭제하려고 할 때 이런 에러가 났다. 

 

12:58:29 TRUNCATE orders Error Code: 1701. Cannot truncate a table referenced in a foreign key constraint (`book-shop`.`orderedBook`, CONSTRAINT `fk_orderedBook_orders_id` FOREIGN KEY (`order_id`) REFERENCES `book-shop`.`orders` (`id`)) 0.0011 sec

 

FOREIGN KEY 제약조건으로 발생하는 에러로, orders 테이블을 지우려고 하자 orderedBook 테이블에서 데이터를 참조하고 있다고 지우지 못하도록 하고 있다. 하지만, 이미 orderedBook 테이블의 행은 모두 지운 상태이기 때문에 orderedBook 에서 orders 테이블을 참조하고 있는 행은 존재하지 않는다. 

 

이럴 때는 아래의 명령어를 사용해서 해결할 수 있다. 

이 명령어는 FOREIGN KEY 제약조건을 비활성화 하는 명령어로 일시적으로 FOREIGN KEY 무결성 검사를 비활성화하여 테이블의 데이터를 수정하거나 삭제할 수 있도록 해준다. 

SET FOREIGN_KEY_CHECKS = 0;

 

수정/삭제를 진행한 후에는, 0이 아닌 1을 넣어서 명령어를 입력해주면 비활성화 됐던 FOREIGN KEY 제약조건이 활성화 된다. 

SET FOREIGN_KEY_CHECKS = 1;

 

 

 

2. 주문 기능 구현

 

2.1. 주문하기

 

기존의 주문하기 API 를 수정했다. 

 

items 라는 배열에 {cartItemId, bookId, quantity} 를 여러 개 담아서 리퀘스트 요청을 보냈었는데,

items 배열에 cartItemId 들만 담아서 보내고, cartItemId 를 통해서 장바구니 조회 후 정보를 가져오는 방식으로 바꿨다. 

 

cartItemId 로 조회한 정보를 사용해서 orderedBook 테이블에 INSERT 하고, 장바구니에서 해당 도서들을 삭제하였다. 

삭제하는 함수는 따로 deleteCartItems 라는 모듈을 만들어서 불러서 사용하였다. 

 

어려웠던 부분은 sql 쿼리문에서 IN 을 사용할 때 (1,2,3) 이렇게 여러 개의 값과 함께 사용할 경우 execute 를 사용하지 못하기 때문에 query 를 사용해야 했는데, 그 부분을 몰라서 처리하는 데 애를 먹었다. 

const order = async (req, res) => {
    const conn = await mysql.createConnection({
        host: 'localhost',
        user: 'root',
        password: 'root',
        database: 'book-shop',
        dateStrings: true
    });

    const {items, delivery, totalQuantity, totalPrice, userId, firstBookTitle} = req.body;

    // delivery
    let sql = `INSERT INTO delivery (address, receiver, contact) VALUES (?, ?, ?)`;
    let values = [delivery.address, delivery.receiver, delivery.contact];
    let [results] = await conn.execute(sql, values);
    let delivery_id = results.insertId;

    // orders
    sql = `INSERT INTO orders (book_title, total_quantity, total_price, user_id, delivery_id)
            VALUES (?, ?, ?, ?, ?)`
    values = [firstBookTitle, totalQuantity, totalPrice, userId, delivery_id]
    let orderResults = await conn.execute(sql, values);
    let order_id = orderResults[0].insertId;

    // items를 가지고, 장바구니에서 book_id, quantity를 조회
    sql = `SELECT book_id, quantity FROM cartItems WHERE id IN (?)`;
    let [orderItems, fields] = await conn.query(sql, [items]);

    // orderedBook 테이블 삽입
    sql = `INSERT INTO orderedBook (order_id, book_id, quantity) VALUES ?` // 벌크로 insert 를 한다
    values = [];
    orderItems.forEach((item) => {
        values.push([order_id, item.book_id, item.quantity]);
    })
    results = await conn.query(sql, [values]);

    let result = await deleteCartItems(conn, items);

    return res.status(StatusCodes.OK).json(results);
};

// 주문한 도서 장바구니에서 삭제
const deleteCartItems = async (conn, items) => {
    let sql = `DELETE FROM cartItems WHERE id IN (?)`;

    let result = await conn.query(sql, [items]);
    return result;
}

 

 

2.2. 주문 내역 조회

const getOrders = async (req, res) => {
    const conn = await mysql.createConnection({
        host: 'localhost',
        user: 'root',
        password: 'root',
        database: 'book-shop',
        dateStrings: true
    });

    let sql = `SELECT orders.id, book_title, total_quantity, total_price, created_at, address, receiver, contact
                FROM orders LEFT JOIN delivery
                ON orders.delivery_id = delivery.id`
    let [rows, fields] = await conn.query(sql);
    return res.status(StatusCodes.OK).json(rows);
};

 

 

2.3. 주문 상세 조회

 

books 테이블에서 title 을 가져오면서 book_title 로 컬럼명을 출력해주고 싶어서 AS 를 사용해서 book_title 로 출력해주었다. 

const getOrderDetail = async (req, res) => {
    const {id} = req.params;

    const conn = await mysql.createConnection({
        host: 'localhost',
        user: 'root',
        password: 'root',
        database: 'book-shop',
        dateStrings: true
    });

    let sql = `SELECT book_id, title AS book_title, author, price, quantity
                FROM orderedBook LEFT JOIN books
                ON orderedBook.book_id = books.id 
                WHERE order_id = ?;`

    let [rows, fields] = await conn.query(sql, [id]);
    return res.status(StatusCodes.OK).json(rows);
};