【学习笔记】【DB】Oracle数据迁移Mysql(更新中...)

故沉

已于 2023-02-23 22:44:58 修改

阅读量3.3k

点赞数 3

分类专栏： Dadabase 学习笔记文章标签：数据库学习 oracle mysql database

于 2022-12-31 22:20:18 首次发布

本文链接：https://blog.csdn.net/jesmine_gu/article/details/128442757

版权

学习笔记同时被 2 个专栏收录

16 篇文章 1 订阅

订阅专栏

Dadabase

1 篇文章 0 订阅

订阅专栏

前言

好记性不如烂笔头，就算是自己做过的东西，时间久了也会有些模糊。刚开始写这篇文章的时候才刚开始，经过一个春节，现在基本收尾。所以想完整地梳理和记录一下迁移过程中碰到的问题，不谈业务逻辑，仅作个人小结用（不涉及任何项目细节）

参考内容随文附上。如有错误之处，欢迎指正。

正文

大致梳理一下迁移过程中涉及到的部分，分模块慢慢整理更新。

想了很久不知道怎么安排行文，最开始只是为了记录我在做自己那部分时遇到的问题，后来把整个流程跟了下来，又想把整个流程形成案例分享，这样的话，扩展出去的东西就太多了(每一个扩展的知识点深入理解学习，展开讲讲都够一篇博文的)……

一、准备工作

分Sheet整理出Oracle数据库中涉及到的全部表、视图、物化视图、存储过程、触发器、函数、定时任务、涉及业务改造迁移(略)。

这样有几个好处：

方便团队分工
便于进度反馈和跟踪
保证测试验证的有序
确保迁移的完整性

做好生产环境和测试环境的一致性确认，做好数据库安全基线的检查。这方面已经吃过亏了，谨记！

讨论迁移过程中可能出现的问题及解决方案，比如MySQL没有物化视图的概念，如何实现？比如相对Oracle而言，存储过程不适合在MySQL实现，如何落地转换？比如MySQL不存在dblink的功能。比如，可能产生的查询效率低下，对查询实时性的要求等等……

最后确定时间节点。

二、表结构迁移

主要是字段类型的不同，以及对字符集、排序规则和Unicode编码的配置。

Oracle	MySQL
varchar2	varchar
number	int/bigint/decimal()
date	datetime
enable	空

两个问题：

字段长度超过3072的无法建立索引
全部表字段总长度超过65535的无法建表

针对以上两个问题，考虑把varchar(4000)等长字段类型视情况改为text/longtext/blob等类型规避解决。

针对字符集编码为utf8mb4，核对码为utf8mb4_general_ci。在后面验证迁移数据是否一致性的时候才发现一个大坑。

部分视图刷新以后的数据量差了几条到十几条不等，单看逻辑没有任何问题，实在不知道从何下手，选了个表数据在一百条左右的，把Oracle和MySQL拉出来比对，发现对涉及到中英文括号的，MySQL中自动视为一条。对另外出现同样问题的表检查均为对大小写、中英文的敏感导致的数据量差异。把涉及核对码改为utf8mb4_general_bin后解决，(存个疑问:针对统一表中存在不同核对码是否会影响数据库操作，目前没发现)。

拓展一：数据库约束

enable这个字段转换的时候直接跳过了，设为空。

其实并不非常理解，查到是用于控制约束，控制表的约束是禁止还是激活状态。

disable:禁止状态，相当于该列没有约束，使用disable默认为Novalidate

enable:激活状态，默认新创建的约束状态是激活状态，使用enable默认为Validate

有一种说法：enable和disable是对未来数据的约束。这里拓展一下，谈谈数据库的约束。

比较常见和常用的四种约束：主键约束(primary key)[相当于unique+not null]、唯一性约束(unique)、非空约束(not null)、外键约束(foreign key)

还有一种对我而言比较新鲜的：检查约束(check)

参考文献一中提到：

很多时候由于业务需要，比如我们有大量的历史数据，需要和现有数据合并，当前表存在数据库约束（如非空约束），而这些历史数据又包含违背非空约束的数据行，为了避免导入时由于违反约束而导入失败，我们通过调整约束状态来达到目的。

数据库约束有两类状态

　　启用/禁用（enable/disable）：是否对新变更的数据启用约束验证

　　验证/非验证 (validate/novalidate) ：是否对表中已客观存在的数据进行约束验证

这两类四种状态从语法角度讲可以随意组合，默认是 enable validate

我们需要上传大量违反非空约束的历史数据（从业务角度讲这些数据不会造成系统功能异常），可以临时将约束状态转为 disable novalidate，以保证这些不合要求的数据导入表中
alter table emp modify constraint emp_ename_nn disable novalidate;
在数据导入完成之后，我们再将约束状态转为enable novalidate 以确保之后添加的数据不会再违反约束
alter table emp modify constraint emp_ename_nn enable novalidate;

上面的解释可以说很清楚了，再举个通俗一点的例子：

建表设置主键的时候，会默认主键字段是唯一且非空，此时disables去掉主键的约束(alter table test disable constraint id)，插入主键值为空的数据(insert into test values(null))是可行的；再用enable改变表约束时(alter table test enable constraint id)会发现，不成功。

因为默认约束validate，也就是会对历史数据做检查，新加的enable检测到要加上主键约束的这个字段存在非空的情况，此时想要enable就必须加上novalidate约束(alter table test ENABLE novalidate constraint id)，改变表约束成功，再次插入主键为空的数据(insert into test values(null))报错。

总结就是这两类约束要综合考虑。

参考文献：

拓展二：MySQL校对规则

字符集和小队规则视一对多的关系，每个字符集都有一个默认的校对规则，两者相互依赖。

SHOW VARIABLES LIKE 'COLLATION\_%'

"collation_connection" "utf8_general_ci" 连接数据库时使用的校对规则
"collation_database" "utf8mb4_0900_ai_ci" 创建数据库时使用的校对规则
"collation_server" "utf8m4_bin" MySQL服务器使用的校对规则

校对规则的命令约定：

以校对规则对印度个字符集名为开头
以国家名居中(或者general)
ci(大小写不敏感)、cs(表示大小写敏感)、bin(按二进制编码值比较)结尾

三、内置函数差异

1、Oracle的Decode函数

decode(expression, value, result1, result2)，理解为if expression=value输出得到value1，否则输出得到value1；对于有多个参数的依此类推decode(expression, value1, result1, value2, result2, ……, default)。

MySQL中根据逻辑改写就是if(expression=value, result1, result2)。

举个例子：

Oracle：select decode(sex, 1, '男‘, 2, '女’, ‘其他’) from test

MySQL：select if(sex=1, '男', if(sex=2, ‘女’, '其他‘)) from test

2、Oracle的NVL和NVL2

nvl(s1, s2)，理解为若s1为空，则返回s1否则返回s2；MySQL中对应为ifnull(s1, s2)

拓展一下nvl2(s1, s2, s3)，理解为如果s1为空，则返回s2否则返回s3；MySQL中对应实现为if(isNull(s1), s2, s3)

3、Oracle的full join/(+)

Oracle中的full join等同于MySQL中的left join+union+right join

Oracle可以使用(+)做外连接，MySQL中没有这样的用法。

Oracle：select * from a, b where a.id=b.id(+)；select * from a, b where a.id(+)=b.id

MySQL：select * from a left join b on a.id=b.id；select * from a right join b on a.id=b.id

拓展三：oracle的表连接方式

内连接（inner join）、外连接（outer join）、全连接（full join）

内连接inner join，比如select * from A inner join B on A.field1=B.field2，另外两种方式也是一样的，select * from A join B on A.field1=B.field2，select * from A,B where A.field1=B.field2，内连接取得是两张表的并集，也就是在A中也有，在B中也有的数据才能查询出来。

外连接outer join，可分为左外连接left outer join和右外连接right outer join。

select * from A left (outer) join B on A.id = B.id，left join以左表A为准，左表A的数据会全部显示，若左表有数据右表没有数据，则显示数据显示为空；

select * from A right (outer) join B on A.id = B.id，right join类似的，以右表B为准，右表B的数据会全部显示，若左表没有数据右表有数据，则显示数据显示为空

全连接full join，语法为full join ... on ...，全连接的查询结果是左外连接和右外连接查询结果的并集Oracle中的实现：select * from a full join b on a.id = b.id

MySQL中的实现：select * from a left join b on a.id = b.id union select * from a right join b on a.id = b.id

拓展四：联合查询union

UNION用于合并两个或多个select语句的结果集，并消去表中的重复行。其内部的select语句必须拥有相同数量行、相同列顺序、相似数据类型

UNION ALL则不消除重复列

4、Oracle的to_char/to_number

to_char将数值型或者日期型转化为字符型，这个函数可以拓展开很多东西。

MySQL中如果是把数值转化为字符，可用a = cast(b as char)或者a = concat(b, '')；如果是把日期转化为字符型，可用date_format(date/time, format)

to_number将字符串变为数值类型，MySQL中可用a = cast(b as SIGNED INTEGER)

在一些博客中也看到有自定义去实现的，这里不赘述。

5、Oracle的nulls first/last

把空值放在哪里，MySQL中有两种实现方法

方法一：

if(isnull(range),1,0)表示把null值放在最后，1和0互换则表示放在最前面。

方法二：
nulls first: order by NOT ISNULL(field);
nulls last: order by ISNULL(field);

6、row_number() over (partition by ... order by ... )

这个用法网上的MySQL改写很多，参考一是搜出来的第一个，感觉写的也是挺清楚的。对Oracle的解析，参考二写的很详尽，不做搬运了，看透之后那自己的话写下来帮助理解就好了。

从字面含义可以大致参测到，这个函数是进行分组排序以后的一个计数。在实际Oracle的使用中，第二个括号里的形式主要是纯排序(必须有 ORDER BY 语句)或者排序分组

最开始使用的MySQL5里面没有相关语法，后来换成MySQL8以后就不需要在自己转换了。

Orcale:
select range,appci ,row_number() over (partiotion by range order by appci) as rank from app_view_info

MySQL5-1:
select a.*, if(@p=a.range, @rank:=@rank+1,@rank:=1) as rank, @p:=a.range, from
(select range, appci from app_view_info where range id not null order by range, appci) a,
(select @p:=null, @rank:=0) b

MySQL5-2：
select a.range, a.appci, if(@p=a.range, @rank:=@rank+1,@rank:=1) as rank, @p:=a.range
from (select @p:=null, @rank:=0)b, app_view_info a
order by if(isnull(range),1,0),range, appci

参考：

7、||和concat

Oracle中连接字符串这两种方式都有，||可以连接多个字符串，concat()只能连接两个字符串。

MySQL中的连接多个字符串用concat，有一个注意的点是：如果有任何一个参数为null，则返回值为null

8、instr

instr(源字符串, 目标字符串)，返回目标字符串在源字符串的位置。比如select instr('hello','l') from test，最后返回3。这里值得注意的是，数据库里面的编号是从1开始的。

instr(源字符串, 目标字符串, 起始位置, 匹配序号)，类似的举个例子，select instr('hello','l',2,2) from test，最后返回4。从第二个位置开始找，第二次匹配到‘l’的位置。

因为转换的原意是判断instr(st1, st2)>0，就直接想到用模糊匹配，只要判断存在字符就行了。后来发现MySQL中本身也有现成的函数

locate(目标字符串，源字符串)，其含义一样，如果源字符串中不存在目标字符串，则返回0；存在则返回目标字符串第一次出现的位置。还有一种用法，locate(substr, str, pos)，从pos位置开始查询。

position(目标字符串 in 源字符串)，用法同locate一样

instr(源字符串, 目标字符串)，用法同locate，注意参数顺序

至于模糊匹配的具体知识就参看参考文献2吧，不做搬运了。

参考文献：

9、with...as()

with...as一般放在开头，可以理解为是sql片段或者一张临时表，达到一次解析多次使用的效果，其后必须紧接着查询语句。可以很好的提高效率和代码简洁度。

这个关键字是在改写视图的时候碰到的，发现MySQL中建立视图不支持临时表，当时没想到怎么解决，就用了笨方法，直接把sql语句提出来覆盖每个用到的地方。这里存个疑问

顺便拓展一下这个关键字的用法吧

-- 抽取出来多次使用
-- 单个别名
with temp as (select id from test where name='test')
select * from temp

-- 多个别名
with
   tmp as (select * from tb_name),
   tmp2 as (select * from tb_name2),
   tmp3 as (select * from tb_name3)
select * from tmp, tmp1, tmp2 where tmp.id = tmp2.id and tmp2.name = tmp3.name

-- 在union中使用
with
   tmp as (select * from tb_name),
   tmp2 as (select * from tb_name2),
   tmp3 as (select * from tb_name3)
select * from tmp 
union all
select * from tmp2
union all
select * from tmp3

10、listagg/xmlagg

这两个都是Oracle里的拼接函数，先说两者的区别：前者listagg只适用于拼接以后的字段长度(varchar)小于4000的，超过4000的话，数据库查询会报错”ORA-01489:字符串连接的结果过长“，这时候就可以使用xmlagg了。

对于mysql相同的效果实现，可以使用group_concat()函数。具体看参考文献1和2。

参考文献：

四、功能实现

1、物化视图的改造

首先理解几个问题：

什么是物化视图
物化视图和普通视图的区别是什么

借用百度百科上的解释：

物化视图是包括一个查询结果的数据库对象，它是远程数据的的本地副本，或者用来生成基于数据表求和的汇总表。物化视图存储基于远程表的数据，也可以称为快照（类似于MSSQL Server中的snapshot，静态快照）。对于复制，物化视图允许你在本地维护远程数据的副本，这些副本是只读的。如果你想修改本地副本，必须用高级复制的功能。当你想从一个表或视图中抽取数据时，你可以从物化视图中抽取。对于数据仓库，创建的物化视图通常情况下是聚合视图，单一表聚合视图和连接视图。（这个是基于本地的基表或者视图的聚合）。物化视图，说白了，就是物理表，只不过这张表通过oracle的内部机制可以定期更新，将一些大的耗时的表连接用物化视图实现，会提高查询的效率。当然要打开查询重写选项

再来说一说视图，还是百度百科：