数据库mysql转为postgresql变动
因为⼯作需要，项⽬以前使⽤的mysql数据，现在需要更改为postgresql。
⼀、MYSQL转PG
1.1 同步表结构
利⽤navicat：⼯具->数据传输直接将mysql库->postgresql库，变动：
navicat转换后的sql，会丢失默认值
官⽹到⼀个⼯具，这个⼯具官⽅是付费的，貌似是专门做异构数据库转换的。限制是单表只能转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的格式区别
参考;
更多sql实现和标准sql区别可以查看。
四、如何将mysql列名从驼峰修改为下划线
由于历史原因，在创建数据表的时候，表字段采⽤了驼峰命名，如何将命名规则转为带有下划线的呢？
4.1 mysql查询所有列名
我们可以通过如下命令查询⼀个数据库下有哪些字段：
select * from lumns 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 = t(**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.__ute(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 lumns where table_schema='{}';".format(database)
mysql = MySqlTransform("127.0.0.1", 3306, "root", "123456",database)
cur = _query(sql)
res = cur.fetchall()
tabletablefor 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\" 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
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论