MySQL에서 JSON 필드 사용하기

JSON 필드는 현대 데이터베이스 설계에서 점점 더 중요해지고 있습니다. 특히 MySQL에서 JSON 타입을 활용하면 복잡한 데이터 구조를 간단하게 저장하고 처리할 수 있습니다.

 

MySQL은 5.7.8 버전부터 JSON 데이터 타입을 지원하며, 이를 통해 JSON 데이터를 효율적으로 저장, 조회, 수정할 수 있습니다. 이 글에서는 MySQL에서 JSON 필드를 활용하는 방법과 JSON 필드를 지원하지 않는 경우의 해결책을 설명합니다.

 

JSON 필드가 필요한 이유

1. 복잡한 계층적 데이터 구조 저장
   JSON은 계층적 구조를 지원하여, 관계형 데이터베이스에서 표현하기 어려운 복잡한 데이터 모델을 쉽게 저장할 수 있습니다. 예를 들어, 중첩된 객체나 배열 데이터를 JSON 필드 하나로 관리할 수 있어 테이블 설계를 간소화할 수 있습니다
2. 유연한 스키마
   JSON 데이터는 스키마가 고정되어 있지 않으므로, 애플리케이션 요구사항에 따라 데이터를 동적으로 추가하거나 수정할 수 있습니다. 이는 정형화된 관계형 테이블 구조보다 유연하게 데이터를 관리할 수 있다는 장점이 있습니다
3. 추가적인 테이블 관리 불필요
   관계형 데이터베이스에서 데이터를 정규화하면 여러 테이블을 생성하고 이를 연결해야 합니다. 그러나 JSON 필드를 사용하면 하나의 컬럼에 다양한 정보를 저장할 수 있어 추가적인 테이블 관리와 JOIN 작업이 필요하지 않습니다
4. 웹 및 모바일 애플리케이션과의 호환성
   JSON은 클라이언트-서버 간 데이터를 주고받는 데 널리 사용되는 형식으로, 웹 및 모바일 애플리케이션과의 통신에 최적화되어 있습니다. MySQL에서 JSON 필드를 사용하면 이러한 데이터를 직접 저장하고 처리할 수 있습니다
5. 데이터 분석 및 검색 기능 제공
   MySQL은 JSON_EXTRACT, JSON_CONTAINS 등의 내장 함수를 통해 JSON 데이터를 효율적으로 검색하고 분석할 수 있는 기능을 제공합니다. 이를 통해 복잡한 데이터에서도 특정 값을 쉽게 추출하거나 조건에 맞는 데이터를 검색할 수 있습니다

JSON 필드 지원 여부

MySQL 버전에 따른 지원

1. MySQL 5.7.8 이상:
   • JSON 데이터 타입을 기본적으로 지원합니다.
   • JSON 데이터를 바이너리 형식으로 저장하여 성능을 최적화합니다14.
   • 다양한 JSON 함수(JSON_EXTRACT, JSON_SET, JSON_ARRAY 등)를 제공하여 데이터를 조작할 수 있습니다46.
2. MySQL 5.6 이하:
   • JSON 데이터 타입을 지원하지 않습니다.
   • JSON 데이터를 처리하려면 TEXT 또는 VARCHAR 컬럼에 문자열로 저장해야 합니다8.

JSON 필드 활용 방법

테이블 생성

JSON 데이터를 저장하려면 테이블에 JSON 타입 컬럼을 정의합니다.

 

sql
CREATE TABLE example ( id INT AUTO_INCREMENT PRIMARY KEY, data JSON NOT NULL );

데이터 삽입

JSON 데이터를 삽입할 때는 유효한 JSON 형식을 사용해야 합니다.

 

sql
INSERT INTO example (data) VALUES ('{"key": "value", "array": [1, 2, 3]}');

데이터 조회

JSON_EXTRACT와 -> 연산자를 사용하여 특정 키의 값을 조회할 수 있습니다.

 

sql
SELECT data->"$.key" AS key_value FROM example;

데이터 수정

JSON_SET을 사용하여 기존 JSON 데이터의 값을 업데이트할 수 있습니다.

 

sql
UPDATE example SET data = JSON_SET(data, "$.key", "new_value") WHERE id = 1;

인덱싱

JSON 데이터의 특정 키를 검색 성능 향상을 위해 인덱싱하려면 생성된 컬럼(generated column)을 사용할 수 있습니다.

 

sql
ALTER TABLE example ADD COLUMN key_value VARCHAR(255) AS (JSON_UNQUOTE(data->"$.key")) STORED; CREATE INDEX idx_key_value ON example (key_value);

JSON 필드를 지원하지 않는 경우 해결책

MySQL 5.6 이하와 같이 JSON 필드를 지원하지 않는 환경에서는 다음과 같은 대안이 있습니다.

1. TEXT 또는 VARCHAR 사용:
   • JSON 데이터를 문자열로 저장합니다.
   • 유효성 검사를 위해 애플리케이션 레벨에서 JSON 파싱을 수행하거나 JSON_VALID 함수를 사용할 수 있습니다2.
2. MariaDB로 전환:
   • MariaDB는 MySQL과 호환되며, 10.2 버전부터 JSON 데이터를 지원합니다.
   • 단, MariaDB는 JSON 데이터를 LONGTEXT로 처리하므로 성능 차이가 있을 수 있습니다29.
3. NoSQL 데이터베이스 사용:
   • MongoDB와 같은 NoSQL DB를 사용하면 더 나은 성능과 유연성을 제공받을 수 있습니다.
   • 특히 복잡한 계층적 데이터 구조를 다룰 때 적합합니다.

활용 시 주의사항

• 인덱싱 제한: MySQL의 JSON 필드는 직접 인덱싱이 불가능하므로 필요한 경우 생성된 컬럼을 활용해야 합니다5.
• 데이터 크기 제한: JSON 데이터는 max_allowed_packet 크기 제한을 받습니다7.
• 복잡한 쿼리 성능: 복잡한 JSON 경로를 사용하는 쿼리는 성능 저하를 초래할 수 있으므로 주의가 필요합니다1.