目录
需求描述
由于客户需要，需要将项目原本用的Oracle数据库转为MySQL，项目涉及到和数据库相关的也都需要进行相应的修改
本文主要是记录将Oracle数据库中的数据转为MySQL数据库的数据的过程。
表结构和数据转移
表结构和数据的转移是最基本的，这里使用到的工具是Navicat Premiium，可以使用其自带的工具进行数据传输
首先分别简历Oracle的数据库连接和MySQL的数据库连接，然后再用工具进行数据传输，如下图
从上面可以看到由于数据库不同，所以只能进行表之间的数据传输，而视图，函数，存储，触发器等由于语法不同是无法进行直接传输的，因此这些不能直接传输的就需要进行修改，根据MySQL语法要求进行改写、创建，下面会详细讲。
表产生问题及解决方案
- 由于MySQL中VARCHAR类型的长度最大为255，因此在传输时如果Oracle那边字段长度很大的话会导致报错
解决方案：根据实际使用中如果不需要这么大的长度可以讲Oracle的对应长度改小点，或者使用其它类型，如TEXT类型
- 迁移过程中如果报下面这种错，是因为MySQL限制了每次执行插入操作的最大值，需要的话可以将最大值进行修改
1153 - Got a packet bigger than 'max_allowed_packet' bytes With statement解决方案：使用MySQL命令窗口执行以下内容来修改信息报的大小，如增大到16M
set global max_allowed_packet = 1024*1024*16- 其它类型的报错的话可以根据报错提示进行修改，在此就不一一列举了。
- 在创建数据库时如果默认排序规则选择了 utf8_general_ci 这种，则在查询和导入时会不区分大小写，导致插入时会因为重复而报错，在查询时会返回多行而报错，如果不注意的话造成的结果会和预期不符，以及导致系统相应功能模块崩溃。
解决方案：可以使用 utf8_bin 这种排序规则，这样就会大小写敏感，区分大小写了。
- 在迁移过程中默认值会丢失，需要手动再给赋个默认值，MySQL默认值只支持默认字段类型的值，不支持函数等
解决方案：为了确保批量修改导致表出错而崩溃，这里选择在Oracle中得到相关数据后在MySQL中进行手动修改
字段默认值处理
以下脚本是获取表的字段的默认值信息，根据需要可以进行相关修改
-- 以下脚本是用来给MySQL数据库的表字段赋默认值的
BEGIN
DBMS_OUTPUT.ENABLE(10000000000);
FOR ITEM IN (SELECT T.COLUMN_NAME,
T.TABLE_NAME,
T.DEFAULT_LENGTH,
T.DATA_DEFAULT,
T.DATA_TYPE
FROM USER_TAB_COLUMNS T
WHERE T.DEFAULT_LENGTH IS NOT NULL) LOOP
IF ITEM.DATA_TYPE = 'NUMBER' OR ITEM.DATA_TYPE = 'FLOAT' THEN
DBMS_OUTPUT.PUT_LINE('ALTER TABLE ' || ITEM.TABLE_NAME ||
' alter column ' || ITEM.COLUMN_NAME ||
' set default ' || ITEM.DATA_DEFAULT || ';');
ELSIF (ITEM.DATA_TYPE = 'DATE') THEN
IF INSTR(ITEM.COLUMN_NAME, 'LASTMODIFIEDON') > 0 THEN
DBMS_OUTPUT.PUT_LINE('ALTER TABLE ' || ITEM.TABLE_NAME ||
' MODIFY COLUMN ' || ITEM.COLUMN_NAME ||
' datetime(0) NULL DEFAULT CURRENT_TIMESTAMP COMMENT "最后修改时间";');
ELSIF INSTR(ITEM.COLUMN_NAME, 'CREATEDON') > 0 THEN
DBMS_OUTPUT.PUT_LINE('ALTER TABLE ' || ITEM.TABLE_NAME ||
' MODIFY COLUMN ' || ITEM.COLUMN_NAME ||
' datetime(0) NULL DEFAULT CURRENT_TIMESTAMP COMMENT "创建时间";');
ELSE
DBMS_OUTPUT.PUT_LINE('ALTER TABLE ' || ITEM.TABLE_NAME ||
' MODIFY COLUMN ' || ITEM.COLUMN_NAME ||
' datetime(0) NULL DEFAULT CURRENT_TIMESTAMP;');
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('ALTER TABLE ' || ITEM.TABLE_NAME ||
' alter column ' || ITEM.COLUMN_NAME ||
' set default ' || ITEM.DATA_DEFAULT || ';');
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('');
END;
查看表和列详情语句
-- Oracle 查询语句
SELECT T.* FROM USER_TABLES T;
SELECT T.* FROM USER_TAB_COLUMNS T;
-- MySQL 查询语句
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'base table'
AND TABLE_SCHEMA = '数据库名'
ORDER BY TABLE_NAME ASC;
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = '数据库名'Oracle中常用函数的改写
有些在Oracle中能直接使用的函数，在MySQL中没有或者使用方式不一样，为了使原有SQL语句正常执行，则需要在MySQL
中进行改写、创建，大多数常用函数MySQL中也都有，只是使用方法不一样，可以创建个函数直接调用MySQL的函数就行，
其它特殊的函数则需要特殊处理，比如to_char,to_date,to_number等，由于MySQL中不支持函数的重载，所需要注意传参和
返回值类型，可以进行 case when 进行判断然后做相应的处理，还可以抛个异常方便及时处理，以保证函数的通用性健壮性
MySQL中不支持 || 进行字符串拼接，需要改成CONCAT()函数进行拼接，一个可拼接多个字符串，用 ，分割，当然为了保证
SQL语句的通用性，可以两个两个字符串拼接，使用多个CONCAT()进行拼接
对于DECODE() 函数，这个没有好的解决办法，创建这个函数的在实际使用中也达不到要求，因此都改成了 case when 语法
触发器迁移
- MySQL不支持CREATE OR REPLACE TARIGGER "XXX",可以通过 DROP TRIGGER IF EXISTS XXX 来创建
- MySQL触发器的触发条件一次只能有一个，不像Oracle中那样可以 BEFORE INSERT OR UPDATE OR DELETE ON XXX
- MySQL中的 new 和 old 用法是 new.xxx 和 old.xxx ，和Oracle的区别是前面不加 ：号
- 字段引号要用左上角~这个键的`号，而不是英文的单引号，不然会提示1064错误
- 进行更新操作后的新老数据赋值不能直接使用 = 号，需要使用 SET 进行赋值，如：SET new.xxx = 'xxx';
创建触发器范例
DELIMITER $$
DROP TRIGGER IF EXISTS TRG_SYNCROLERIGHT_DELETE $$
CREATE TRIGGER TRG_SYNCROLERIGHT_DELETE
BEFORE DELETE ON TSYS_ROLE_RIGHT
FOR EACH ROW
BEGIN
DECLARE V_GROUPID VARCHAR(64);
SELECT P.L_GROUPID
INTO V_GROUPID
FROM HSI_GROUP P
WHERE P.C_GROUPCODE = OLD.ROLE_CODE;
IF OLD.RIGHT_FLAG <> 2 THEN
DELETE FROM HSI_GROUPRIGHT
WHERE C_SYSNAME = 'XXX'
AND L_GROUPID = V_GROUPID
AND C_RIGHTCODE = OLD.SUB_TRANS_CODE;
END IF;
END $$
DELIMITER ;Oracle转MySQL触发器脚本
DECLARE
-- 输入触发器的拥有者即可获得MySQL创建触发器所用的脚本，仅供简单的触发器做参考，复杂的还是需要手动调整
TRIGGER_OWNER VARCHAR(1000) := 'ATS';
TRIGGER_NAME VARCHAR2(64);
SQLT CLOB;
ERRORTEXT VARCHAR2(200);
BEGIN
DBMS_OUTPUT.ENABLE(1000000000);
FOR ITEM IN (SELECT T.TRIGGER_NAME
FROM ALL_TRIGGERS T
WHERE T.OWNER = TRIGGER_OWNER
GROUP BY T.TRIGGER_NAME
ORDER BY T.TRIGGER_NAME DESC) LOOP
DBMS_OUTPUT.PUT_LINE('-- ' || ITEM.TRIGGER_NAME);
BEGIN
SELECT ('CREATE ' || REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE((LISTAGG(TEXT,
'*')
WITHIN
GROUP(ORDER BY LINE)),
'"',
''),
'ATS.',
''),
'declare',
''),
'IF UPDATING THEN',
''),
':',
''),
'end if;',
''),
CHR(10),
''),
'*',
'')) TEXT
INTO SQLT
FROM USER_SOURCE
WHERE NAME = ITEM.TRIGGER_NAME;
DBMS_OUTPUT.PUT_LINE(SQLT);
DBMS_OUTPUT.PUT_LINE('');
EXCEPTION
WHEN OTHERS THEN
ERRORTEXT := SUBSTR(SQLERRM, 1);
DBMS_OUTPUT.PUT_LINE(ERRORTEXT);
END;
END LOOP;
END;检查触发器是否漏写
-- MySQL中执行
SELECT T.TRIGGER_NAME
FROM INFORMATION_SCHEMA. TRIGGERS AS T
WHERE T.TRIGGER_SCHEMA = '数据库名称';
-- 将上面结果复制到下面即可查看有哪些触发器没有创建 --
-- Oracle中执行
SELECT T.TRIGGER_NAME
FROM ALL_TRIGGERS T
WHERE T.OWNER = 'ATS'
AND NOT INSTR('上面的查询结果', T.TRIGGER_NAME) > 0;函数和存储过程迁移
- 同样没有 CREATE OR REPLACE 语法，可以使用 DROP FUNCTION/PROCEDURE IF EXISTS XXX
- MySQL中的 DECLARE 变量的定义是在 BEGIN END; 里面
- 赋值需要执行 SET 或 INTO
- 返回值的关键字是 RETURNS 而不是 RETURN
- MySQL中游标(CRUSON)的定义和使用和Oracle略有不同
- 循环语法也有所不同，可以根据需求选择使用不同的循环方式
- MySQL中没有 Packages 和 Types，根据需求进行改写
创建函数范例
DELIMITER $$
DROP FUNCTION IF EXISTS f_getdictionarycaption$$
CREATE FUNCTION f_getdictionarycaption (
var1 VARCHAR (20),
var2 VARCHAR (20)
) RETURNS VARCHAR (64)
BEGIN
DECLARE result VARCHAR (64) ;
IF var1 IS NULL OR var1 = '' THEN
SET result = '' ;
ELSE
SELECT xxx INTO result FROM DUAL ;
END IF ;
RETURN result ;
END$$
DELIMITER ;创建存储过程范例
DELIMITER $$
DROP PROCEDURE IF EXISTS WF_SP_INITDATA$$
CREATE PROCEDURE WF_SP_INITDATA (
p_table_name VARCHAR (64),
p_condition VARCHAR (64),
p_cols VARCHAR (64),
p_values VARCHAR (64)
)
label : BEGIN
DECLARE v_l_rowcount INT ;
DECLARE v_vc_table_name VARCHAR (255) ;
DECLARE v_vc_condition VARCHAR (255) ;
DECLARE v_vc_values text ;
DECLARE v_sql text ;
SET v_vc_table_name = p_table_name ;
SET v_vc_condition = p_condition ;
SET v_vc_values = p_values ;
IF v_vc_table_name IS NULL OR v_vc_table_name = '' THEN
SELECT '表名必须输入' FROM DUAL ;
LEAVE label ;
END IF ;
IF v_vc_condition IS NULL OR v_vc_condition = '' THEN
SELECT '插入数据的条件不能为空' FROM DUAL ;
LEAVE label ;
END IF ;
IF v_vc_values IS NULL OR v_vc_values = '' THEN
SELECT '插入数据的值不能为空' FROM DUAL ;
LEAVE label ;
END IF ;
/*判断表是否已经存在*/
SELECT count(1) v_l_rowcount FROM information_schema.tables WHERE table_name = upper(p_table_name) ;
IF v_l_rowcount = 0 THEN
SELECT CONCAT( '表 ', p_table_name, ' 不存在!' ) FROM DUAL ;
LEAVE label ;
END IF ;
/*判断记录是否已经存在*/
SET v_sql = CONCAT( 'select count(1) from ', v_vc_table_name, ' where ', v_vc_condition ) ;
SELECT v_sql INTO v_l_rowcount ;
/* 不存在才插入数据*/
IF v_l_rowcount = 0 THEN
SET v_sql = CONCAT( 'insert into ', v_vc_table_name, p_cols, ' values ', v_vc_values ) ;
SELECT v_sql ;
END IF ;
END$$
DELIMITER ;视图迁移
- 视图是可以使用 CREATE OR REPLACE VIEW XXX 语法的
- MySQL不像Oracle那样支持from子查询，但可以将子查询再建个视图后进行关联使用，需要注意创建的先后顺序
- 视图中包含 union 的话不要在 as 后加括号
- MySQL中 Union 的子句不支持 order by
- MySQL能用 GROUP_CONCAT(xxx SEPARATOR',') 进行行转列
创建视图范例
create or replace view bs_bank_vl_sub as
select b.urid as bankcode,
b.name as bankname,
b.urid as banklevelcode,
b.urid as parentbanklevelcode,
'1' as banklevleid,
'总行' as banklevel,
b.code as directcode
from t_banks b
union
select tb.urid as bankcode,
tb.name as bankname,
tb.code as banklevlelcode,
b.urid as parentbanklevelcode,
'2' as banklevelid,
'分行' as banklevel,
b.code as directcode
from t_banklocations tb, t_banks b
where tb.bankid = b.urid 序列迁移
- MySQL中没有序列，有的主键可以用自增长来处理，前提是要知道哪些表的主键可以设为自增长
- 如果无法设置自增长的话可以模仿Oracle，通过使用序列来实现
- 可以创建个序列表，分别存着Oracle中的序列名称，当前值，增长值，在创建使用的函数
为了方便创建序列和初始化数据，可以将下面查询结果导出到excel中，在导入到MySQL中创建的sequence表中，很方便
-- 查询Oracle序列信息
select t.sequence_name,t.last_number,t.increment_by from user_sequences t;
MySQL实现Oracle序列
-- 创建表（序列）
drop table if exists sequence;
create table sequence (
seq_name VARCHAR(50) NOT NULL, -- 序列名称
current_value INT NOT NULL, -- 当前值
increment_value INT NOT NULL DEFAULT 1 -- 步长(跨度)
);
-- 实现currval的模拟方案 函数使用为：select currval('MovieSeq');
DELIMITER $$
DROP FUNCTION IF EXISTS currval $$
create function currval(v_seq_name VARCHAR(50))
returns integer
begin
declare value integer;
set value = 0;
select current_value into value
from sequence
where seq_name = v_seq_name;
return value;
END $$
DELIMITER;
-- 实现nextval的模拟方案 函数使用为:select nextval('MovieSeq');
DELIMITER $$
DROP FUNCTION IF EXISTS nextval $$
create function nextval (v_seq_name VARCHAR(50))
returns integer
begin
update sequence
set current_value = current_value + increment_value
where seq_name = v_seq_name;
return currval(v_seq_name);
END $$
DELIMITER ;
-- 增加设置值的函数
DELIMITER $$
DROP FUNCTION IF EXISTS setval $$
create function setval(v_seq_name VARCHAR(50), v_new_value INTEGER)
returns integer
begin
update sequence
set current_value = v_new_value
where seq_name = v_seq_name;
return currval(seq_name);
END $$
DELIMITER ;数据库备份
为了避免数据库产生的未知问题或操作而导致无法挽回的结果，可以在测试好后备份一下数据，可以通过导出SQL文件进行
备份，也可以使用工具自带的备份功能进行备份，如下图
发到客户那遇到的问题及解决
- 权限问题，由于导出的总的脚本里面包含用户信息（我使用的是root用户），所以会导致客户那边用户不同会报错及权限问题
解决方式：将导出的脚本中的视图、函数、存储、触发器中的用户信息的sql片段去掉，可以批量替换成空格
- 视图问题，上面也说了，由于MySQL中不支持from子查询，需要将子查询再创建个子视图，但导出语句中视图的顺序是不同的，导致子视图在后面的话，创建父视图会找不到而报错
解决方式：手动将脚本中视图那块的语句改为普通的创建语句，并调整好顺序，将子视图放父视图前面，以免报错而中止导入
总结
本次数据库迁移中遇到的问题及解决让我对Oracle和MySQL的不同有了进一步的了解，以后使用MySQL的企业和用户应该会越来越多，此文章记录下这个过程可以为之后类似的工作做个参考，记录并未涉及全部内容，后续有需要的话可以再新增修改。
附笔记链接：Oracle转MySQL记录
488
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
