상하 관계와 문자 규칙을 같이 알아야 했다

과정 분류를 조회하는데 하위 항목이 안 나왔다. 구조는 맞게 짰다.

Table of contents

증상 — 구조는 맞는데 0건이었다

표 구조는 알고 있었다.

CREATE TABLE category (
  code        varchar(20) NOT NULL,
  parent_code varchar(20),
  name        nvarchar(100),
  PRIMARY KEY (code)
);

parent_code 로 이어지는 구조라 그대로 조회했다.

SELECT * FROM category WHERE parent_code = 'IT';

데이터는 있는데 결과가 0건이었다.

구조가 맞는데 안 나온다면 조건에 넣은 값이 저장된 값과 다르다는 뜻이다. 눈으로는 같아 보이므로 눈이 아니라 바이트로 봐야 하는 상황이었다.

원인 — 값의 실제 바이트

값을 바이트로 봤다.

SELECT code, LEN(code), DATALENGTH(code) FROM category WHERE code LIKE 'IT%';

code  LEN  DATALENGTH
IT      2         20

LEN 이 2인데 DATALENGTH 가 20이라 뒤가 공백으로 채워져 있었다.

SELECT * FROM category WHERE parent_code = 'IT                  ';

공백까지 맞추니 나왔다.

고정 길이 컬럼이라 남는 자리가 자동으로 채워진 것인데 저장할 때 일어나는 일이라 넣는 쪽에서는 보이지 않는다.

제약 — DB마다 비교가 다르다

이 시스템은 두 종류의 DB 를 쓰고 있었다.

한쪽   고정 길이 비교에서 뒤 공백을 무시한다
다른쪽 무시하지 않는다

같은 쿼리가 DB 에 따라 다르게 동작했다.

WHERE RTRIM(parent_code) = 'IT'

RTRIM 으로 떼고 비교하면 되지만 그러면 인덱스를 못 쓴다.

컬럼을 가변 길이로 바꾸는 것이 근본이라 옮기는 김에 같이 했는데 문법만 맞추고 이런 규칙 차이를 안 보면 조용히 어긋난다.

주의 — 문자 종류와 바인딩

이름 컬럼은 유니코드였다.

SELECT * FROM category WHERE name = '정보기술';

이렇게 하면 안 나온다.

SELECT * FROM category WHERE name = N'정보기술';

N 을 붙여야 유니코드 문자열로 보고 안 붙이면 다른 문자 집합으로 변환돼 비교가 실패한다.

코드에서 어떤 형으로 나가는지도 봐야 했다.

$stmt = $pdo->prepare("SELECT * FROM category WHERE name = ?");
$stmt->execute([$name]);

PARAM_STR 로 나가면 기본이 유니코드가 아니었다.

$pdo = new PDO($dsn, $user, $pass, [
    PDO::SQLSRV_ATTR_ENCODING => PDO::SQLSRV_ENCODING_UTF8,
]);

SQLSRV_ATTR_ENCODING 을 지정해 나가는 형을 맞췄다.

쿼리만 보고 있으면 이 자리가 안 보인다. 모르면 데이터가 있는데 안 나온다로 끝난다.

검증 — 안 나올 때 볼 순서

값이 안 나올 때 볼 순서를 적었다.

1. 데이터가 실제로 있는가       SELECT COUNT(*) 조건 없이
2. 조건 없이 몇 건인가          LIKE '%값%' 로 넓게
3. 값의 실제 바이트는           DATALENGTH, HEX
4. 앞뒤 공백이 있는가            LEN vs DATALENGTH
5. 문자 종류가 맞는가            N 접두 유무로 비교
6. 코드에서 어떻게 나가는가      쿼리 로그로 확인

LEN 과 DATALENGTH 를 나란히 보는 넷째가 이번 원인을 잡았다.

순서가 있으면 매번 처음부터 헤매지 않고 이대로 보니 그다음부터는 같은 유형을 빨리 찾았다.

조치 — 규칙 정리와 계층 조회

옮기는 김에 규칙도 정했다.

문자 컬럼은 가변 길이를 쓴다
코드성 값은 영문 대문자와 숫자만 (공백·특수문자 금지)
이름은 유니코드
비교는 항상 같은 문자 종류로

옛 데이터도 함께 정리했는데 코드 값에 공백이 들어간 것이 열두 건이나 있었다.

값을 제대로 찾게 된 뒤에도 조회 자체가 느렸다.

function children($code) {
    $rows = $db->query("SELECT * FROM category WHERE parent_code = ?", [$code]);
    foreach ($rows as &$r) { $r['children'] = children($r['code']); }
    return $rows;
}

children 이 한 단계씩 내려가서 깊이가 4단계면 조회가 수십 번 나간다.

$all = $db->query("SELECT * FROM category");
$byParent = [];
foreach ($all as $r) { $byParent[$r['parent_code']][] = $r; }

전체를 한 번에 가져와 byParent 로 묶고 메모리에서 이었다.

분류가 300건이라 전부 가져와도 부담이 없었고 건수가 적은 계층 자료는 이쪽이 훨씬 빨랐다.

정리

• 구조를 알아도 문자 규칙을 모르면 조회가 안 된다
• 눈으로 같아 보이면 값의 실제 바이트를 본다
• 길이와 바이트가 다르면 채워진 것이 있다
• 고정 길이 컬럼은 뒤가 공백으로 채워진다
• 채움은 저장할 때 일어나 넣는 쪽에서는 안 보인다
• DB 마다 뒤 공백 비교 방식이 다르다
• 한쪽에서 돌던 쿼리가 옮기면 안 돌 수 있다
• 유니코드는 비교할 때 표시가 필요할 수 있다
• 코드에서 어떤 형으로 나가는지 보고 드라이버를 설정한다
• 안 나올 때 볼 순서를 정해 둔다
• 옮기는 김에 컬럼 종류와 값 규칙을 정리한다