[MySQL] JSON 타입 사용해서 JSON Array 다루기

반응형

MySQL에 JSON 타입이 지원되기 시작한 건 version 5.7.8 in mid 2015 부터이다. MySQL 5.7 공식 가이드

이 글에선 8.0 기준으로 설명한다.

개발 공부할때 직접 쿼리를 돌려가보면서 확인하기 귀찮은 나같은 사람에게 이 글을 바친다.

 

---

JSON 타입을 적극 활용하자!

JSON 타입 컬럼에 값을 insert할때 유효성검사를 시스템에서 자동으로 해주므로, 5.7.8 이상 버전을 사용한다면 구지 varchar, text와 같은 문자열 타입 컬럼에 json 형태 string을 저장할 필요가 없다. 또한 JSON 컬럼에 저장된 데이터는 시스템 자체에서 json 객체로 인식하기 때문에 적합하고 사용이 편리하다.

 

 

JSON 타입 컬럼의 사이즈 한도

LONGBLOB, LONGTEXT 타입과 거의 유사한 저장 크기를 가진다. 다만 JSON 타입에 부과되는 오버헤드가 있기 때문에 4~10바이트는 여유를 두고 사용하여야 한다.

그리고 max_allowed_packet 사이즈보다 클 수 없다. 이 값은 mysql conf파일에 명시된 속성이며, MySQL 서버에서 이 값의 디폴트는 64MB이다.

SHOW VARIABLES LIKE 'max_allowed_packet'; -- 조회

 

아무런 설정을 변경하지 않은 경우, 67108864 Byte(64MB)로 확인됨.

변경 명령은 정상 실행은 되나 값이 변경되지 않아서 첨부하지 않았다.

 

 

 

 

JSON 타입 컬럼을 포함한 테이블 생성

사이트 게시글의 해시태그 목록을 저장하는 테이블을 만들어 보았다.

JSON Array를 저장할 컬럼을 만들고, default value로는 null이 아닌 빈 json array가 들어가도록 DDL을 작성해보았다.

create table post_hashtag(
    seq int auto_increment primary key,
    post_code varchar(20) not null,
    hashtag json not null default (json_array())
);

이때 주의사항은,

• MySQL 8.0.13 이전 버전에서는 Not null한 JSON 컬럼에 default value를 설정할 수 없다. 
  Prior to MySQL 8.0.13, a JSON column cannot have a non-NULL default value.
• Create DDL에서 default value 설정시 JSON value를 소괄호로 묶어야 정상 실행된다(소괄호가 없으면 문법 오류 발생). 이유는 아직 찾아본 적 없음.

+ Select시에는 소괄호가 없어도 문제가 없다.

select json_array() from dual; -- 정상 실행

결과

 

이제 DML을 통해 컬럼을 검증해보자. JSON 타입 핸들 함수에 대해선 뒤에서 설명하겠다.

 

컬럼 최초 삽입

-- default 값이 잘 지정되는지 확인
insert into post_hashtag (post_code) value ('A100000001');
-- json array 값 할당
insert into post_hashtag (post_code, hashtag) value ('A100000002', json_array('mysql8'));

select * from post_hashtag;

결과

Array에 요소 추가 쿼리

-- json array에 요소 추가
update post_hashtag set hashtag = json_merge_preserve(hashtag, json_array('common')) where post_code = 'A100000002';
update post_hashtag set hashtag = json_merge_preserve(hashtag, json_array('programming', 'intellij')) where post_code = 'A100000002';

select * from post_hashtag where post_code = 'A100000002';

결과

Array 요소 삭제 쿼리

update post_hashtag set hashtag = json_remove(hashtag, '$[1]') where post_code = 'A100000002';
-- hashtag에서 인덱스 1번 요소 삭제

select * from post_hashtag where post_code = 'A100000002';

결과

Array 통째로 덮어쓰기

update post_hashtag set hashtag = '["kafka"]' where post_code = 'A100000002';
-- json_array() 함수 대신 json형식에 text를 대입해도 됨. 다만 자동 유효성검사를 거치므로 json형식을 꼭 지켜야 함.

select * from post_hashtag where post_code = 'A100000002';

결과

Array 검색 쿼리

요소 순서에 상관없이 검색조건에 명시한 array의 모든 요소가 포함된 대상을 반환한다.

 

 

대상 row 없음

대상 row 없음

hastag에 'mysql5'이라는 요소를 가진 레코드가 없기에 조회 결과가 없는 모습이다.

 

 

 

---

JSON Object 타입을 저장하는 컬럼을 사용하는 포스팅은 후속편으로 추가할 예정 :)

 

 

 

 

참고:

https://dev.mysql.com/doc/refman/8.0/en/json.html#json-path-syntax

https://stackoverflow.com/questions/5688403/how-to-check-and-set-max-allowed-packet-mysql-variable

https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html#data-types-storage-reqs-json