PHP와 MySQL로 JSON 변환 및 저장하기: 기존 테이블 데이터 재구성

PHP와 MySQL로 JSON 변환 및 저장하기: 기존 테이블 데이터 재구성

웹 개발을 하다 보면, 데이터를 효율적으로 관리하기 위해 데이터베이스를 사용합니다. 하지만 시간이 지나면서 기존에 구축한 데이터 구조가 비효율적이거나 복잡하다고 느껴질 때가 있습니다. 특히 JSON 형식을 활용하면 이런 문제를 해결할 수 있습니다. 이번 글에서는 기존 데이터베이스 테이블을 JSON 형식으로 변환하여 새로운 컬럼에 저장하는 과정을 소개합니다.

 

문제 상황: 기존 데이터베이스 구조의 한계

예전에는 JSON을 사용하는 방법에 익숙하지 않아서, 관계형 데이터베이스의 테이블을 연결하여 데이터를 저장하곤 했습니다. 예를 들어, estimatedb라는 테이블이 있고, 이 테이블의 각 행에 연결된 세부 데이터는 ETxmlgrid라는 테이블에 저장되어 있었습니다.

ETxmlgrid 테이블은 parentKey라는 필드로 estimatedb 테이블의 num 필드와 연결되어 있었습니다. 이 방식은 데이터를 정규화하는 데는 적합했지만, 데이터를 조회하거나 활용할 때 여러 번의 조인을 수행해야 했기 때문에 비효율적이었습니다.

현재는 PHP의 json_encode와 json_decode를 활용하여 데이터를 JSON 형식으로 저장하고 활용할 수 있습니다. 이를 통해 기존의 ETxmlgrid 데이터를 JSON으로 변환하고, estimatedb 테이블의 새로운 xmlJson 컬럼에 저장하는 작업을 진행했습니다.

---

데이터베이스 테이블 구조

작업을 이해하기 위해 먼저 두 테이블의 구조를 살펴보겠습니다.

1. ETxmlgrid 테이블 이 테이블은 여러 세부 정보를 저장하며, parentKey를 통해 상위 테이블인 estimatedb와 연결됩니다.

CREATE TABLE `ETxmlgrid` (
  `childnum` int NOT NULL AUTO_INCREMENT,
  `parentKey` text,
  `description` text,
  `material` text,
  `length` text,
  `width` text,
  `thickness` text,
  `weights` text,
  `donenum` text,
  `donetime` text,
  `laserfee` text,
  `unitfee` text,
  `bendingNum` text,
  `bendingFee` text,
  `bendingUnitFee` text,
  `bendingAmount` text,
  `paintingArea` text,
  `paintingChoice` text,
  `paintingUnit` text,
  `paintingUnitFee` text,
  `paintingAmount` text,
  `lastworkUnitFee` text,
  `lastwork` text,
  `unit_Amount` text,
  `est_Amount` text,
  `device` text,
  `outfitgrid` text,
  `item_code_grid` text,
  `item_name_grid` text,
  `item_desc_grid` text,
  `vat_grid` text,
  `deadline_grid` date DEFAULT NULL,
  `memo_grid` text,
  `jpgurl` text,
  `realurl` text,
  PRIMARY KEY (`childnum`)
) ENGINE=InnoDB AUTO_INCREMENT=1955 DEFAULT CHARSET=utf8;

2. estimatedb 테이블 이 테이블은 상위 데이터를 저장하며, 이번 작업에서 xmlJson 컬럼에 변환된 JSON 데이터를 저장할 것입니다.

CREATE TABLE `estimatedb` (
  `num` int NOT NULL AUTO_INCREMENT,
  `estimate_EMP_CD` text,
  `estimate_erpuser` text,
  `estimate_TIME_DATE` date DEFAULT NULL,
  `estimate_regist_date` date DEFAULT NULL,
  `estimate_CUST` text,
  `estimate_CUST_DES` text,
  `estimate_WH_CD` text,
  `estimate_warename_input` text,
  `estimate_Ecount` text,
  `estimate_comment` text,
  `estimate_searchtag` text,
  `estimateJson` text CHARACTER SET utf8 COLLATE utf8_general_ci,
  `update_log` text,
  `xmlJson` text,
  PRIMARY KEY (`num`)
) ENGINE=InnoDB AUTO_INCREMENT=77 DEFAULT CHARSET=utf8;

---

SQL로 JSON 데이터 생성하기

먼저, ETxmlgrid 테이블의 데이터를 parentKey별로 JSON으로 묶는 SQL 코드를 작성합니다.

SELECT 
    parentKey,
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'description', description,
            'material', material,
            'length', length,
            'width', width,
            'thickness', thickness,
            'weights', weights,
            'donenum', donenum,
            'donetime', donetime,
            'laserfee', laserfee,
            'unitfee', unitfee,
            'bendingNum', bendingNum,
            'bendingFee', bendingFee,
            'bendingUnitFee', bendingUnitFee,
            'bendingAmount', bendingAmount,
            'paintingArea', paintingArea,
            'paintingChoice', paintingChoice,
            'paintingUnit', paintingUnit,
            'paintingUnitFee', paintingUnitFee,
            'paintingAmount', paintingAmount,
            'lastworkUnitFee', lastworkUnitFee,
            'lastwork', lastwork,
            'unit_Amount', unit_Amount,
            'est_Amount', est_Amount,
            'device', device,
            'outfitgrid', outfitgrid,
            'item_code_grid', item_code_grid,
            'item_name_grid', item_name_grid,
            'item_desc_grid', item_desc_grid,
            'vat_grid', vat_grid,
            'deadline_grid', deadline_grid,
            'memo_grid', memo_grid,
            'jpgurl', jpgurl,
            'realurl', realurl
        )
    ) AS xmlJsonData
FROM dbeunsungbiz.ETxmlgrid
GROUP BY parentKey;

---

PHP로 데이터 변환 및 저장하기

위 SQL로 생성된 JSON 데이터를 estimatedb 테이블의 xmlJson 컬럼에 저장하기 위해 PHP 코드를 작성합니다.

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

try {
    // 1. ETxmlgrid 데이터를 JSON으로 변환
    $sql = "
        SELECT 
            parentKey,
            JSON_ARRAYAGG(
                JSON_OBJECT(
                    'description', description,
                    'material', material,
                    'length', length,
                    'width', width,
                    'thickness', thickness,
                    'weights', weights,
                    'donenum', donenum,
                    'donetime', donetime,
                    'laserfee', laserfee,
                    'unitfee', unitfee,
                    'bendingNum', bendingNum,
                    'bendingFee', bendingFee,
                    'bendingUnitFee', bendingUnitFee,
                    'bendingAmount', bendingAmount,
                    'paintingArea', paintingArea,
                    'paintingChoice', paintingChoice,
                    'paintingUnit', paintingUnit,
                    'paintingUnitFee', paintingUnitFee,
                    'paintingAmount', paintingAmount,
                    'lastworkUnitFee', lastworkUnitFee,
                    'lastwork', lastwork,
                    'unit_Amount', unit_Amount,
                    'est_Amount', est_Amount,
                    'device', device,
                    'outfitgrid', outfitgrid,
                    'item_code_grid', item_code_grid,
                    'item_name_grid', item_name_grid,
                    'item_desc_grid', item_desc_grid,
                    'vat_grid', vat_grid,
                    'deadline_grid', deadline_grid,
                    'memo_grid', memo_grid,
                    'jpgurl', jpgurl,
                    'realurl', realurl
                )
            ) AS xmlJsonData
        FROM dbeunsungbiz.ETxmlgrid
        GROUP BY parentKey;
    ";

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

    // 2. 각 parentKey에 해당하는 데이터를 estimatedb.xmlJson에 업데이트
    $updateSql = "UPDATE dbeunsungbiz.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();
    }

    echo "데이터 변환이 완료되었습니다.";
} catch (PDOException $e) {
    echo "오류: " . $e->getMessage();
}
?>

---

JSON 활용의 장점

이번 작업을 통해 기존의 복잡했던 데이터 구조를 JSON으로 단순화하여 관리와 활용이 쉬워졌습니다. 데이터베이스에 저장된 JSON은 PHP의 json_encode와 json_decode를 통해 손쉽게 처리할 수 있습니다. 이런 접근법은 앞으로 유사한 프로젝트에서도 유용하게 사용할 수 있습니다.