키 순서가 다른 JSON 을 MariaDB 에서 같은 값으로 비교하고 중복 막기 — JSON_EQUALS 와 JSON_NORMALIZE 실측

상품 옵션을 JSON 컬럼에 저장하는 테이블이 있다. 한 상품에 같은 옵션 조합이 두 번 들어가면 안 되니, 저장하기 전에 같은 값이 이미 있는지 찾는다.

$attrs = json_encode(['size' => 'M', 'color' => 'red']);
$stmt = $pdo->prepare('SELECT id FROM product_option WHERE product_id = ? AND attrs = ?');
$stmt->execute([$productId, $attrs]);

한동안은 잘 돈다. 그러다 관리자 화면에서 옵션 순서를 바꿔 저장하는 기능이 들어오거나, 다른 경로(자바스크립트의 JSON.stringify, 엑셀 업로드)로 들어온 행이 섞이면 중복이 생긴다. DB 에 {"color":"red","size":"M"}가 있는데 찾는 값이 {"size":"M","color":"red"}이면, 사람이 보기에는 같은 옵션이어도 =는 다르다고 답한다.

MariaDB 의 JSON은 별도 타입이 아니라 LONGTEXT의 별칭이다. 테이블을 만들고 SHOW CREATE TABLE을 보면 그대로 드러난다.

CREATE TABLE product_option (
  id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL,
  attrs JSON NOT NULL
);

-- SHOW CREATE TABLE 결과 (MariaDB 11.4)
`attrs` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL
        CHECK (json_valid(`attrs`))

유효성 검사가 붙은 문자열 컬럼이다. 그래서 =는 글자 단위 비교가 되고, 키 순서·공백·숫자 표기가 다르면 다른 값이다. 이 문제를 SQL 안에서 푸는 함수가 MariaDB 10.7 에서 들어온 JSON_EQUALS()와 JSON_NORMALIZE()다. 이미 4년 된 함수인데 잘 안 쓰인다. 이 글은 MariaDB 11.4.12 와 11.8.9 를 도커로 띄워 두 함수가 실제로 무엇을 같다고 보는지, 중복 방지에 어떻게 쓰는지 잰 결과다. 두 버전 결과는 경고 하나를 빼고 같았다.

JSON_EQUALS 가 같다고 보는 것과 다르다고 보는 것

SELECT JSON_EQUALS('{"a":1,"b":2}', '{"b":2,"a":1}');   -- 1
SELECT '{"a":1,"b":2}' = '{"b":2,"a":1}';               -- 0

키 순서와 공백은 무시한다. 여기까지는 소개글대로다. 경계를 더 재 봤다.

숫자는 값으로 비교한다. 1, 1.0, 1e0이 모두 같다. 그러면서도 9007199254740993과 9007199254740992를 구별했다. 자바스크립트처럼 배정밀도 실수로 바꿔서 비교했다면 이 둘은 같게 나왔을 것이다. 0.1000000000000000000001과 0.1도 다르게 나왔다.

깨진 JSON 은 NULL이다. 11.8 에서는 Warning 4037 Unexpected end of JSON text in argument 1 to function 'json_equals' 경고가 남고, 11.4 에서는 같은 쿼리에 SHOW WARNINGS가 비어 있었다. WHERE JSON_EQUALS(...)에서는 NULL이 거짓처럼 걸러지므로, 깨진 행은 조용히 결과에서 빠진다.

한글이 \uXXXX 로 저장돼 있으면 다른 값이 된다

표에서 가장 실무적인 줄은 "\uAC00" 대 "가"다. JSON 명세상 둘은 같은 문자열이지만 JSON_EQUALS는 0 을 돌려준다. JSON_NORMALIZE로 보면 이유가 보인다. 이스케이프를 풀지 않고 그대로 둔다.

SELECT JSON_NORMALIZE('{"b":2, "a":[3,1], "c":1.50, "d":"\uAC00"}');
-- {"a":[3.0E0,1.0E0],"b":2.0E0,"c":1.5E0,"d":"\uAC00"}

SELECT JSON_NORMALIZE('"가"');
-- "가"

키는 정렬되고, 공백은 빠지고, 숫자는 1.5E0 같은 지수 표기로 통일된다. 배열 순서는 그대로다. 그런데 \uAC00은 그대로 남는다.

이게 PHP 와 만나면 문제가 된다. json_encode는 기본값으로 한글을 \uXXXX로 바꾼다.

echo json_encode(['name' => '가나', 'size' => 'M']);
// {"name":"\uac00\ub098","size":"M"}

echo json_encode(['name' => '가나', 'size' => 'M'], JSON_UNESCAPED_UNICODE);
// {"name":"가나","size":"M"}

한 경로는 기본값으로, 다른 경로는 JSON_UNESCAPED_UNICODE로 저장했다면 같은 옵션이 서로 다른 값으로 들어간다. 키 순서는 JSON_EQUALS가 흡수해 주지만 이스케이프 차이는 흡수하지 못한다. 아래 UNIQUE 실측에서도 {"name":"\uac00\ub098"}과 {"name":"가나"}가 둘 다 들어갔다.

해법은 SQL 쪽이 아니라 쓰는 쪽에 있다. JSON 을 만드는 모든 경로에서 JSON_UNESCAPED_UNICODE를 쓰도록 맞춘다. 자바스크립트의 JSON.stringify와 MariaDB 의 JSON_OBJECT()는 한글을 그대로 내보내므로, PHP 쪽을 그쪽에 맞추는 편이 쉽다.

MySQL 은 = 만으로 같다고 본다

MySQL 에서 넘어온 사람은 여기서 헷갈린다. MySQL 의 JSON은 진짜 타입이라, 저장할 때 바이너리 형식으로 바꾸고 =도 의미로 비교한다. MySQL 8.4.11 에서 같은 실험을 했다.

MySQL 은 이스케이프도 저장 시점에 풀어 버려서 {"name": "가나"}로 저장된다. 대신 MySQL 에도 함정이 하나 있다. 비교 상대가 CAST(? AS JSON)이 아니라 그냥 문자열이면 JSON 문자열 값으로 취급되어 0 이 나온다. 양쪽 다 "JSON 컬럼이니까 =면 되겠지"가 안 통하는 것은 같은데, 안 통하는 이유가 다르다.

중복 막기: JSON_NORMALIZE 로 만든 생성 컬럼에 UNIQUE

조회할 때 JSON_EQUALS로 비교하는 것만으로는 동시에 들어오는 두 요청을 못 막는다. 제약은 DB 에 둬야 한다. 정규화한 값을 생성 컬럼으로 두고 거기에 UNIQUE 를 건다.

ALTER TABLE product_option
  ADD attrs_norm LONGTEXT AS (JSON_NORMALIZE(attrs)) VIRTUAL,
  ADD UNIQUE KEY uq_attrs (product_id, attrs_norm);

LONGTEXT에 UNIQUE 를 걸면 인덱스 길이 제한에 걸릴 것 같지만, MariaDB 11.4 는 오류 없이 받아들였다. SHOW CREATE TABLE을 보면 이유가 나온다.

UNIQUE KEY `uq_attrs` (`product_id`,`attrs_norm`) USING HASH

MariaDB 10.4 부터 있는 긴 값 UNIQUE 다. 값 전체의 해시를 숨은 컬럼에 두고 그걸로 중복을 검사한다. 넣어 보면 막힌다.

INSERT INTO product_option (product_id, attrs) VALUES (1, '{"color":"red","size":"M"}');  -- 성공
INSERT INTO product_option (product_id, attrs) VALUES (1, '{"size":"M","color":"red"}');
-- ERROR 1062 (23000): Duplicate entry '1-{"color":"red","size":"M"}' for key 'uq_attrs'
INSERT INTO product_option (product_id, attrs) VALUES (1, '{ "size" : "M", "color" : "red" }');
-- ERROR 1062 (23000)

오류 메시지에 원래 입력이 아니라 정규화된 값이 찍힌다는 점도 알아 두면 로그를 읽을 때 덜 헷갈린다. 다른 경우도 넣어 봤다.

마지막 두 줄이 앞 절의 이스케이프 문제다. JSON_NORMALIZE를 믿고 UNIQUE 를 걸어도, 이스케이프 표기가 섞여 들어오면 제약을 통과한다. 쓰는 쪽의 인코딩 옵션을 맞추는 일은 UNIQUE 로 대신할 수 없다.

태그 목록처럼 순서가 의미 없는 배열은 JSON_NORMALIZE가 정렬해 주지 않는다. 저장하기 전에 애플리케이션에서 sort()해서 넣어야 한다.

USING HASH 인덱스는 조회에 쓰이지 않는다

중복은 막지만, 이 인덱스로 같은 옵션을 찾을 수 있는지는 별개다. 50개 상품에 옵션 5000행을 넣고 EXPLAIN을 봤다.

EXPLAIN SELECT id FROM product_option
WHERE product_id = 7 AND attrs_norm = JSON_NORMALIZE('{"size":"S7","color":"red"}');
-- type: ALL  possible_keys: uq_attrs  key: NULL  rows: 5135

EXPLAIN SELECT id FROM product_option
WHERE product_id = 7 AND JSON_EQUALS(attrs, '{"size":"S7","color":"red"}');
-- type: ALL  key: NULL  rows: 5135

둘 다 전체 스캔이다. USING HASH UNIQUE 는 후보에는 오르지만 실제로 쓰이지 않았다. JSON_EQUALS는 함수라서 처음부터 인덱스를 탈 수 없다. 저장 전에 같은 값을 찾는 조회가 잦다면, 정규화 값의 해시를 저장 컬럼으로 두고 일반 UNIQUE 를 거는 편이 낫다.

ALTER TABLE product_option
  ADD attrs_hash BINARY(32) AS (UNHEX(SHA2(JSON_NORMALIZE(attrs), 256))) STORED,
  ADD UNIQUE KEY uq_attrs_hash (product_id, attrs_hash);

EXPLAIN SELECT id FROM product_option
WHERE product_id = 7
  AND attrs_hash = UNHEX(SHA2(JSON_NORMALIZE('{"color":"red", "size":"S7"}'), 256));
-- type: const  key: uq_attrs_hash  rows: 1  Extra: Using index

const 조회로 바뀌었고, 키 순서와 공백이 다른 값으로 찾아도 같은 행이 나왔다. 32바이트 고정 길이라 인덱스 크기도 작다. 접두 인덱스(attrs_norm(100))도 ref로 타긴 했지만, 정규화된 JSON 앞 100자가 같은 행이 많으면 효과가 떨어진다. 해시 쪽이 일관된다.

정리

• MariaDB 의 JSON 컬럼은 LONGTEXT라서 =가 글자 비교다. 키 순서·공백만 달라도 다른 값이 된다.
• JSON_EQUALS()는 키 순서·공백·숫자 표기(1/1.0/1e0)를 무시한다. 배열 순서, 문자열과 숫자의 차이, 대소문자는 구별한다. 큰 정수도 정확히 비교한다.
• 같은 한글이라도 \uAC00처럼 이스케이프된 값과 그냥 쓴 값을 다르다고 본다. PHP json_encode의 기본값이 이스케이프이니 JSON_UNESCAPED_UNICODE로 통일할 것.
• 중복 방지는 JSON_NORMALIZE 생성 컬럼 + UNIQUE 로 된다. LONGTEXT에는 자동으로 USING HASH가 붙는데, 이 인덱스는 조회에 쓰이지 않았다.
• 조회도 빨라야 하면 UNHEX(SHA2(JSON_NORMALIZE(attrs), 256)) 저장 컬럼에 UNIQUE 를 건다.
• MySQL 8.4 는 JSON 이 진짜 타입이라 =가 의미 비교이고, JSON_EQUALS 함수는 없다.

실측 환경: 도커 mariadb:11.4(11.4.12), mariadb:11.8(11.8.9), mysql:8.4(8.4.11), PHP 8.5.4.