MySQL에서 배열 데이터를 다루는 방법

배열이란 무엇인가?

배열이란 같은 종류의 여러 개의 데이터를 하나의 변수에 저장할 수 있는 자료구조입니다. 예를 들어, 사과, 바나나, 오렌지 등과 같은 과일들을 fruits라는 변수에 저장할 수 있습니다.

MySQL에서 배열 데이터를 저장하는 방법

MySQL에는 배열 타입의 컬럼을 만들 수 있는 기능이 없습니다. 그러나 MySQL 5.7.8 버전부터 JSON 타입이 추가되었으므로, JSON 타입을 이용하여 배열 데이터를 저장할 수 있습니다. JSON 타입은 JSON 배열과 객체를 저장할 수 있는 타입입니다.

예를 들어, fruits라는 테이블을 만들고, name 컬럼에 과일 이름을, colors 컬럼에 과일 색깔을 JSON 배열로 저장하고 싶다면 다음과 같은 쿼리를 사용할 수 있습니다.

CREATE TABLE fruits (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  colors JSON NOT NULL
);

INSERT INTO fruits (name, colors) VALUES
('apple', '["red", "green"]'),
('banana', '["yellow"]'),
('orange', '["orange"]');

MySQL에서 배열 데이터를 조회하는 방법

MySQL에서 JSON 타입의 컬럼에 저장된 배열 데이터를 조회하려면, JSON 함수들을 사용해야 합니다. 예를 들어, fruits 테이블에서 과일 이름과 색깔을 조회하려면 다음과 같은 쿼리를 사용할 수 있습니다.

SELECT name, colors FROM fruits;

이 쿼리의 결과는 다음과 같습니다.

만약 색깔 컬럼의 값이 아니라, 색깔 컬럼에 포함된 값 중 하나와 일치하는 과일 이름을 조회하고 싶다면, JSON_CONTAINS 함수를 사용할 수 있습니다. 예를 들어, 색깔이 빨간색인 과일 이름을 조회하려면 다음과 같은 쿼리를 사용할 수 있습니다.

SELECT name FROM fruits WHERE JSON_CONTAINS(colors, '"red"');

이 쿼리의 결과는 다음과 같습니다.

MySQL에서 배열 데이터를 수정하고 삭제하는 방법

MySQL에서 JSON 타입의 컬럼에 저장된 배열 데이터를 수정하거나 삭제하려면, JSON_SET, JSON_REPLACE, JSON_REMOVE 등의 함수들을 사용해야 합니다. 이 함수들은 JSON 타입의 컬럼에 대해 JSON 문서를 수정하거나 삭제하는 역할을 합니다.

만약 색깔 컬럼의 값 중 하나를 다른 값으로 바꾸고 싶다면, JSON_REPLACE 함수를 사용할 수 있습니다. 예를 들어, id가 2인 과일의 색깔을 노란색에서 초록색으로 바꾸려면 다음과 같은 쿼리를 사용할 수 있습니다.

UPDATE fruits SET colors = JSON_REPLACE(colors, '$[0]', 'green') WHERE id = 2;

이 쿼리의 결과는 다음과 같습니다.

만약 색깔 컬럼의 값 중 하나를 삭제하고 싶다면, JSON_REMOVE 함수를 사용할 수 있습니다. 예를 들어, id가 1인 과일의 색깔 중 파란색을 삭제하려면 다음과 같은 쿼리를 사용할 수 있습니다.

UPDATE fruits SET colors = JSON_REMOVE(colors, '$[2]') WHERE id = 1;

이 쿼리의 결과는 다음과 같습니다.

MySQL에서 배열 데이터를 활용하는 예제

MySQL에서 배열 데이터를 활용하는 예제로는 다음과 같은 것들이 있습니다.

• 특정 조건에 맞는 배열 데이터를 필터링하거나 정렬하기
• 배열 데이터에 대한 통계적 분석을 수행하기
• 배열 데이터와 다른 테이블의 데이터를 조인하기

예를 들어, fruits 테이블과 다른 테이블인 prices 테이블을 조인하여 과일 이름과 가격을 조회하고 싶다면 다음과 같은 쿼리를 사용할 수 있습니다.

CREATE TABLE prices (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  price INT NOT NULL
);

INSERT INTO prices (name, price) VALUES
('apple', 1000),
('banana', 500),
('orange', 800);

SELECT f.name, p.price FROM fruits f JOIN prices p ON f.name = p.name;

이 쿼리의 결과는 다음과 같습니다.

참조

• https://dev.mysql.com/doc/refman/8.0/en/json.html
• https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#function_json-contains
• https://dev.mysql.com/doc/refman/8.0/en/json-modification-functions.html