MYSQL에서 Insert와 update 하나의 구문으로 처리하기 (ON DUPLICATE KEY UPDATE, UPSERT, sequalize)

현재 데이터의 상태를 업데이트 하는 쿼리를 만들다가 데이터가 있는지 DB접근 1 번 그리고 데이터가 없다면 삽입 있다면 업데이트를 하는 로직을 따로 만들었다.

그러나 이를 한번에 할 수 있는 방법이 있을거 같아 찾아보니

"MySQL에서는 INSERT ... ON DUPLICATE KEY UPDATE를 사용하여 레코드가 존재하면 업데이트하고, 존재하지 않으면 삽입하는 동작을 한 번의 쿼리로 수행할 수 있습니다. 그러나 먼저 이를 사용하려면 해당 테이블에 기본 키나 유니크 키 제약 조건이 있어야 합니다."

라고 챗 GPT가 알려줬다.

const query = `
    INSERT INTO DBNAME.${table_name} (A, B) 
    VALUES ('${a}', '${b}')
    ON DUPLICATE KEY UPDATE B = '${_b}';
`;

그리고 DUPLICATE KEY의 경우 "중복키 제약 조건"에 위배되는 경우 업데이트를 하는 구문이기 때문에

PK를 잘 설정해 주어야 한다.

상황에 따라 다르겠지만 현재는 유니스왑 v3의 틱을 PK로 설정해 두고 틱별 데이터를 저장 하려고 했기 때문에 틱을 PK로 두었다.

const query = `
    INSERT INTO DBNAME.${table_name} (A, B) 
    VALUES ('${a}', '${b}')
    ON DUPLICATE KEY UPDATE B = B + '${_b}';
`;

또한 만약 B가 토큰의 개수라고 치면 토큰 개수의 합산을 위해서는 다음과 같이 업데이트 구문을 바꿔 줄 수 도 있다.

그럼 업데이트가 2개 되는 구문은?

const query = `
    INSERT INTO DBNAME.${table_name} (A, B) 
    VALUES ('${a}', '${b}')
    ON DUPLICATE KEY UPDATE 
    A = A + '${_a}',
    B = B + '${_b}'
`;

이렇게 2개를 , 로 각각 지정해서 업데이트 해주면 된다.

물론 간현한 만큼 위와 같은 쿼리가 문제점도 있는데 바로 SQL injection 공격 에 취약하다는 것이다.

근데 해당 쿼리는 일단 블록체인 이벤트 데이터를 그대로 넣어주는 것이기도 하고 외부에서 호출하는 것이 아닌 내부에서 CronJob으로 사용하는 구문이고 시퀄라이즈도 사용중이기 때문에 SQL injection에 대한 문제가 없어 그대로 사용하였다.

그리고 해당 경우 시퀄라이즈에서 사용할때는

.query(query)

다음과 같이 queryTypes를 안주면 된다. 또한 여기에 대한 응답값은 [0,2000] 이런식으로 나오는데 앞의 값은 0이 나오는 것이고 뒤의 값은 영향을 받은 행의 수라고 한다.

const [results, metadata] = await sequelize.query("UPDATE users SET y = 42 WHERE x = 12");
// Results will be an empty array and metadata will contain the number of affected rows.

위 코드의 출저인 시퀄라이즈 공식 문서링크는 아래와 같다.

https://sequelize.org/docs/v6/core-concepts/raw-queries/

Raw Queries | Sequelize