과정 분류를 조회하는데 하위 항목이 안 나왔다. 구조는 맞게 짰다.
Table of contents
Open 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 20LEN 이 2인데 DATALENGTH 가 20이라 뒤가 공백으로 채워져 있었다. SQL Server 공식 문서대로 DATALENGTH 는 표현에 쓰인 바이트 수를, LEN 은 문자 수를 돌려주고, char(n) 은 고정 길이라 n 바이트를 항상 차지한다.
SELECT * FROM category WHERE parent_code = 'IT ';공백까지 맞추니 나왔다.
고정 길이 컬럼이라 남는 자리가 자동으로 채워진 것인데 저장할 때 일어나는 일이라 넣는 쪽에서는 보이지 않는다.
제약 — DB마다 비교가 다르다
이 시스템은 두 종류의 DB 를 쓰고 있었다.
한쪽 고정 길이 비교에서 뒤 공백을 무시한다
다른쪽 무시하지 않는다같은 쿼리가 DB 에 따라 다르게 동작했다.
WHERE RTRIM(parent_code) = 'IT'RTRIM 으로 떼고 비교하는 것 대신 컬럼을 가변 길이로 바꾸는 쪽을 골랐다. 이유는 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마다 뒤 공백 비교 방식이 다르다- 한쪽에서 돌던 쿼리가 옮기면 안 돌 수 있다
- 유니코드는 비교할 때 표시가 필요할 수 있다
- 코드에서 어떤 형으로 나가는지 보고 드라이버를 설정한다
- 안 나올 때 볼 순서를 정해 둔다
- 옮기는 김에 컬럼 종류와 값 규칙을 정리한다