되돌릴 수 없는 표를 고치기 전에

정산 금액을 일괄로 조정해 달라는 요청을 받았다. 특정 기간의 수수료율이 잘못 들어갔다. 쿼리 한 줄이면 되는 일로 보였다.

Table of contents

그 표는 다시 계산할 수 없었다

돌리려던 것은 이 한 줄이었다.

UPDATE settlement
SET commission = amount * 0.05
WHERE settle_date BETWEEN '2014-08-01' AND '2014-08-31';

바꾸기 전 값이 어디에도 없다. settlement 에는 이력이 없고 원본에서 다시 계산할 방법도 없었다. 주문 금액은 남아 있지만 그때 적용한 수수료율이 상품마다 달랐다.

한 번 덮으면 이전 값을 아무 데서도 못 가져온다. 상품 정보는 잘못 바꿔도 다시 입력할 수 있지만 정산은 돈이 오간 기록이라 그럴 수 없다.

다시 계산되는 표와 아닌 표는 다루는 방식이 달라야 했다. 이 표는 뒤쪽이니 고치기 전에 사본이 필요했다.

바꿀 행만 사본으로 남겼다

바꿀 범위만 떴다.

CREATE TABLE settlement_bak_20140826 AS
SELECT * FROM settlement
WHERE settle_date BETWEEN '2014-08-01' AND '2014-08-31';

전체를 뜨면 크고 어차피 그 범위만 바뀐다. 만들고 나서 개수를 확인했다.

SELECT COUNT(*) FROM settlement_bak_20140826;
-- 1,842

SELECT COUNT(*) FROM settlement
WHERE settle_date BETWEEN '2014-08-01' AND '2014-08-31';
-- 1,842

같아야 한다. 사본을 만든 뒤 세는 것이 사본이 제대로 만들어졌다는 근거다.

이름에 날짜를 넣은 것은 언제 만든 것인지 알아야 나중에 지울 수 있기 때문이다. 이름만 보고 못 지우는 사본이 쌓이는 것이 이 스키마에서 흔한 일이었다.

무엇이 어떻게 바뀔지 먼저 봤다

UPDATE 를 돌리기 전에 같은 조건으로 SELECT 를 했다.

SELECT settle_no, amount, commission AS old_commission,
       amount * 0.05 AS new_commission,
       (amount * 0.05) - commission AS diff
FROM settlement
WHERE settle_date BETWEEN '2014-08-01' AND '2014-08-31'
ORDER BY ABS((amount * 0.05) - commission) DESC
LIMIT 20;

ORDER BY ABS(...) 로 차이가 큰 것부터 봤다.

settle_no  amount    old      new     diff
  10231   4,200,000  420,000  210,000  -210,000
  10244   3,800,000  380,000  190,000  -190,000

수수료가 절반으로 준다. 물어보니 10%로 잘못 들어간 것을 5%로 고치는 일이 맞았다. 확인 안 하고 돌렸으면 방향이 반대인 경우도 있었다.

이미 5%로 맞게 들어간 것도 있을까 봤다.

SELECT COUNT(*) FROM settlement
WHERE settle_date BETWEEN '2014-08-01' AND '2014-08-31'
  AND ABS(commission - amount * 0.05) < 1;
-- 213

213건은 이미 맞아서 대상에서 뺐다.

UPDATE settlement
SET commission = amount * 0.05
WHERE settle_date BETWEEN '2014-08-01' AND '2014-08-31'
  AND ABS(commission - amount * 0.05) >= 1;

여기에 확인하고 넘어갈 것이 하나 있었다. MySQL 은 UPDATE 뒤의 ROW_COUNT() 를 기본적으로 실제로 값이 바뀐 행 수로 돌려준다. 접속할 때 CLIENT_FOUND_ROWS 를 주면 그때만 WHERE 에 걸린 행 수가 된다.

이미 맞는 행을 조건에 남겨 두면 미리 세어 둔 개수와 ROW_COUNT() 가 서로 다른 숫자가 된다. 빼고 나면 둘이 같아져 하나로 대조할 수 있다. 바뀐 행 수가 1,629여야 한다.

검증 — 돌린 뒤의 확인

돌리고 나서 확인했다.

SELECT ROW_COUNT();
-- 1629

예상과 맞다. 값도 확인했다.

SELECT COUNT(*) FROM settlement
WHERE settle_date BETWEEN '2014-08-01' AND '2014-08-31'
  AND ABS(commission - amount * 0.05) >= 1;
-- 0

조건에 맞는 행이 0이면 다 바뀐 것이다. 남아 있으면 조건이 예상과 달랐다는 뜻이다.

사본과 대조해서 합계 차이도 봤다.

SELECT
  (SELECT SUM(commission) FROM settlement_bak_20140826) AS before_sum,
  (SELECT SUM(commission) FROM settlement
    WHERE settle_date BETWEEN '2014-08-01' AND '2014-08-31') AS after_sum;

SUM() 이 예상한 만큼 움직였는지 본 것이다.

사본을 언제 지울지도 정했다. 정산은 확인 기간이 있어서 바로 못 지우므로 다음 달 마감이 끝난 뒤로 잡았다. 지울 시점을 안 정하면 사본이 계속 남아 어느 것이 무엇인지 모르게 된다.

정리

• 다시 계산할 수 없는 표는 한 번 덮으면 끝이다
• 다시 계산되는 표와 아닌 표는 다루는 방식이 다르다
• 고치기 전에 바꿀 범위만 사본을 만들고 개수를 확인한다
• 사본 이름에 날짜를 넣고 지울 시점을 정한다
• UPDATE 전에 같은 조건으로 SELECT 해서 전후를 본다
• ORDER BY ABS(...) 로 차이가 큰 것부터 보면 방향 실수가 보인다
• MySQL 의 ROW_COUNT() 는 기본이 실제로 바뀐 행 수다
• CLIENT_FOUND_ROWS 를 줘야 WHERE 에 걸린 행 수가 된다
• 이미 맞는 행을 조건에서 빼면 두 숫자가 같아진다
• 돌린 뒤 조건에 맞는 행이 0인지와 SUM() 을 확인한다