PHP로 MySQL 데이터 업데이트: JSON 데이터를 처리하는 완벽한 방법

웹 개발을 하다 보면 MySQL 데이터베이스에 JSON 데이터를 저장하거나 업데이트해야 할 때가 있습니다. 이 과정은 간단해 보이지만, 큰 JSON 데이터를 처리하거나 데이터베이스 설정에 따라 예기치 못한 오류가 발생할 수 있습니다. 이번 포스팅에서는 PHP를 사용해 MySQL 데이터베이스에 JSON 데이터를 저장하고 업데이트하는 코드와 이를 디버깅하는 과정을 자세히 살펴보겠습니다.

1. 코드 개요와 동작 방식

다음은 PHP로 작성된 MySQL 데이터 업데이트 코드입니다. 이 코드는 클라이언트로부터 전달된 JSON 데이터를 MySQL 테이블의 특정 레코드에 업데이트하는 역할을 합니다.

<?php
require_once($_SERVER['DOCUMENT_ROOT'] . "/session.php");
header("Content-Type: application/json");
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

$mode = isset($_REQUEST['mode']) ? $_REQUEST['mode'] : '';
$num = isset($_REQUEST['num']) ? $_REQUEST['num'] : '';
$tablename = isset($_REQUEST['tablename']) ? $_REQUEST['tablename'] : '';
$update_log = isset($_REQUEST['update_log']) ? $_REQUEST['update_log'] : '';

if (isset($_POST['recordslatMid'])) {
    $recordslatMid_jsondata = json_decode($_POST['recordslatMid'], true);
} else {
    $recordslatMid_jsondata = null;
}

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

try {
    error_log("num: $num");
    error_log("tablename: $tablename");
    error_log("recordslatMid_jsondata: " . json_encode($recordslatMid_jsondata));
    
    $pdo->beginTransaction();

    $sql = "UPDATE {$DB}.{$tablename}  
            SET recordslatMid=? WHERE num=? LIMIT 1";
    error_log("SQL: $sql");

    $stmh = $pdo->prepare($sql);
    $stmh->bindValue(1, json_encode($recordslatMid_jsondata), PDO::PARAM_STR);          
    $stmh->bindValue(2, $num, PDO::PARAM_STR);

    $stmh->execute();

    if ($stmh->rowCount() > 0) {
        error_log("데이터가 성공적으로 업데이트되었습니다.");
    } else {
        error_log("업데이트된 행이 없습니다. num 또는 tablename 확인 필요.");
    }

    $pdo->commit();
} catch (PDOException $Exception) {
    error_log("오류: " . $Exception->getMessage());
    $pdo->rollBack();
    echo json_encode(['error' => $Exception->getMessage()], JSON_UNESCAPED_UNICODE);
    exit;
}

$data = [
    'num' => $num,            
    'recordslatMid_jsondata' => $recordslatMid_jsondata
];
error_log("반환 데이터: " . json_encode($data));

echo json_encode($data, JSON_UNESCAPED_UNICODE);
?>

2. 코드의 주요 기능

이 코드는 다음과 같은 기능을 제공합니다:

클라이언트 요청 데이터 처리
- $_REQUEST와 $_POST에서 필요한 값을 읽어옵니다.
- JSON 데이터를 디코딩하여 PHP 배열로 변환합니다.
MySQL 데이터 업데이트
- PDO를 사용해 데이터베이스에 안전하게 접속하고, SQL 쿼리를 실행합니다.
- JSON 데이터를 MySQL에 저장하기 위해 json_encode를 사용합니다.
트랜잭션 처리
- 데이터의 일관성을 유지하기 위해 트랜잭션을 시작하고, 쿼리가 성공적으로 실행되면 커밋합니다.
- 오류 발생 시 롤백하여 데이터 무결성을 보장합니다.
디버깅 및 로그 출력
- error_log를 통해 디버깅 정보를 기록합니다.
- 클라이언트로 반환되는 데이터를 JSON 형식으로 출력합니다.

3. 디버깅 포인트와 문제 해결

코드를 실행하면서 발생할 수 있는 문제와 이를 해결하기 위한 디버깅 방법은 다음과 같습니다:

3.1. SQL 오류 및 데이터 크기 제한 문제

만약 SQL 실행 중에 Row size too large와 같은 오류가 발생하면, 이는 데이터베이스 설정 문제일 가능성이 높습니다. 이런 문제는 다음과 같이 해결할 수 있습니다:

테이블의 ROW_FORMAT을 DYNAMIC으로 변경합니다.
ALTER TABLE `output` ROW_FORMAT=DYNAMIC;
JSON 데이터를 저장하는 컬럼의 데이터 타입을 LONGTEXT로 변경합니다.
ALTER TABLE `output` MODIFY COLUMN `recordslatMid` LONGTEXT;

3.2. 쿼리 실행 결과 확인

업데이트가 이루어지지 않을 경우 rowCount()를 확인하여 업데이트된 행이 있는지 확인합니다. 로그 메시지를 통해 num과 tablename 값이 올바른지 다시 검토해야 합니다.

3.3. 클라이언트 데이터 확인

클라이언트에서 전송된 JSON 데이터가 올바른지 확인하려면 디코딩된 데이터를 error_log로 출력해 문제를 진단합니다.

4. PDO와 JSON 데이터 처리의 장점

이 코드는 데이터베이스 보안을 강화하고, JSON 데이터를 효율적으로 처리하는 방법을 보여줍니다.

보안: PDO를 사용하여 SQL 인젝션 공격을 방지할 수 있습니다.
유연성: JSON 데이터를 MySQL에 저장하면 구조화된 데이터를 동적으로 처리할 수 있습니다.
디버깅 용이성: error_log와 트랜잭션을 활용해 오류를 신속히 탐지하고 처리할 수 있습니다.

5. 활용 팁

이 코드를 다른 프로젝트에 적용할 때 몇 가지 팁을 염두에 두세요:

트랜잭션 사용
데이터 무결성이 중요한 경우, 트랜잭션을 활용하세요.
JSON 데이터 크기 관리
큰 JSON 데이터를 처리할 때는 데이터베이스와 PHP 메모리 제한을 고려해야 합니다.
디버깅 로그 사용
개발 단계에서 error_log를 적극 활용해 문제를 진단하세요. 운영 환경에서는 디버깅 정보를 비활성화하여 보안을 강화할 수 있습니다.

이 코드를 통해 PHP와 MySQL을 활용한 JSON 데이터 처리가 한층 더 쉬워졌기를 바랍니다. 앞으로도 MySQL과 JSON 데이터 관련 작업에서 실수를 줄이고, 더 효율적인 코드를 작성하는 데 도움이 되길 기대합니다.

우리주변

PHP로 MySQL 데이터 업데이트: JSON 데이터를 처리하는 완벽한 방법