업무 요구사항에 따라, 프로젝트에서 기존에 사용하던 MySQL 데이터베이스를 PostgreSQL로 변경해야 하는 상황이 발생했습니다.
1. MySQL에서 PostgreSQL로 전환
1.1 테이블 구조 동기화
Navicat 도구를 활용하여: 도구 → 데이터 전송을 통해 MySQL 데이터베이스를 PostgreSQL 데이터베이스로 직접 전환할 수 있습니다. 이 과정에서 다음과 같은 변화가 발생합니다:
- Navicat로 변환된 SQL에서 기본값(default value)이 유지되지 않을 수 있습니다
공식 웹사이트에서 MySQL을 PostgreSQL로 변환하는 도구를 찾았습니다. 이 도구는 공식적으로 유료이며, 이종(heterogeneous) 데이터베이스 전환을 전문으로 하는 것으로 보입니다. 단점으로는 단일 테이블당 50개의 데이터만 변환 가능하나 테이블 수에는 제한이 없습니다.
1.2 데이터 동기화
Navicat를 사용하여: 도구 → 데이터 전송 기능을 통해 MySQL에서 PostgreSQL로 데이터를 직접 동기화할 수 있습니다.
2. DDL(Data Definition Language) 변화
2.1 열 수정
MySQL:
ALTER TABLE 테이블이름 MODIFY COLUMN 열이름 데이터타입
PostgreSQL:
ALTER TABLE 테이블이름 ALTER COLUMN 열이름 TYPE 데이터타입
2.2 데이터 타입 변화
MySQL → PostgreSQL 변환 시 주요 타입 변환:
tinyint → int datetime → timestamp
3. DML(Data Manipulation Language) 변화
3.1 SQL 쿼리 타입 관련
PostgreSQL에서는 문자열(string) 열을 정수(integer) 값으로 조회할 수 없습니다. MySQL은 자동 형변환이 가능하지만, PostgreSQL은 명시적인 형변환이 필요합니다.
3.2 PostgreSQL 쿼리문 대소문자 구분
데이터 테이블의 필드명에 대문자가 포함된 경우, PostgreSQL 쿼리문에서는 큰따옴표(")를 사용해야 합니다. 따라서 데이터 테이블을 생성할 때 필드명은 밑줄(underscore) 방식을 사용하는 것이 좋습니다:
select id,"loginName" from 사용자;
3.3 PostgreSQL과 MySQL의 날짜 타입 DATE/date 형식 차이
PostgreSQL과 MySQL의 날짜 타입 DATE/date 형식 차이에 대한 자세한 내용은 참고 자료를 확인하세요.
추가 SQL 구현 및 표준 SQL 차이점은 https://www.runoob.com/sql/sql-tutorial.html 에서 확인할 수 있습니다.
4. MySQL 열명을 카멜케이스에서 언더스코어 형식으로 변환하는 방법
역사적인 이유로 데이터 테이블 생성 시 카멜케이스(camelCase) 명명 규칙을 사용한 경우, 언더스코어(underscore) 형식으로 변환하는 방법은 다음과 같습니다.
4.1 MySQL에서 모든 열명 조회
다음 명령을 사용하여 데이터베이스의 모든 필드를 조회할 수 있습니다:
select * from information_schema.columns where table_schema='블로그'
4.2 배치 수정 스크립트 작성
테이블 이름, 기존 열 이름, 속성 정보, 새로운 열 이름을 기반으로 Python 스크립트를 작성하여 배치로 열 이름을 수정할 수 있습니다. 각 수정 문장은 다음과 유사합니다:
ALTER TABLE `테이블이름` CHANGE `기존열이름` `새열이름` VARCHAR(40) null default "xxx" comment "주석"
주의할 점:
이 문장은 기존 인덱스를 변경하지 않습니다.
Python 스크립트 예시:
import pymysql
class DBTransform(object):
"""
MySQL 데이터베이스 auth 데이터베이스의 모든 테이블에서 카멜케이스 형식의 필드를 언더스코어 명명으로 변환
"""
def __init__(self, host, port, user, password,database):
# 데이터베이스 설정
self.__conn = None
self.__DB_CONFIG = {
'user': user,
'port': port,
'host': host,
'password': password,
'database': database
}
self.__cur = self.__get_connect()
def __get_connect(self):
# 연결 설정
self.__conn = pymysql.connect(**self.__DB_CONFIG)
# cursor() 메소드로 작업 커서 획득
cur = self.__conn.cursor()
if not cur:
raise (NameError, "데이터베이스 연결 실패")
else:
return cur
def __del__(self):
"""
소멸자
"""
if self.__conn:
self.__conn.close()
def exec_query(self, sql):
"""
쿼리 실행
"""
self.__cur.execute(sql)
return self.__cur
# 카멜케이스를 언더스코어로 변환 ID=>id loginName => login_name
def camel_to_underline(text):
lst = []
last_upper_index = None
for index, char in enumerate(text):
if char.isupper():
if index != 0 and (last_upper_index == None or last_upper_index + 1 != index):
lst.append("_")
last_upper_index = index
lst.append(char)
return "".join(lst).lower()
def generate_column_sql(item=None):
db_name = "블로그"
sql = "select * from information_schema.columns where table_schema='{}';".format(db_name)
db = DBTransform("127.0.0.1", 3306, "root", "123456",db_name)
cur = db.exec_query(sql)
res = cur.fetchall()
for index, item in enumerate(res):
table_name = item[2]
old_column_name = item[3]
new_column_name = camel_to_underline(old_column_name)
column_type = item[15]
column_null = 'null' if item[6] == 'YES' else 'not null'
column_defult = '' if item[5] is None else 'default {}'.format(item[5])
column_comment = item[19]
if old_column_name == new_column_name:
continue
# SQL 생성
sql = 'alter table {} change column {} {} {} {} {} comment "{}";'.format(table_name,old_column_name,new_column_name, column_type, column_null, column_defult, column_comment)
print(sql)
if __name__ == '__main__':
generate_column_sql()
실행 결과는 다음과 같습니다:
"D:\Program Files\python\python.exe" E:/개인학습코드/python/sql/mysql_main.py
alter table t_blog_article change column viewCount view_count int null default 0 comment "";
alter table t_blog_article change column createdAt created_at datetime null comment "";
alter table t_blog_article change column updatedAt updated_at datetime null comment "";
alter table t_blog_category change column articleId article_id int null comment "";
alter table t_blog_comment change column articleId article_id int null comment "";
alter table t_blog_comment change column createdAt created_at datetime null comment "";
alter table t_blog_comment change column updatedAt updated_at datetime null comment "";
alter table t_blog_comment change column userId user_id int null comment "";
alter table t_blog_ip change column userId user_id int null comment "";
alter table t_blog_privilege change column privilegeCode privilege_code varchar(32) not null comment "권한 코드";
alter table t_blog_privilege change column privilegeName privilege_name varchar(32) null comment "권한 이름";
alter table t_blog_reply change column createdAt created_at datetime null comment "";
alter table t_blog_reply change column updatedAt updated_at datetime null comment "";
alter table t_blog_reply change column articleId article_id int null comment "";
alter table t_blog_reply change column commentId comment_id int null comment "";
alter table t_blog_reply change column userId user_id int null comment "";
alter table t_blog_request_path_privilege_mapping change column urlId url_id int null comment "요청 경로 ID";
alter table t_blog_request_path_privilege_mapping change column privilegeId privilege_id int null comment "권한 ID";
alter table t_blog_role change column roleName role_name varchar(32) not null comment "역할 이름";
alter table t_blog_role_privilege_mapping change column roleId role_id int not null comment "역할 ID";
alter table t_blog_role_privilege_mapping change column privilegeId privilege_id int not null comment "권한 ID";
alter table t_blog_tag change column articleId article_id int null comment "";
alter table t_blog_user change column disabledDiscuss disabled_discuss tinyint(1) not null default 0 comment "금지: 0=금지 안함, 1=금지";
alter table t_blog_user change column createdAt created_at datetime null comment "";
alter table t_blog_user change column updatedAt updated_at datetime null comment "";
alter table t_blog_user_role_mapping change column userId user_id int not null comment "사용자 ID";
alter table t_blog_user_role_mapping change column roleId role_id int not null comment "역할 ID";
Process finished with exit 0