MySQL JSON 필드 업데이트 하는 방법
2022년 12월 24일
MySQL
📕 목차
들어가며
JSON 타입의 컬럼을 사용해서 데이터를 저장하는 경우가 종종 있는데, JSON 컬럼을 업데이트 하는 방법에 대한 기록을 남기기위해서 이 글을 작성합니다.
데이터 예시
커피숍들의 정보를 저장하는 테이블이 있다고 하나 가정해보겠다. 메뉴 목록을 저장하고 있는 컬럼은 JSON 타입으로 되어있다.
+----------------------------------------------------------------------------------+
| menu |
+----------------------------------------------------------------------------------+
| '{"아메리카노": {"가격": 4500}, "카페라떼": {"가격": 5000}, "바닐라라떼": {"가격": 5500}' |
| '{"아메리카노": {"가격": 4200}, "카페라떼": {"가격": 4800}, "바닐라라떼": {"가격": 5200}' |
| '{"아메리카노": {"가격": 4300}, "카페라떼": {"가격": 4900}, "바닐라라떼": {"가격": 5300}' |
+----------------------------------------------------------------------------------+새로운 메뉴 추가 절차
커피숍에 비엔나 커피라는 새로운 메뉴가 추가되어 테이블 백필작업을 진행하려고 한다. 절차는 다음과 같다.
- 비엔나 커피가 없는 커피숍들의 메뉴를 조회한다. 그리고, 업데이트하기 위한 레코드의 갯수를 확인해본다.
- 비엔나 커피메뉴를 추가해서 저장한다. (이때, 키가 없었기때문에 MySQL 8.0에 도입된
Partial Update가 적용되지 않고, 새롭게 다시 Document를 저장한다.)
1. 비엔나 커피가 없는 커피숍들의 메뉴를 조회한다.
대량의 레코드를 업데이트하기위해서는, 배치 사이즈를 정해서 여러 번 업데이트 작업을 수행하곤 합니다. 그래서, 업데이트 수행 전 작업 계획을 세우기 위해서 키가 존재하지 않는 레코드의 갯수를 조회해서 적절하게 배치 사이즈를 조정해봅니다.
아래의 SQL은 JSON_EXTRACT 함수를 사용해서 JSON 문서에서 비엔나커피 키가 존재하지 않는 레코드의 갯수를 조회한다.
SELECT COUNT(*)
FROM coffee_shop
WHERE JSON_EXTRACT(menu, '$."비엔나커피"') IS NULL;2. 비엔나 커피메뉴를 추가해서 저장한다.
UPDATE coffee_shop
SET menu = JSON_SET(menu, '$."비엔나커피"', "")
WHERE JSON_EXTRACT(menu, '$."비엔나커피"', '{"가격": 4300}') IS NULL
LIMIT 50000 -- MySQL에서는 Update 구문에서 LIMIT을 제공하기때문에 배치 업데이트를 편하게 할 수 있다.JSON 컬럼 부분 업데이트 (Partial Update)
위에서 잠깐 언급한 Partial Update에 대해서 알아보겠다. MySQL 8.0버전에 들어오면서 기존에 전체 JSON을 새롭게 다시 쓰는방식을 개선하여 부분적으로 업데이트하는 기능이 추가되었다. Partial Update를 사용하기위한 문법이 제공되는 것은 아니고, 작성한 SQL문이 조건에 맞다면 자동으로 적용된다. Partial Update는 옵티마이저에 의해서 수행될 수 있다.
부분 업데이트와 전체 업데이트가 수행되는 여부차이는 새롭게 추가하려는 공간이 남아있는지 여부에 따라 달려있다. 새로운 키-값을 추가하는 것은 새로운 공간을 차지하기 때문에 새롭게 써야한다. 그리고, 이미 키가 있지만 기존보다 값이 더 큰 경우 새로운 공간이 필요하기때문에 부분 업데이트가 적용되지 않는다. 반대로, 바이트 수가 큰 값에서 작은 값으로 변경하는 것은 공간이 충분하기 때문에 부분 업데이트가 적용된다.
부분 업데이트를 예시) {"name": "바닐라라떼"} 를 {"name": "녹차라떼"}와 같이 5글자를 저장하던 것에서 4글자로 변경하는 것은 공간이 충분하기때문에 Partial Update를 할 수 있다.
전체 업데이트를 예시) {"name": "녹차라떼"} 를 {"name": "토피넛라떼"}와 같이 4글자를 저장하던 것에서 5글자로 변경하는 것은 공간이 부족하기때문에 Partial Update를 할 수 없다. 그래서 새로운 문서로 다시 저장한다.
간략하게 JSON 컬럼 부분 업데이트에 대한 메커니즘에 대해서 알아봤다. 자세한 내용은 MySQL의 블로그 문서 Partial Update of JSON values를 참고하자.