MySQL 테이블 데이터를 JSON으로 변환하고 저장하는 방법

데이터베이스를 설계하고 개발하다 보면 기존의 테이블 구조를 효율적으로 변경해야 할 때가 있습니다. 특히 데이터를 JSON 형태로 저장하면 유연성과 가독성이 크게 향상됩니다. 이번 글에서는 기존 MySQL 테이블 데이터를 JSON 형태로 변환해 저장하는 과정을 초보자도 이해하기 쉽게 설명하려고 합니다.

기존 테이블 구조와 문제점

저희가 다룰 데이터는 두 개의 테이블로 구성되어 있습니다. 먼저, ETxmlgrid라는 테이블은 각 행이 parentKey라는 키를 통해 특정 그룹에 속해 있습니다. 또 다른 테이블인 estimatedb는 ETxmlgrid 테이블의 데이터를 참조하는 부모 테이블로, xmlJson이라는 컬럼에 JSON 데이터를 저장해야 합니다.

기존 테이블 구조

ETxmlgrid 테이블:

childnum: 기본 키
parentKey: 부모 테이블의 키
description, material, length 등 여러 컬럼들

estimatedb 테이블:

num: 기본 키
xmlJson: JSON 데이터를 저장할 컬럼

문제는 ETxmlgrid 데이터를 JSON 형태로 변환해 estimatedb 테이블의 xmlJson 컬럼에 저장하는 것입니다. 이를 통해 데이터를 더 직관적으로 관리하고 활용할 수 있게 됩니다.

JSON 변환과 저장 방법

JSON 데이터를 다루기 위해선 다음 단계를 거칩니다:

ETxmlgrid 데이터를 그룹화하여 JSON으로 변환
변환된 JSON 데이터를 estimatedb 테이블에 저장
필요한 데이터 형식을 정확히 확인 및 검증

아래는 이를 구현한 PHP 코드입니다. 간단한 예제를 통해 이해를 돕겠습니다.

1. 데이터베이스 연결 및 초기 설정

require_once($_SERVER['DOCUMENT_ROOT'] . "/lib/mydb.php");
$pdo = db_connect();

PDO 객체를 이용해 MySQL 데이터베이스와 연결합니다. 이는 데이터베이스 작업을 안전하게 수행하기 위한 기본적인 준비 과정입니다.

2. ETxmlgrid 데이터를 JSON으로 변환

ETxmlgrid 테이블에서 parentKey를 기준으로 데이터를 그룹화하고, 각 그룹을 JSON 배열로 변환합니다. 이를 위해 JSON_ARRAYAGG와 JSON_OBJECT를 사용합니다.

$sql = "
    SELECT
        parentKey,
        JSON_ARRAYAGG(
            JSON_OBJECT(
                'description', IFNULL(description, ''),
                'material', IFNULL(material, ''),
                'length', IFNULL(length, ''),
                'width', IFNULL(width, ''),
                'thickness', IFNULL(thickness, ''),
                'weights', IFNULL(weights, ''),
                'donenum', IFNULL(donenum, ''),
                'donetime', IFNULL(donetime, '')
                -- 필요한 컬럼 추가
            )
        ) AS xmlJsonData
    FROM ETxmlgrid
    GROUP BY parentKey;
";

$stmt = $pdo->query($sql);
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

JSON_ARRAYAGG는 데이터를 배열 형태로 묶어주고, JSON_OBJECT는 개별 객체를 생성합니다. 여기서 IFNULL을 사용해 NULL 값을 빈 문자열로 변환하여 JSON 데이터가 깨지지 않도록 합니다.

3. JSON 데이터를 estimatedb 테이블에 저장

이제 변환된 JSON 데이터를 estimatedb의 xmlJson 컬럼에 업데이트합니다.

$updateSql = "UPDATE estimatedb SET xmlJson = :xmlJson WHERE num = :parentKey";
$updateStmt = $pdo->prepare($updateSql);

foreach ($result as $row) {
    $updateStmt->bindValue(':xmlJson', $row['xmlJsonData'], PDO::PARAM_STR);
    $updateStmt->bindValue(':parentKey', $row['parentKey'], PDO::PARAM_INT);
    $updateStmt->execute();
}

이 코드는 parentKey를 기준으로 xmlJson 컬럼에 JSON 데이터를 저장합니다. bindValue를 사용해 SQL 인젝션을 방지하면서 안전하게 값을 바인딩합니다.

구현 결과 확인

위 코드를 실행하면 ETxmlgrid의 데이터가 그룹화되어 estimatedb의 xmlJson 컬럼에 JSON 형태로 저장됩니다. 저장된 데이터는 다음과 같은 구조를 가집니다:

[
    {
        "description": "Sample Item 1",
        "material": "Steel",
        "length": "100",
        "width": "50",
        "thickness": "2.5",
        "weights": "1.2",
        "donenum": "5",
        "donetime": "2023-12-25"
    },
    {
        "description": "Sample Item 2",
        "material": "Aluminum",
        "length": "200",
        "width": "80",
        "thickness": "1.5",
        "weights": "0.8",
        "donenum": "10",
        "donetime": "2023-12-26"
    }
]

이 데이터는 JSON 디코딩을 통해 PHP나 JavaScript에서 손쉽게 처리할 수 있습니다.

유용한 팁

JSON 저장 시 검증: 데이터가 정확히 JSON 형식인지 저장 전 확인하세요. PHP의 json_encode를 사용하면 간편하게 검증할 수 있습니다.
NULL 처리: NULL 값은 JSON 형식에서 오류를 유발할 수 있으므로 IFNULL이나 PHP의 조건문으로 처리하세요.
데이터 최적화: 대량의 데이터를 처리할 때는 페이징이나 배치 작업으로 나누어 처리하면 성능을 향상시킬 수 있습니다.

이 글을 통해 MySQL 데이터를 JSON으로 변환하고 활용하는 방법을 이해하셨기를 바랍니다. 실무에서 JSON은 데이터 구조화와 전송에 매우 유용하므로 적극 활용해보세요.

우리주변

MySQL 테이블 데이터를 JSON으로 변환하고 저장하는 방법