MySQL에서 한 셀에 여러 요소 세트 저장하는 방법 – JSON 데이터 타입 활용하기

MySQL에서 한 셀에 요소 세트 추가하기

관계형 데이터베이스에서 하나의 셀(컬럼)에 여러 개의 요소를 저장해야 하는 경우가 종종 있습니다. 이럴 때 JSON 데이터 타입을 활용하면 하나의 셀에 배열 형태로 여러 값을 손쉽게 저장할 수 있습니다. MySQL 5.7 버전부터 JSON 타입을 기본적으로 지원하며, 잘못된 형식의 JSON 문자열이 입력되면 자동으로 오류를 반환해 데이터 유효성까지 검증해 줍니다.

먼저 예제에 사용할 테이블을 생성해 보겠습니다.

mysql> create table DemoTable1828
     (
     EmployeeId int,
     EmployeeRecords JSON
     );
Query OK, 0 rows affected (0.00 sec)

위 테이블은 직원 ID를 저장하는 EmployeeId 컬럼과, 직원 정보를 JSON 배열 형태로 담는 EmployeeRecords 컬럼으로 구성되어 있습니다.

데이터 삽입하기

INSERT 명령을 사용하여 JSON 형식의 레코드를 테이블에 삽입합니다. 한 사람의 정보만 넣는 대신, 이름과 나이를 담은 객체 여러 개를 배열로 묶어 한 번에 저장할 수 있습니다.

mysql> insert into DemoTable1828 values(1,'[{"EmployeeName":"Chris","EmployeeAge":29},{"EmployeeName":"David","EmployeeAge":27}]');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1828 values(2,'[{"EmployeeName":"John","EmployeeAge":36},{"EmployeeName":"Mike","EmployeeAge":32}]');
Query OK, 1 row affected (0.00 sec)

저장된 데이터 조회하기

SELECT 문을 사용하여 테이블의 모든 레코드를 확인해 보겠습니다.

mysql> select * from DemoTable1828;

위 쿼리를 실행하면 다음과 같은 결과가 출력됩니다.

+------------+----------------------------------------------------------------------------------------------+
| EmployeeId | EmployeeRecords                                                                            |
+------------+----------------------------------------------------------------------------------------------+
|          1 | [{"EmployeeAge": 29, "EmployeeName": "Chris"}, {"EmployeeAge": 27, "EmployeeName": "David"}] |
|          2 | [{"EmployeeAge": 36, "EmployeeName": "John"}, {"EmployeeAge": 32, "EmployeeName": "Mike"}]   |
+------------+----------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

JSON 내부 값 추출하기

저장된 JSON 데이터에서 특정 값만 필요한 경우에는 JSON_EXTRACT() 함수나 -> 연산자를 사용하면 됩니다. 예를 들어 첫 번째 직원의 이름만 가져오려면 다음과 같이 작성할 수 있습니다.

mysql> select EmployeeRecords->'$[0].EmployeeName' from DemoTable1828 where EmployeeId = 1;
+--------------------------------------+
| EmployeeRecords->'$[0].EmployeeName' |
+--------------------------------------+
| "Chris"                                 |
+--------------------------------------+
1 row in set (0.00 sec)

이처럼 JSON 데이터 타입을 사용하면 별도의 자식 테이블을 만들지 않고도 하나의 셀에 여러 요소를 유연하게 저장하고 관리할 수 있습니다. 다만 검색 조건이 복잡해질 가능성이 있다면, 정규화된 테이블 구조와 JSON 방식 중 프로젝트 특성에 맞는 선택을 하는 것이 좋습니다.