从oracle到mysql模型转换的自动化实现

最新推荐文章于 2024-10-07 12:53:02 发布

鲸品堂

最新推荐文章于 2024-10-07 12:53:02 发布

阅读量3.7k

点赞数 1

文章标签：开发语言

本文链接：https://blog.csdn.net/whalecloud/article/details/127734047

版权

众所周知，同为IT民工，但是只有程序员称之为码农，因为搞开发的其实有很多重复劳动，至于交付实施则重复劳动更多。所以，相对没那么多重复劳动，高瞻远瞩的设计同学，虽然头上的发量堪忧，头上的包却也比较多，但是还是站在了民工金字塔的顶端（众所周知，领导不是IT民工，是包工头），大家还是比较羡慕的。

01 开端

然而在轰轰烈烈的去O运动中，设计同学也开始为大量的重复劳动发愁了。

发愁的原因，是因为去O本身就是一个数据库转型的动作，有太多从oracle语法转向其他数据库语法的工作，如建表语句、程序SQL，存储过程等等，而这种工作，基本都是重复性的搜索和替换动作（键盘“R”键也需要加固了），大部分工作，都需要设计同学的参与，尤其是建表部分。特别是稍大点的系统，动则就上千张表，如何能够快速而准确的转为新数据库（我们一般是mysql或pg），想想都头疼。

本文讨论怎么将oracle建表语句转换为mysql的建表语句。这算是开了个头，写了一些段子，不符合技术类文章的风格，强行辩护一下，是因为这活实在太无聊了。

02 现有可用工具

可能没有实际处理过这种工作的同学会觉得数据库都大同小异，标准SQL已经多少年了，还存在这么多需要翻译的模型和SQL吗？实际上，在我看来，标准SQL只有每条语句的开头的第一个单词是标准的（select,insert,delete），其他的都有很多夹带的“私货”，这一方面是标准SQL其实是一个很松散的标准，没法把所有的SQL都标准化；另外一方面，其实也是每个数据库都不想被标准化，特别是占据大市场份额的ORACLE。所以，去O远比想象的要难得多。

当然，现在SQL翻译的软件很多，也很强大，比如powerdesigner，又比如我强烈推荐的如下SQL翻译网站：http://www.sqlines.com/online

这些软件都可以将各个数据库之间的建表语句互转，对于小规模的转换，基本够用，但是对于大规模的，还是存在较多难以满足的地方，主要有如下几个方面：

oracle导出建表语句本身也不够标准。Oracle导出的建表语句，就不是那么“干净”，不符合标准SQL的部分比较多，需要逐个表修改。

很多字段类型并不是一对一的关系，有些时候要分析实际数据才知道到底需要转换为何种类型，比如number类型，在mysql中存在多种类型与之对应，比如tinyint ，smallint,mediumint ，bigint，decimal等等，在上面的图中，其实可以看到将number转为double实际是不准确的。

目的数据库对于可执行的ddl语句可能存在限制。如teledb，存在限制说必须有表必须要有主键、字段必须要有描述、索引等要写在建表语句中等诸多限制，而转出来的SQL往往不那么符合要求。

费用，你懂的。

总的来说，就是各种不好用，虽然减少了部分劳动，但是还是进行各种手工操作，只能算是个半自动产品。所以，为了更快地提高效率，减少重复劳动，还是自己写个小工具来实现比较好。

嗯，我就写了一个。以下的部分，是介绍这个工具的实现以及一些经验的总结，供有需要的同学参考。鉴于oracle本身的复杂性以及我的知识有限，错误之处在所难免，请各位大拿批评指正（诚恳的、谦虚的语气）。

03 自研发工具的整体实现逻辑

本来首先想的，也是先通过get_ddl函数将表和索引语句导出，然后通过一些SQL语法分析引擎来实现，并解决前文说到的不足的问题。找了一下主流的分析引擎了解了一下，发现第一较为复杂，学习的时间会比较长；第二感觉这个事情用分析引擎也属于高射炮打蚊子，未必好用。其实建表语句无非就那么几个关键点，拿oracle系统表完全可以提炼出来。

以一个mysql风格的建表语句为例，主要由如下几个部分组成（/**/部分）：

CREATE TABLE trans_table /*表名*/ (`table_id`/*字段名*/   int /*字段类型，长度*/    NOT NULL /*是否可空*/   COMMENT 'ID配置生成，不需要填值' /*描述*/ ,`data_source_name`  varchar(100)        COMMENT '数据源' ,`table_name`  varchar(50)        COMMENT '表名' ,`table_desc`  varchar(1024)        COMMENT '描述' ,`table_type`  tinyint       COMMENT '表类型--预留' ,`clean_way`  tinyint  DEFAULT 0 /*默认值*/ ，primary key (`table_id`) /*索引和主键部分*/  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

这里面，表相关主要有表名、字段名、字段类型、是否可空、默认值，索引相关主要有索引名、索引字段、是否主键索引等信息，可以看到，并不复杂。于是就果断的选择了直接用oracle系统表提炼出建表语句的各个部分,然后再拼接起来实现（相当于实现一个异库get_ddl函数）。

这里没有太多复杂的流程问题，相信各位码农同学们根本不需要我画什么流程图。嗯，我知道你要什么，主要是需要将涉及的系统表整理出来，上菜：

与之对应的系统视图，列表如下：

04 转换难点：字段类型转换

确定了目标语言的风格以及要素之后，最核心的东西，当然是字段类型如何转换。

熟悉oracle数据类型的同学知道，oracle在弄字段类型时看上去比较简单粗暴，但是mysql做的比较精细。但这并不是oracle偷懒，恰恰相反，其中原因，是oracle比较勤快。高情商的说法，是客户需求的推动，低情商的说法，就是为了掌握更多的数据库市场话语权，把更多的“业务”逻辑放在了数据库自身（对于“数值”类型的处理就能看出来）。而mysql、pg等数据库理念则反之，字段类型的定义与程序语言中数据类型的定义符合度更高，对于业务回归到程序本身处理更加友好。

当然以上纯属个人观点，两者取舍之间，也说不上谁对谁错。

正因为如此，在处理oracle与其他数据库的映射关系时，大部分时候都是一对多的关系，所以转起来比较麻烦，不仅需要参考字段类型本身，还需要明确精度，甚至有时候需要参考实际数据的值。

我整理了ORACLE常用的数据类型与mysql的对应关系，如下：

最复杂莫过于number(p,s)，以这个举例来说明如何转换。

p是指有效位数，s是小数位，需要以p和s的实际取值来确定mysql的字段类型：

当p,s均为空时，此时需要参考表内的实际数值或者根据具体业务来确定转换的类型。参考实际数值时，可以采用获取表中max(length(column_name))-1(包含一个小数点)来确定p，用max(length(column_name)-instr(column_name ||'.','.'))(查找小数点后的位数)来确定s的值。

另外，一定要注意mysql的int(2)这种写法，并不是smallint的别名，不管是int(2)还是int(8)，实际都是int。

除了最复杂的number类型以外，其他字段还有几点小建议：

除非要修改设计以支持秒以下级别的精度，Date最好转换为datetime而不要转为TIMESTAMP，因为TIMESTAMP存在2038年问题。
ORACLE FLOAT不要转为MYSQL FLOAT，要转为DOUBILE避免精度丢失。
处理char时，要注意最大的长度。
BLOB，CLOB转换时，MYSQL也有众多不同长度的类型选择，最好根据实际业务需要和业务数据来判断选择何种长度。

05 其他要素：主键、索引、外键

首先还是提醒特别要注意目的数据库的建表风格问题。现在使用的Mysql，大部分为mysql集群产品，对于使用会存在一些限制，比如主键必须存在，建主键的语句必须放到建表语句“里面”等等。所以，在转换之前，要先考虑目的数据库的建表限制以及语句风格。根据风格确定模板后，再进行转换。

其次再说说oracle和mysql对于索引和主键上的一些差异。

主键判断：在索引是否主键的判断上，oracle使用约束视图ALL_CONSTRAINTS的约束类型CONSTRAINT_TYPE=“P”来判断，转换时，应该先查找all_index来查找所有索引，再根据ALL_CONSTRAINTS判断是否主键。值得一提的是，mysql只需要通过索引的索引名称判断即可，即索引名称为“PRIMARY”的为主键，其他则为普通索引。

主键名称：这两者的主键的创建语法，也有差异，Oracle主键的创建语法，可以有名字，也可以没名字，如果没有名字，则系统会自动加上一个名字；但是mysql则不同，就算是创建时有名字，到了mysql系统视图中，也会被“PRIMARY”替换。所以，mysql创建主键时，建议是不写名字。

主键序列：Mysql主键支持自增列（AUTO_INCREMENT），而ORACLE则需要先定义序列，再设置默认值，这一点Mysql比较方便，当遇到在oracle中主键默认值为序列时，转为自增长处理，注意把自增列数据类型设置为无符号型。当在oracle中没有主键时，推荐增加一个自增列作为主键。

索引名称：Oracle索引名称是owner下唯一，而mysql索引名称是表下唯一，也就是说，原有oracle一些分表，比如分月等索引带月份的，在mysql下可以不带月份。

联合索引字段顺序：在ALL_IND_COLUMNS中查找索引字段时，注意按照COLUMN_POSITION排序避免准换后丢失顺序。

外键：建议直接丢弃不用。主要是因为现在用的Mysql基本都是集群的mysql，分库分表无法使用外键，另外去O的主流数据库设计，也会推荐尽量减少数据库层面的外键关联，转而交由业务逻辑实现。