예제의 테이블과 데이터는 모두 설명을 위해 만든 가상의 것. 아래 실행 결과는 MariaDB 10.11과 Java 21에서 직접 실행한 값.
요약
- 증상: 리포트에서
app채널이 0건으로 표시된다. 그런데 표의 행을 모두 합치면 전체 합계보다 12만 건이 모자란다 - 원인: ① DB의 대소문자 무시 collation이
app과APP을 한 그룹으로 합치고 대표 라벨을 어느 쪽으로 고를지 보장하지 않는다, ② Java의HashMap은 대소문자를 구분한다 - 해결: 데이터를 고치지 않고 조회 단에서 정규화(
LOWER)하고, 애플리케이션에서도 한 번 더 막는다(이중 방어). 미분류 항목은 경고 로그로 남긴다 - 교훈: 같은 값이 DB와 Java에서 서로 다르게 해석된다. 값이 계층을 넘어갈 때마다 "여기서는 어떻게 비교하는가"를 명시해야 한다
1. 상황 설정
채널별 월간 주문 건수를 집계한 테이블을 읽어 월간 리포트를 만든다. 값은 대부분 소문자인데, 외부 제휴 피드가 넣은 2건만 대문자다.
CREATE TABLE channel_stat (
id INT AUTO_INCREMENT PRIMARY KEY,
month_key CHAR(6) NOT NULL,
channel VARCHAR(20) NOT NULL, -- 기본 collation: utf8mb4_general_ci
order_cnt INT NOT NULL
);
INSERT INTO channel_stat (month_key, channel, order_cnt) VALUES
('202609', 'web', 50000),
('202609', 'app', 120000),
('202609', 'APP', 2); -- 제휴 피드가 대문자로 적재app과 APP은 바이트로는 서로 다른 값이다.
2. 증상: 리포트에서 app이 0건이다
리포트는 채널별 합계를 Map에 담고 이름으로 값을 찾는다.
// SELECT channel, SUM(order_cnt) FROM channel_stat WHERE month_key = ? GROUP BY channel
Map<String, Integer> byChannel = rows.stream()
.collect(Collectors.toMap(Row::channel, Row::orders));
byChannel.getOrDefault("app", 0); // 0 ← 12만 건이 있는데?표의 합(50,000)이 월 전체 합계(170,002)보다 120,002건 모자란다. 건수가 없는 게 아니라 표의 어떤 행에도 매칭되지 않는 이름 아래에 있다는 단서다. DB로 내려가 확인해 보자.
2. 원인 ①: DB는 대소문자를 구분하지 않는다
SELECT channel, SUM(order_cnt) AS total FROM channel_stat GROUP BY channel;+---------+--------+
| channel | total |
+---------+--------+
| app | 120002 |
| web | 50000 |
+---------+--------+app(12만)과 APP(2)이 한 그룹으로 합쳐져 120,002가 됐다. MariaDB의 기본 collation(utf8mb4_general_ci)은 대소문자를 구분하지 않기 때문이다. (MySQL 8의 기본 utf8mb4_0900_ai_ci도 대소문자를 구분하지 않는다.) WHERE도 마찬가지다.
SELECT channel, order_cnt FROM channel_stat WHERE channel = 'app';+---------+-----------+
| channel | order_cnt |
+---------+-----------+
| app | 120000 |
| APP | 2 |
+---------+-----------+그런데 이 결과에는 더 위험한 성질이 숨어 있다. 합쳐진 그룹의 대표 라벨을 DB가 어떻게 고르는가다. 같은 데이터를 삽입 순서만 바꿔서 넣어 보자.
CREATE TABLE channel_stat2 LIKE channel_stat;
INSERT INTO channel_stat2 (month_key, channel, order_cnt) VALUES
('202609', 'APP', 2), -- 대문자 행이 먼저
('202609', 'web', 50000),
('202609', 'app', 120000);
SELECT channel, SUM(order_cnt) AS total FROM channel_stat2 GROUP BY channel;+---------+--------+
| channel | total |
+---------+--------+
| APP | 120002 | ← 라벨이 'APP' 으로 바뀌었다
| web | 50000 |
+---------+--------+숫자(120,002)는 같지만 라벨이 app에서 APP으로 바뀌었다. 어떤 라벨이 나올지는 삽입 순서, 실행 계획 같은 조건에 따라 달라질 수 있고, 코드가 거기에 의존해서는 안 된다.
3. 원인 ②: Java의 HashMap은 대소문자를 구분한다
이제 DB가 APP이라는 라벨로 120,002를 돌려줬다고 하자.
List<Row> rows = List.of(new Row("APP", 120_002), new Row("web", 50_000));
Map<String, Integer> raw = rows.stream().collect(Collectors.toMap(Row::channel, Row::orders));
System.out.println(raw.getOrDefault("app", 0)); // 0
System.out.println(raw.getOrDefault("APP", 0)); // 1200020
120002"app"과 "APP"은 String.equals 기준으로 다른 키다. 리포트는 "app"을 찾고, DB는 "APP"을 줬으니 매칭에 실패하고 기본값 0이 표시된다.
4. 세 레이어의 암묵적 가정
그림 2. 각 레이어는 자기 기준으로는 정상 동작한다. 가정이 서로 달랐을 뿐이다.
- 입력: 외부 피드가 대문자를 적재했다. 저장 시점에 정규화가 없었다.
- DB: collation 규칙에 따라 두 값을 같다고 보고 합산했다. 대표 라벨은 보장되지 않는다.
- 애플리케이션:
equals규칙에 따라 두 값을 다르다고 보고 매칭에 실패했다.
어느 레이어도 버그가 아니다. 레이어마다 "같다"의 정의가 다른데, 아무도 그 사실을 명시하지 않았다는 것이 문제의 본질이다. 그래서 단 2건의 데이터가 12만 건짜리 표시 오류로 증폭된다.
5. 해결 선택지 비교
| 방법 | 언제 적합한가 | 주의할 점 |
|---|---|---|
A. 조회 정규화 (LOWER(col) + Java toLowerCase) |
값이 의미상 대소문자와 무관하고, 데이터를 건드리고 싶지 않을 때 | 컬럼에 함수를 씌우면 일반적으로 인덱스를 쓰지 못한다 |
B. BINARY 그룹핑 |
대소문자가 의미 있는 구분자일 때 (예: app과 APP이 서로 다른 출처) |
ONLY_FULL_GROUP_BY에서 MAX() 필요 (아래) |
| C. 저장 시점 정규화 (INSERT 전 변환 + 기존 데이터 백필) | 근본 해결을 원하고, 입력 경로를 우리가 통제할 수 있을 때 | 기존 데이터 백필, 원천 데이터의 소유권, 배포 범위가 부담이다 |
D. 컬럼 collation을 *_bin으로 변경 |
해당 컬럼의 모든 비교가 대소문자를 구분해야 할 때 | 정렬·유니크·조인 등 모든 비교에 영향을 주므로 영향 분석이 필수다 |
E. 입력 제약 (CHECK, 코드 테이블) |
잘못된 값이 아예 못 들어오게 막고 싶을 때 | CHECK 작성에 함정이 있다 (아래) |
A. 조회 정규화
SELECT LOWER(channel) AS channel, SUM(order_cnt) AS total
FROM channel_stat GROUP BY LOWER(channel);+---------+--------+
| channel | total |
+---------+--------+
| app | 120002 |
| web | 50000 |
+---------+--------+라벨이 항상 소문자로 고정되니 Java에서도 "app"으로 찾을 수 있다.
B. BINARY 그룹핑과 ONLY_FULL_GROUP_BY
대소문자를 구분해서 그룹핑하려면 GROUP BY BINARY channel을 쓸 수 있다. 그런데 ONLY_FULL_GROUP_BY가 켜져 있으면 이렇게 쓸 수 없다. (MySQL 5.7+는 기본으로 켜져 있고, MariaDB는 기본으로 꺼져 있다. 아래는 켠 상태에서 실행했다.)
SELECT channel, SUM(order_cnt) FROM channel_stat GROUP BY BINARY channel;
-- ERROR 1055 (42000): 'demo_collation.channel_stat.channel' isn't in GROUP BY그룹 키는 BINARY channel인데 SELECT에는 channel이 있어서 서로 다른 식으로 취급되기 때문이다. MAX()로 감싸 "그룹 안에서 값은 모두 같다"고 알려주면 된다.
SELECT MAX(channel) AS channel, SUM(order_cnt) AS total
FROM channel_stat GROUP BY BINARY channel;+---------+--------+
| channel | total |
+---------+--------+
| APP | 2 |
| app | 120000 |
| web | 50000 |
+---------+--------+이제 app과 APP이 분리됐다. 이 방식이 맞는지는 두 값이 정말 다른 의미인지에 달려 있다. 같은 채널의 표기 차이일 뿐이라면 A가 맞다.
D. COLLATE를 쓸 때는 컬럼의 문자셋을 확인한다
COLLATE는 컬럼의 문자셋과 짝이 맞아야 한다. 컬럼이 utf8mb3인데 흔히 보이는 utf8mb4_bin을 쓰면 이렇게 실패한다.
-- channel 컬럼이 CHARACTER SET utf8mb3 인 경우
SELECT channel, COUNT(*) FROM channel_stat3 GROUP BY channel COLLATE utf8mb4_bin;
-- ERROR 1253 (42000): COLLATION 'utf8mb4_bin' is not valid for CHARACTER SET 'utf8mb3'컬럼의 문자셋에 의존하지 않으려면 BINARY를 쓰는 편이 안전하다.
E. 소문자만 허용하는 CHECK의 함정
"소문자만 들어오게 막자"며 이렇게 쓰기 쉽다.
CREATE TABLE chk1 (channel VARCHAR(20) NOT NULL, CHECK (channel = LOWER(channel)));
INSERT INTO chk1 VALUES ('app');
INSERT INTO chk1 VALUES ('APP'); -- 통과해 버린다!기본 collation에서는 'APP' = LOWER('APP')도 참이라서 제약이 아무것도 막지 못한다. 바이트로 비교해야 한다.
CREATE TABLE chk2 (channel VARCHAR(20) NOT NULL, CHECK (BINARY channel = LOWER(channel)));
INSERT INTO chk2 VALUES ('app');
INSERT INTO chk2 VALUES ('APP');
-- ERROR 4025 (23000): CONSTRAINT `CONSTRAINT_1` failed for `demo_collation`.`chk2`7. 방어 코드: 한쪽이 틀어져도 리포트가 죽지 않게
DB 쿼리를 LOWER로 고쳤더라도, 누군가 쿼리를 되돌리거나 다른 경로에서 대소문자가 섞인 데이터가 들어올 수 있다. 애플리케이션에도 한 겹 더 방어를 둔다.
// 키를 소문자로 정규화하고, 같은 키가 충돌하면 합산한다
Map<String, Integer> safe = rows.stream().collect(Collectors.toMap(
r -> r.channel().toLowerCase(Locale.ROOT),
Row::orders,
Integer::sum));
System.out.println(safe.getOrDefault("app", 0)); // 120002병합 함수(Integer::sum)가 없으면 키가 충돌하는 순간 예외가 나서 리포트 발송 자체가 죽는다.
List<Row> split = List.of(new Row("app", 120_000), new Row("APP", 2));
split.stream().collect(Collectors.toMap(r -> r.channel().toLowerCase(Locale.ROOT), Row::orders));
// IllegalStateException: Duplicate key app (attempted merging values 120000 and 2)표에 없는 값은 조용히 버리지 말고 경고를 남긴다. 그리고 표시한 합이 전체 합과 같은지 검증한다.
Set<String> known = Set.of("web", "app");
int shown = 0; // total: 전체 합계 (별도 쿼리로 조회)
for (var e : safe.entrySet()) {
if (known.contains(e.getKey())) {
shown += e.getValue();
} else {
log.warn("excluded unclassified channel: key={}, count={}", e.getKey(), e.getValue());
}
}
if (shown != total) {
log.warn("report mismatch: shown={}, total={}", shown, total); // 2편의 증상이 바로 드러나는 지점
}회귀 테스트도 하나 둔다. 대소문자가 다른 입력이 같은 행으로 합쳐지는지만 확인해도 같은 버그의 재발을 막는다.
@Test
void mixedCaseKeys_areMergedIntoOneRow() {
var rows = List.of(new Row("app", 120_000), new Row("APP", 2));
assertEquals(120_002, normalize(rows).get("app"));
}'Backend > DB | SQL' 카테고리의 다른 글
| 자릿수가 다른 문자열 비교의 함정 (0) | 2026.10.12 |
|---|
