MySQL 테이블 데이터 백업과 수정
개요
리그 오브 레전드를 주제로 한 토이 프로젝트를 진행 중에, 새로운 시즌이 시작됨에 따라 플래티넘과 다이아몬드 티어 사이에 새롭게 에메랄드 티어가 도입되었다. 내 데이터베이스 테이블은 사용자의 닉네임, 티어, 라인, 점수로 구성되어 있으며, 사용자의 티어에 따라 자동으로 점수가 매핑되도록 설정되어 있다. 즉, 티어와 점수가 직접 연결되어 있기 때문에, 이러한 게임 내 변화는 데이터베이스의 일관성을 유지하기 위한 추가 조치가 필요하다는 것을 알았다.
이번 시간에는 기존의 테이블에 존재하는 데이터를 수정하는 방법과, 그전에 데이터를 백업하는 방법에 대해 알아보도록 하자.
본문
데이터 백업
데이터를 수정하기 전에 백업을 하자. 필자는 WorkBench 8.0을 쓰고 있어서, 이를 기준으로 설명을 진행하도록 하겠다.
사용 중인 플랫폼을 열고, 대상 데이터베이스에 연결한 후 Server 메뉴에서 Data Export를 선택한다.
Tables to Export에서 백업하고자 하는 스키마를 선택하면 된다. 만약 선택한 스키마 내의 특정 테이블만 백업하려면 왼쪽의 스키마를 선택한 후 오른쪽의 테이블을 선택하면 된다.
이후 테이블의 구조, 데이터를 어떤 식으로 백업할지 선택하면 된다. Dump Structure and Data는 테이블 구조와 데이터 모두를 백업하는 기능이고, Dump Data Only는 테이블의 데이터만 백업, Dump Structure Only는 테이블의 구조만 백업하는 기능이다.
마지막으로 백업 데이터를 단일 파일로 저장(Export to Self-Contained File)할 것인지, 각 테이블과 오브젝트를 개별 파일로 저장(Export to Dump Project Folder)할 것인지 선택하면 된다. 만약 데이터베이스가 상당히 크다면 후자를 선택하는 것이 더 좋을 것이다.
만약 백업할 데이터베이스 오브젝트의 유형을 세밀하게 선택하고자 하면 Object to Export에서 필요한 옵션을 선택하면 된다. Stored Procedures and Functions는 저장 프로시저와 함수를 포함한 저장된 프로그램을 백업하는 기능이고, Event는 MySQL의 이벤트 스케줄러에서 정의된 이벤트, Triggers는 특정 테이블에서 데이터가 변경될 때 자동으로 실행되는 SQL 쿼리문을 의미한다.
필자의 경우엔 데이터베이스 내의 테이블 구조와 데이터 모두 백업하기 위해 Dump Structure and Data를 선택하였고, 프로젝트의 규모가 작기 때문에 Export to Self-Contained File 옵션을 선택하였다. 그리고 딱히 필요한 오브젝트 유형이 없으므로 아무 선택도 하지 않았다.
만약 해당 파일을 이용해 데이터를 복원하고 싶다면 Server → Data Import를 선택한다.
Import Option 섹션에서 본인이 지정한 방식의 옵션을 선택하고, 복원하려는 백업 파일을 선택하면 된다. 그 후 Default Schema to be Imported to에서 복원하려는 스키마를 선택하면 된다.
만약 DOS창(MySQL Command Line Cline)에서 진행을 하고 싶다면 다음과 같은 명령어를 입력하면 된다.
mysqldump -u [USERNAME] -p[PASSWORD] [DATABASE_NAME] > [BACKUP_FILE.sql] // 백업
mysql -u [USERNAME] -p[PASSWORD] [DATABASE_NAME] < [BACKUP_FILE.sql] // 복원데이터 수정 (Visual Studio 연동)
이제 주어진 데이터를 수정하는 작업을 해보자. 수정해야 하는 것은 users 테이블에 있는 데이터 중 score에 해당하는 부분이다. MySQL에서 데이터를 수정하는 구문은 일반적으로 다음과 같다.
UPDATE table_name // 수정하려는 테이블의 이름
SET column1 = value1, column2 = value2, ... // 수정하려는 열과 해당 열의 새 값
WHERE condition; // 조건절
혹은 데이터를 삭제하고 새로 갱신하는 방법도 존재한다.
DELETE FROM table_name WHERE condition; // 레코드 삭제
INSERT INTO table_name (column1, column2, ... ) // 레코드 추가
VALUES (value1, value2, ... );
하지만 이러한 방법을 사용한다면, 데이터의 양이 방대해졌을 때 수정하기 번거롭다는 단점이 있다. 그래서 Visual Studio와 연동한 후, 새로운 점수를 할당하는 방법에 대해 알아보았다.
#include <iostream>
#include <string>
#include "mysql.h"
using namespace std;
namespace Config
{
static std::map<std::string, int> tier_score =
{
{"M", 44},
{"D1", 40},
{"D2", 36},
{"D3", 34},
{"D4", 32},
{"E1", 30},
{"E2", 28},
{"E3", 26},
{"E4", 24},
{"P1", 22},
{"P2", 20},
{"P3", 18},
{"P4", 16},
{"G1", 15},
{"G2", 14},
{"G3", 13},
{"G4", 12},
{"S1", 11},
{"S2", 10},
{"S3", 9},
{"S4", 9},
{"B1", 9},
{"B2", 9},
{"B3", 9},
{"B4", 9},
{"U", 0}
};
}
class MySQL
{
MYSQL _mysql;
public:
MySQL()
{
mysql_init(&_mysql);
mysql_set_character_set(&_mysql, "utf8");
if (!mysql_real_connect(&_mysql, "localhost", "root", "password", "TeamCreator", 3306, NULL, 0))
std::cerr << "MySQL과의 연동에 실패하였습니다: " << mysql_error(&_mysql) << "\n";
else
std::cout << "MySQL과의 연동에 성공하였습니다.\n";
}
~MySQL()
{
mysql_close(&_mysql);
}먼저 MySQL과의 기본적인 연동을 해주는 클래스를 작성하자. 기본적인 연동 방법은 다음 게시글을 참조하자. 필자의 경우 매핑할 맵을 namespace로 정의해 주었다.
void UpdateScore()
{
const std::string query
= "SELECT name, tier, line FROM users;";
if (mysql_query(&_mysql, query.c_str()))
{
std::cerr << "데이터를 가져오는 데 실패했습니다: " << mysql_error(&_mysql) << "\n";
return;
}
MYSQL_RES* result = mysql_store_result(&_mysql);
MYSQL_ROW row;사용자의 이름, 티어, 라인을 가져오는 쿼리문을 작성해서 쿼리를 실행한다. 그리고 쿼리의 결과를 result에 저장한다.
while ((row = mysql_fetch_row(result)))
{
std::string name = row[0];
std::string tier = row[1];
std::string line = row[2];
int newScore = 0;
if (Config::tier_score.find(tier) != Config::tier_score.end())
newScore = Config::tier_score[tier];
std::string updateQuery = "UPDATE users SET score=" + std::to_string(newScore) + " WHERE name='" + name + "' AND line='" + line + "';";
if (mysql_query(&_mysql, updateQuery.c_str()))
std::cerr << "점수 업데이트에 실패했습니다: " << mysql_error(&_mysql) << "\n";
}
mysql_free_result(result);
}
};결과의 각 행을 순회하며 처리하는데, 각 행의 데이터를 문자열 변수로 저장한다. 그리고 새로 할당할 점수를 초기화하고, 기존에 매핑해 놓은 맵에서 해당 티어의 점수를 찾는다. 만약 해당 티어의 점수가 있을 경우 새로운 점수를 그 값으로 업데이트한다.
int main()
{
MySQL mysql;
mysql.UpdateScore();
}메인 함수에서 해당 메서드를 호출하면 된다.
총합본
#include <iostream>
#include <string>
#include <map>
#include "mysql.h"
using namespace std;
namespace Config
{
static std::map<std::string, int> tier_score =
{
{"M", 44},
{"D1", 40},
{"D2", 36},
{"D3", 34},
{"D4", 32},
{"E1", 30},
{"E2", 28},
{"E3", 26},
{"E4", 24},
{"P1", 22},
{"P2", 20},
{"P3", 18},
{"P4", 16},
{"G1", 15},
{"G2", 14},
{"G3", 13},
{"G4", 12},
{"S1", 11},
{"S2", 10},
{"S3", 9},
{"S4", 9},
{"B1", 9},
{"B2", 9},
{"B3", 9},
{"B4", 9},
{"U", 0}
};
}
class MySQL
{
MYSQL _mysql;
public:
MySQL()
{
mysql_init(&_mysql);
mysql_set_character_set(&_mysql, "utf8");
if (!mysql_real_connect(&_mysql, "localhost", "root", "password", "TeamCreator", 3306, NULL, 0))
std::cerr << "MySQL과의 연동에 실패하였습니다: " << mysql_error(&_mysql) << "\n";
else
std::cout << "MySQL과의 연동에 성공하였습니다.\n";
}
~MySQL()
{
mysql_close(&_mysql);
}
void UpdateScore()
{
const std::string query
= "SELECT name, tier, line FROM users;";
if (mysql_query(&_mysql, query.c_str()))
{
std::cerr << "데이터를 가져오는 데 실패했습니다: " << mysql_error(&_mysql) << "\n";
return;
}
MYSQL_RES* result = mysql_store_result(&_mysql);
MYSQL_ROW row;
if (!result)
{
std::cerr << "데이터를 가져오는 데 실패했습니다: " << mysql_error(&_mysql) << "\n";
return;
}
while ((row = mysql_fetch_row(result)))
{
std::string name = row[0];
std::string tier = row[1];
std::string line = row[2];
int newScore = 0;
if (Config::tier_score.find(tier) != Config::tier_score.end())
newScore = Config::tier_score[tier];
std::string updateQuery = "UPDATE users SET score=" + std::to_string(newScore) + " WHERE name='" + name + "' AND line='" + line + "';";
if (mysql_query(&_mysql, updateQuery.c_str()))
std::cerr << "점수 업데이트에 실패했습니다: " << mysql_error(&_mysql) << "\n";
}
mysql_free_result(result);
}
};
int main()
{
MySQL mysql;
mysql.UpdateScore();
}데이터 수정 (참조 테이블)
만약 MySQL에서 참조 테이블을 만들어 매핑하고 싶다면 다음과 같이 참조 테이블을 만들어 테이블의 점수를 업데이트하는 쿼리를 작성해도 된다.
CREATE TABLE tier_score (
tier VARCHAR(10) PRIMARY KEY,
score INT
);
INSERT INTO tier_score (tier, score) VALUES
('M', 44),
('D1', 40),
...
// 나머지 티어와 점수 데이터를 삽입
;
UPDATE users
JOIN tier_score ON users.tier = tier_score.tier
SET users.score = tier_score.score;요약
MySQL 데이터 백업
1. 정의: MySQL 데이터베이스의 내용을 보존하는 방법
2. 특징
a. Data Export를 통해 데이터를 백업
b. Data Import를 통해 백업된 데이터를 불러옴
3. 주의 사항
- 백업 전에 현재 작업 중인 데이터 베이스의 상태를 확인하고, 복원 시 현재 상태와 백업된 데이터의 호환성을 확인해야 함
MySQL 데이터 수정
1. 정의: 기존에 저장된 데이터의 값을 변경하는 방법
2. 특징
a. UPDATE 문을 사용하여 데이터를 수정
b. 조건을 지정하여 특정 행 또는 열의 데이터만 수정 가능
3. 주의 사항
- 데이터 수정 전 원본 데이터의 백업을 권장하며, 특정 조건을 지정하지 않으면 모든 데이터가 수정될 수 있음
'CS > 데이터베이스' 카테고리의 다른 글
| [MySQL] Visual studio 연동 - 한글 인코딩 (0) | 2023.07.13 |
|---|---|
| [MySQL] MySQL 서버 설정 및 레코드 추가 (0) | 2023.07.09 |
| [MySQL] MySQL과 Visual Studio 연동하기 (0) | 2023.07.09 |