JSON을 사용하여 MySQL에서 배열 데이터 처리 방법 (예시 포함 완전 가이드)

1 1. 소개
- 1.1 MySQL에서 배열 데이터 처리 필요성
- 1.2 JSON 데이터 타입 사용의 장점
2 2. MySQL JSON으로 배열을 다루는 기본 지식
- 2.1 JSON 데이터 타입이란?
- 2.2 JSON 데이터 타입의 장점 및 활용 사례
3 3. 기본 JSON 배열 연산
4 4. JSON 배열 검색 및 필터링
5 5. 실용 예시: 실제 사용 사례에서 JSON 배열 활용
6 6. JSON 데이터 유형 사용 시 중요한 고려 사항
7 7. MySQL에서 배열 사용에 관한 자주 묻는 질문
8 8. 요약

1. 소개

MySQL에서 배열 데이터 처리 필요성

데이터베이스는 일반적으로 관계형 설계 원칙에 따라 데이터를 저장합니다. 그러나 애플리케이션 요구에 따라 하나의 컬럼에 여러 값을 저장하고 싶을 때가 있습니다. 이런 경우 “배열”과 유사한 데이터 구조가 유용합니다.

예를 들어 다음과 같은 상황을 생각해 볼 수 있습니다:

사용자가 선택한 여러 태그를 저장
제품에 대한 여러 이미지 URL을 저장
히스토리나 로그를 하나의 필드에 결합

JSON 데이터 타입 사용의 장점

MySQL은 직접적인 “배열 타입”을 제공하지 않지만 JSON 데이터 타입을 사용하면 배열과 같은 데이터 구조를 처리할 수 있습니다. JSON 타입은 매우 유연하며 다음과 같은 장점을 제공합니다:

중첩 데이터 구조 지원
쿼리 내에서 손쉽게 데이터 조작 가능
하나의 필드 안에 다양한 데이터 포맷을 관리 가능

이 글에서는 JSON 데이터 타입을 활용해 MySQL에서 배열 데이터를 효율적으로 처리하는 방법을 소개합니다.

2. MySQL JSON으로 배열을 다루는 기본 지식

JSON 데이터 타입이란?

JSON (JavaScript Object Notation)은 가볍고 단순한 데이터 교환 형식입니다. MySQL에서는 5.7 버전부터 기본 JSON 지원이 도입되어, 데이터베이스 내에서 JSON 형식의 데이터를 직접 저장하고 조작할 수 있습니다.

예시: 아래는 JSON 컬럼에 저장될 수 있는 데이터 예시입니다.

{
  "tags": ["PHP", "MySQL", "JSON"],
  "status": "published"
}

JSON 데이터 타입의 장점 및 활용 사례

JSON 타입을 사용할 때의 주요 장점은 다음과 같습니다:

유연한 데이터 구조 : 관계형 스키마를 수정하지 않고도 가변 길이 데이터를 처리할 수 있습니다.
효율적인 데이터 조작 : MySQL 전용 함수(JSON_EXTRACT, JSON_ARRAY 등)를 사용해 데이터를 손쉽게 조작할 수 있습니다.
스키마리스 설계 : 애플리케이션 요구가 변할 때 스키마를 자주 수정할 필요가 없습니다.

활용 사례 예시:

제품 정보에 여러 카테고리를 할당
사용자 맞춤 설정 저장
웹 애플리케이션에서 중첩 JSON 데이터 사용

3. 기본 JSON 배열 연산

JSON 배열 생성

MySQL에서는 JSON_ARRAY 함수를 사용해 손쉽게 JSON 배열을 만들 수 있습니다. 배열은 하나의 컬럼에 여러 값을 저장할 때 유용합니다.

예시

다음 쿼리는 tags 라는 이름의 JSON 배열을 생성합니다.

SELECT JSON_ARRAY('PHP', 'MySQL', 'JavaScript') AS tags;

결과:

["PHP", "MySQL", "JavaScript"]

실용 예시

다음 예시는 INSERT 문을 사용해 데이터베이스에 JSON 배열을 저장하는 방법을 보여줍니다.

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tags JSON
);

INSERT INTO articles (tags) 
VALUES (JSON_ARRAY('PHP', 'MySQL', 'JavaScript'));

JSON 배열에서 데이터 추출

JSON 배열에 저장된 데이터를 가져오려면 JSON_EXTRACT 함수를 사용합니다. 이 함수로 배열의 특정 요소를 손쉽게 추출할 수 있습니다.

예시

다음 예시는 배열의 두 번째 요소(인덱스는 0부터 시작)를 가져옵니다.

SELECT JSON_EXTRACT('["PHP", "MySQL", "JavaScript"]', '$[1]') AS second_tag;

결과:

"MySQL"

여러 요소 한 번에 추출

한 번에 여러 요소를 추출할 수도 있습니다.

SELECT JSON_EXTRACT('["PHP", "MySQL", "JavaScript"]', '$[0]', '$[2]') AS extracted_values;

배열에 데이터 추가, 업데이트, 삭제

배열에 데이터 추가

기존 배열에 새 데이터를 추가하려면 JSON_ARRAY_APPEND 함수를 사용합니다.

SET @tags = '["PHP", "MySQL"]';
SELECT JSON_ARRAY_APPEND(@tags, '$', 'JavaScript') AS updated_tags;

결과:

["PHP", "MySQL", "JavaScript"]

배열의 데이터 업데이트

JSON_SET 함수를 사용하면 배열의 특정 요소를 업데이트할 수 있습니다.

SET @tags = '["PHP", "MySQL", "JavaScript"]';
SELECT JSON_SET(@tags, '$[1]', 'Python') AS updated_tags;

결과:

["PHP", "Python", "JavaScript"]

배열에서 데이터 제거

배열에서 특정 요소를 JSON_REMOVE 함수를 사용하여 제거할 수 있습니다.

SET @tags = '["PHP", "MySQL", "JavaScript"]';
SELECT JSON_REMOVE(@tags, '$[1]') AS updated_tags;

결과:

["PHP", "JavaScript"]

4. JSON 배열 검색 및 필터링

특정 데이터를 포함하는 배열 검색

JSON 배열에 특정 데이터가 포함되어 있는지 확인하려면 JSON_CONTAINS 함수를 사용하십시오. 이 함수는 지정된 값이 JSON 배열에 존재하는지를 판단합니다.

예시

다음 예시는 JSON 배열에 “MySQL”이 포함되어 있는지 확인합니다.

SELECT JSON_CONTAINS('["PHP", "MySQL", "JavaScript"]', '"MySQL"') AS is_present;

결과:

1  (if present)
0  (if not present)

실용 예시: 조건부 검색

데이터베이스 테이블에서 JSON 배열에 특정 값이 포함된 행을 검색하려면 WHERE 절에 JSON_CONTAINS를 사용합니다.

SELECT * 
FROM articles
WHERE JSON_CONTAINS(tags, '"MySQL"');

이 쿼리는 tags 열에 “MySQL”이 포함된 행을 반환합니다.

배열 길이 가져오기

JSON 배열의 요소 개수를 가져오려면 JSON_LENGTH 함수를 사용하십시오. 이 함수는 배열의 요소 수를 반환하며 데이터 분석 및 조건 로직에 유용합니다.

예시

다음 예시는 배열의 요소 개수를 가져옵니다.

SELECT JSON_LENGTH('["PHP", "MySQL", "JavaScript"]') AS array_length;

결과:

실용 예시: 특정 조건을 만족하는 행 추출

요소 개수가 특정 값 이상인 행을 추출하려면 WHERE 절에 JSON_LENGTH를 사용합니다.

SELECT * 
FROM articles
WHERE JSON_LENGTH(tags) >= 2;

이 쿼리는 tags 열에 두 개 이상 요소가 포함된 행을 반환합니다.

고급 조건부 쿼리 예시

여러 조건을 결합하여 보다 고급 검색을 수행할 수 있습니다. 다음 쿼리는 tags 배열에 “JavaScript”가 포함되고 요소가 세 개 이상인 행을 검색합니다.

SELECT * 
FROM articles
WHERE JSON_CONTAINS(tags, '"JavaScript"') 
  AND JSON_LENGTH(tags) >= 3;

5. 실용 예시: 실제 사용 사례에서 JSON 배열 활용

제품 카테고리를 JSON 배열로 저장하는 방법

전자상거래 사이트 및 유사 시스템에서는 하나의 제품이 여러 카테고리에 속할 수 있습니다. 이 경우 JSON 배열을 사용하면 카테고리 정보를 효율적으로 저장할 수 있습니다.

예시: 제품 카테고리 데이터 저장

아래는 제품 테이블에 categories라는 JSON 컬럼을 생성하고 여러 카테고리를 저장하는 예시입니다.

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

INSERT INTO products (name, categories) 
VALUES ('Laptop', JSON_ARRAY('Electronics', 'Computers')),
       ('Smartphone', JSON_ARRAY('Electronics', 'Mobile Devices'));

이 구조는 제품이 여러 카테고리에 속하더라도 데이터를 간결하게 유지합니다.

특정 카테고리의 제품을 추출하는 쿼리

JSON 데이터 타입을 활용하면 특정 카테고리에 속한 제품을 쉽게 검색할 수 있습니다.

쿼리 예시

다음 쿼리는 “Electronics” 카테고리의 모든 제품을 가져옵니다.

SELECT name 
FROM products
WHERE JSON_CONTAINS(categories, '"Electronics"');

결과:

Laptop
Smartphone

이 쿼리를 사용하면 카테고리별로 제품 목록을 유연하게 쉽게 가져올 수 있습니다.

예시: 가격 범위로 필터링

가격 정보를 포함한 JSON 데이터를 저장하고 가격 범위에 따라 제품을 검색하는 방법을 살펴보겠습니다.

예시 데이터

다음 예제는 JSON 유형을 사용하여 제품별 가격 정보를 저장합니다.

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

INSERT INTO products_with_prices (name, details)
VALUES ('Laptop', '{"price": 150000, "categories": ["Electronics", "Computers"]}'),
       ('Smartphone', '{"price": 80000, "categories": ["Electronics", "Mobile Devices"]}');

쿼리 예제

가격이 100,000원 이상인 제품을 검색하려면 JSON_EXTRACT 함수를 사용하십시오.

SELECT name 
FROM products_with_prices
WHERE JSON_EXTRACT(details, '$.price') >= 100000;

결과:

Laptop

JSON_TABLE을 사용한 JSON 확장 및 쿼리 예제

관계형 형식으로 JSON 데이터를 쿼리하려면 JSON_TABLE 함수가 매우 유용합니다. 이 함수는 JSON 배열을 가상 테이블로 확장할 수 있게 해줍니다.

예제

다음 예제는 JSON 배열을 확장하여 각 카테고리를 별도의 행으로 표시합니다.

SELECT * 
FROM JSON_TABLE(
    '["Electronics", "Computers", "Mobile Devices"]',
    '$[*]' COLUMNS(
        category_name VARCHAR(100) PATH '$'
    )
) AS categories_table;

결과:

category_name
--------------
Electronics
Computers
Mobile Devices

6. JSON 데이터 유형 사용 시 중요한 고려 사항

성능 최적화 팁

JSON 유형은 유연하지만, 설계가 부실하면 데이터베이스 성능에 부정적인 영향을 미칠 수 있습니다. 아래는 주요 최적화 포인트입니다.

1. 인덱스 사용

MySQL에서는 JSON 컬럼 자체에 직접 인덱스를 만들 수 없지만, 생성된 컬럼을 만들어 특정 키에 인덱스를 걸 수 있습니다.

예시: 생성된 컬럼을 사용한 인덱스 생성

다음 예제에서는 JSON 데이터 내부의 price 키를 인덱스 대상으로 사용합니다.

ALTER TABLE products_with_prices
ADD COLUMN price INT AS (JSON_EXTRACT(details, '$.price')) STORED,
ADD INDEX idx_price (price);

생성된 컬럼을 사용하면 JSON 데이터에 대한 검색 성능을 크게 향상시킬 수 있습니다.

2. 과도하게 복잡한 JSON 구조 피하기

깊게 중첩된 JSON 구조는 쿼리 가독성과 성능을 저하시킬 수 있습니다. 데이터를 설계할 때는 가능한 가장 단순한 JSON 구조를 선택하십시오.

좋은 예시:

{
  "categories": ["Electronics", "Computers"],
  "price": 150000
}

피해야 할 예시:

{
  "product": {
    "details": {
      "price": 150000,
      "categories": ["Electronics", "Computers"]
    }
  }
}

인덱스 활용 방법

생성된 컬럼을 사용해 인덱싱할 때는 다음 사항을 유념하십시오:

생성된 컬럼은 STORED이어야 합니다.
JSON_EXTRACT 함수를 사용해 특정 키를 추출하여 생성된 컬럼으로 만듭니다.

예를 들어, categories 키의 첫 번째 요소를 추출해 인덱스를 만들려면 다음과 같이 합니다.

ALTER TABLE products
ADD COLUMN main_category VARCHAR(255) AS (JSON_EXTRACT(categories, '$[0]')) STORED,
ADD INDEX idx_main_category (main_category);

데이터 검증의 중요성

JSON 데이터는 유연하기 때문에 잘못된 형식으로 데이터를 저장하기 쉬워집니다. 데이터 무결성을 유지하려면 다음과 같은 방법을 사용하십시오.

1. CHECK 제약조건 사용

MySQL 8.0 이상에서는 CHECK 제약조건을 사용해 JSON 구조와 내용을 검증할 수 있습니다.

ALTER TABLE products_with_prices
ADD CONSTRAINT check_price CHECK (JSON_EXTRACT(details, '$.price') >= 0);

2. 애플리케이션 수준 검증

데이터를 삽입할 때는 애플리케이션 측에서 JSON 형식을 검증하는 것이 권장됩니다. PHP와 Python과 같은 프로그래밍 언어는 표준 라이브러리를 사용해 JSON을 검증할 수 있습니다.

7. MySQL에서 배열 사용에 관한 자주 묻는 질문

Q1: MySQL에 배열 데이터 유형이 있나요?

A1:MySQL은 직접적인 “배열 데이터 타입”을 제공하지 않습니다. 그러나 JSON 타입을 사용하면 배열과 같은 데이터 구조를 처리할 수 있습니다. JSON 타입을 사용하면 하나의 컬럼에 여러 값을 저장하고 쿼리를 통해 조작할 수 있습니다.

예시:

SELECT JSON_ARRAY('Value 1', 'Value 2', 'Value 3') AS array_example;

결과:

["Value 1", "Value 2", "Value 3"]

Q2: JSON 데이터에 인덱스를 생성할 수 있나요?

A2:JSON 타입 자체에 직접 인덱스를 생성할 수 없습니다. 그러나 특정 키나 값을 생성된 컬럼으로 추출하고 그 컬럼에 인덱스를 생성할 수 있습니다.

예시:

ALTER TABLE products_with_prices
ADD COLUMN price INT AS (JSON_EXTRACT(details, '$.price')) STORED,
ADD INDEX idx_price (price);

이를 통해 JSON 데이터 내부의 값을 효율적으로 검색할 수 있습니다.

Q3: JSON 데이터에 크기 제한이 있나요?

A3:MySQL의 JSON 타입은 최대 4GB까지 데이터를 저장할 수 있습니다. 하지만 매우 큰 JSON 문서를 사용하면 성능이 저하될 수 있으므로 데이터를 신중하게 설계해야 합니다.

권장 사항:

최소한의 필수 데이터만 저장하십시오.
깊게 중첩된 JSON 구조를 피하십시오.

Q4: JSON 배열 내부의 특정 요소를 어떻게 업데이트할 수 있나요?

A4:JSON_SET 함수를 사용하여 배열의 특정 요소를 업데이트할 수 있습니다.

예시:

SET @tags = '["PHP", "MySQL", "JavaScript"]';
SELECT JSON_SET(@tags, '$[1]', 'Python') AS updated_tags;

결과:

["PHP", "Python", "JavaScript"]

Q5: JSON 타입과 일반 테이블 설계 비교

A5:JSON 타입은 매우 유연하지만 전통적인 관계형 데이터베이스 설계와는 다른 특성을 가지고 있습니다.

Item	JSON Type	Traditional Table Design
Flexibility	High (no schema changes needed)	Fixed (schema changes required)
Performance	Inferior for some operations	Optimized
Query Complexity	Requires JSON functions	Simple
Indexing	Partially supported via generated columns	Fully supported

8. 요약

MySQL에서 배열 작업을 위한 JSON 데이터 타입 사용의 장점

이 기사에서는 MySQL에서 배열과 같은 데이터를 다룰 때 유용한 JSON 데이터 타입에 대해 설명했습니다. 아래는 다룬 주요 내용의 요약입니다:

왜 JSON 타입을 사용해야 하는가 MySQL은 직접적인 배열 타입을 제공하지 않지만, JSON 타입을 사용하면 하나의 컬럼에 여러 값을 저장하고 유연한 데이터 조작을 할 수 있습니다.
기본 JSON 작업

JSON 배열 생성, 데이터 추출, 데이터 업데이트 및 삭제 방법을 다루었습니다.
JSON_ARRAY, JSON_EXTRACT, JSON_SET와 같은 편리한 함수를 사용하여 배열 데이터를 효율적으로 조작할 수 있습니다.

검색 및 필터링

JSON_CONTAINS를 사용하여 특정 값을 포함하는 데이터를 검색하는 방법.
JSON_LENGTH로 요소 개수를 가져오고 조건부 필터링을 수행하는 방법.

실용적인 예시 제품 카테고리 관리 및 가격 필터링과 같은 실제 사례를 통해 애플리케이션에서 JSON 배열을 적용하는 구체적인 방법을 배웠습니다.
고려 사항 및 최적화

생성된 컬럼을 이용한 인덱스 설정 방법과 JSON 데이터 검증의 중요성을 강조했습니다.

JSON 데이터 타입 사용 시 다음 단계

MySQL에서 JSON 타입을 사용하면 전통적인 관계형 데이터베이스 설계보다 데이터를 보다 유연하게 관리할 수 있습니다. 그러나 좋은 설계와 성능 고려가 필수적입니다.

다음에 배울 주제:

복합 인덱스 사용 JSON 데이터와 일반 컬럼을 결합한 인덱스 설계.
고급 JSON 함수 사용 JSON_MERGE, JSON_OBJECT와 같은 함수를 이용해 더 복잡한 작업 수행.
애플리케이션 수준 데이터 처리 PHP 또는 Python을 사용해 MySQL JSON 데이터를 효율적으로 조작하는 방법.

요약

이 기사를 통해 이제 MySQL의 JSON 데이터 타입을 사용해 배열과 같은 데이터를 효율적으로 처리하는 방법을 이해했을 것입니다. 이 지식을 적용하면 보다 유연하고 확장 가능한 데이터베이스를 설계할 수 있습니다.

JSON을 사용하여 MySQL에서 배열 데이터 처리 방법 (예시 포함 완전 가이드)

1. 소개

MySQL에서 배열 데이터 처리 필요성

JSON 데이터 타입 사용의 장점

2. MySQL JSON으로 배열을 다루는 기본 지식

JSON 데이터 타입이란?

JSON 데이터 타입의 장점 및 활용 사례

3. 기본 JSON 배열 연산

JSON 배열 생성

예시

실용 예시

JSON 배열에서 데이터 추출

예시

여러 요소 한 번에 추출

배열에 데이터 추가, 업데이트, 삭제

배열에 데이터 추가

배열의 데이터 업데이트

배열에서 데이터 제거

4. JSON 배열 검색 및 필터링

특정 데이터를 포함하는 배열 검색

예시

실용 예시: 조건부 검색

배열 길이 가져오기

예시

실용 예시: 특정 조건을 만족하는 행 추출

고급 조건부 쿼리 예시

5. 실용 예시: 실제 사용 사례에서 JSON 배열 활용

제품 카테고리를 JSON 배열로 저장하는 방법

예시: 제품 카테고리 데이터 저장

특정 카테고리의 제품을 추출하는 쿼리

쿼리 예시

예시: 가격 범위로 필터링

예시 데이터

쿼리 예제

JSON_TABLE을 사용한 JSON 확장 및 쿼리 예제

예제

6. JSON 데이터 유형 사용 시 중요한 고려 사항

성능 최적화 팁

1. 인덱스 사용

예시: 생성된 컬럼을 사용한 인덱스 생성

2. 과도하게 복잡한 JSON 구조 피하기

인덱스 활용 방법

데이터 검증의 중요성

1. CHECK 제약조건 사용

2. 애플리케이션 수준 검증

7. MySQL에서 배열 사용에 관한 자주 묻는 질문

8. 요약

MySQL에서 배열 작업을 위한 JSON 데이터 타입 사용의 장점

JSON 데이터 타입 사용 시 다음 단계

다음에 배울 주제:

요약

MySQL 테이블 잠금 설명: READ와 WRITE, 구문, 사용법 및 모범 사례

MySQL 문자 인코딩 문제(모지베이크) 해결 방법: 원인, 해결책 및 트러블슈팅 가이드