数据库mysql转为postgresql变动

因为工作需要，项目以前使用的mysql数据，现在需要更改为postgresql。

一、MYSQL转PG

1.1 同步表结构

利用navicat：工具->数据传输直接将mysql库->postgresql库，变动：

• navicat转换后的sql，会丢失默认值

官网找到一个工具Mysql转PostgreSQL，这个工具官方是付费的，貌似是专门做异构数据库转换的。限制是单表只能转50条数据，表无限制。

1.2 同步数据

使用navicat:工具->数据传输直接将数据从mysql同步到pg即可。

二、DDL变动

2.1 修改列

MYSQL:

ALTER TABLE table_name MODIFY COLUMN column_name datatype

pg：

ALTER TABLE table_name ALTER COLUMN  column_name TYPE datatype

2.2 类型变动

mysql->pg：

tinyint->int

datetime->timestamp

三、DML变动

3.1 SQL查询类型相关

postgresql  string的列不能用integer的值查，mysql可以自动转；

3.2 PG查询语句不区分大小写

数据表中字段如果有大写的话，pg查询语句是需要加双引号的，因此创建数据表的时候字段最好采用下划线的方式：

select id,"loginName" from user;

3.3 PostgreSQL与MySQL的日期类型DATE/date的格式区别

参考;PostgreSQL与MySQL的日期类型DATE/date的格式区别

更多sql实现和标准sql区别可以查看https://www.runoob.com/sql/sql-tutorial.html。

四、如何将mysql列名从驼峰修改为下划线

由于历史原因，在创建数据表的时候，表字段采用了驼峰命名，如何将命名规则转为带有下划线的呢？

4.1 mysql查询所有列名

我们可以通过如下命令查询一个数据库下有哪些字段：

select * from information_schema.columns where table_schema='blog'

结果如下：

4.2 编写批量修改脚本

我们可以编写python脚本，根据表名、旧的字段名，属性信息，以及新的列名，然后生成批量修改列名的脚本。每一条修改语句类似：

ALTER TABLE `table_name` CHANGE `$old_column_name` `new_column_name` VARCHAR(40) null default "xxx" comment "comment"

需要注意的是：

该条语句不会更改原有的索引。

python脚本如下：

import pymysql


class MySqlTransform(object):
    """
    将mysql数据库auth库中的所有表中驼峰格式的字段转为下划线命名
    """
    def __init__(self, host, port, user, password,database):
        # 数据库配置
        self.__conn = None
        self.__MYSQL_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.__MYSQL_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_supper_index = None
    for index, char in enumerate(text):
        if char.isupper():
            if index != 0 and (last_supper_index == None or last_supper_index + 1 != index):
                lst.append("_")
            last_supper_index = index
        lst.append(char)

    return "".join(lst).lower()


def transform_sql_column(item=None):
    database = "blog"
    sql = "select * from information_schema.columns where table_schema='{}';".format(database)

    mysql = MySqlTransform("127.0.0.1", 3306, "root", "123456",database)
    cur = mysql.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 = '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__':
    transform_sql_column()

 输出结果如下：

"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 "权限code";
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 code 0