title: oracle向mysql改造
abbrlink: 23712
date: 2020-1-10 11:44:53
tags:
- oracle
- mysql
categories: - oracle
- mysql
前言
由于工作需要，把oracle数据库改造成mysql数据库，而oracle和mysql在一定程度上有很大的区别，我们的工作是在已有项目改bug，所以特地总结一下这几天项目需要的改动。
1.函数式报错
1.1start with connect by prior函数错误
1.1.1问题描述：
这个是oracle特有的一个函数，是根据条件递归查询"树"。常用用法：start with connect by prior parentId=id或start with connect by prior id=parentId，第一种是：查询结果自己所有的后代节点（包括自己）；第二种用法是查询结果自己所有的父类节点（包括自己）。
1.1.2解决方案：
Mysql不支持通过start with connect by进行递归查询，所以创建函数进行查找。
首先是第一种：查询结果自己所有的后代节点（包括自己），首先得了解find_in_set这个函数作用，举个例子：find_in_set（parent_id,”1,2,3”）的意思是parent_id在1,2,3内。创建函数如下：
set global log_bin_trust_function_creators=TRUE;#创建前需要
create function getTaskObjects #getTaskObject 函数名称
(rootId varchar(100)) #输入值类型
returns varchar(1024) #返回值类型
BEGIN
DECLARE sTemp VARCHAR(1024); #定义变量
DECLARE sTempChd VARCHAR(1024);#定义变量
SET sTemp = '';
-- SET sTempChd = CAST(auth_id AS CHAR);
SET sTempChd = rootId;
WHILE sTempChd IS NOT NULL DO #如果不空一直循环
IF sTemp = '' THEN
SET sTemp = sTempChd;
ELSE
SET sTemp= CONCAT(sTemp,',',sTempChd);
END IF;
SELECT GROUP_CONCAT(dept_id) INTO sTempChd FROM sys_dept WHERE FIND_IN_SET(parent_id,sTempChd)>0;#把所有的id拼接接起来
END WHILE;#结束循环
RETURN sTemp; #返回结果
END 调用可以向平时调用内置函数一样调用,以下面这张表为例,这是全部数据。
两个查找和与之对应结果：
select getTaskObjects('101')
select * from sys_dept WHERE find_in_set(dept_id,getTaskObjects('101'))>0
第二种：查询结果自己所有的父类节点，返回值也是所有符合条件id拼接起来字符串，所以我们调用也得加find_in_set(id,getTaskObject(子节点id))
create function getTaskObjects2 #getTaskObject 函数名称
(rootId varchar(100)) #输入值类型
returns varchar(1024) #返回值类型
BEGIN
DECLARE sTemp VARCHAR(100); #定义变量
DECLARE sTempChd VARCHAR(100);#定义变量
DECLARE allData VARCHAR(1024);#定义变量
SET allData='';
SET sTempChd =rootId;
SET sTemp='';
WHILE sTempChd !=0 DO #如果不空一直循环
SET sTemp=sTempChd;
SET allData=CONCAT(allData,',',sTempChd);
SELECT PARENT_ID INTO sTempChd FROM sys_dept where dept_id=sTemp;
END WHILE; #结束循环
RETURN allData; #返回结果
END 一样以下面这张表为例,这是全部数据。
两个查找和与之对应结果：
select getTaskObjects2('101')
select * from sys_dept WHERE find_in_set(dept_id,getTaskObjects2('101'))>0
1.1.3 延伸：
1.1.3.1 传入的table为动态table表怎么办？
本以为这样写就可以全部用上，但这个有个明显的不足就是table只能写死，一开始的想法是把table传进去，但报错了，我是这样写的：
Select parent_id into sTempChd from tableName(ps：这是个变量) where object_id=sTemp;会报tableName这个表不存在的错误，因为sql不会自动识别tableName这个变量，它会认为有这个tableName的表 。
网上查找了资料说可以用动态sql，但函数不支持动态sql，所以解决办法就是写一个存储过程，以下讲解的是第二种查询结果自己所有的父类节点，如下:
CREATE PROCEDURE SELECT_TREE(IN keyColumn VARCHAR(32) CHARSET utf8,IN tableName VARCHAR(32) CHARSET utf8,IN parentColumn VARCHAR(32) CHARSET utf8,
IN keyValue VARCHAR(64) CHARSET utf8,OUT returndata VARCHAR(2048) CHARSET utf8)--输入输出参数，in为输入，out为输出
BEGIN
declare sTemp VARCHAR(100); --定义变量
SET sTemp = keyValue;
SET returndata='';
WHILE sTemp IS NOT NULL DO--如果不空一直循环
-- 创建sql
SET @selectSql = CONCAT('select ',parentColumn,' INTO @recordcount from ',tableName,' where ',keyColumn,"='",sTemp,"'");
-- 执行动态生成的sql语句
PREPARE temp FROM @selectSql;
EXECUTE temp;
SET sTemp = @recordcount;--其实对比上面仔细看可以看出只是这一步不同
IF sTemp IS NOT NULL THEN
SET returndata=CONCAT(returndata,',',sTemp);
END IF;
END WHILE; --结束循环
END可以对比上面的自定义函数，一个注意地方是@recordcount 代替sTempChd 变量，声明这是个变量，其他是语法问题，逻辑和自定义函数相同。
然后就是调用测试，我是在Navicat测试的，我的版本可以直接传参（听说低版本不可以直接测试）。注意的是out输出参数要传递变量，如:(keycolum,tableName,pid,in,@returnData)。以项目中一张表为例：
call SELECT_TREE('package_id','usbp_object_package','parent_id','74',@returnData);
SELECT @returnData 运行结果：
以上是在Navicat调用的，在项目中，我们需要写sql调用，用的是mybatis框架，调用如下，最重要是要加上statementType=”CALLABLE”，如果传入是map，returnData这个名字可以随便取，传入是pojo，这个值必须固定，在pojo里面应该有个同名变量。返回值去传入的参数里拿，这个我传入的是map，所以直接去传入的map拿了，至于传入pojo要给个变量returnData，会自动注入。
<mapper namespace="com.shd.biz.Tree.service.impl.TreeServiceImpl">
<!-- 注意 statementType="CALLABLE" -->
<select id="treeParent" statementType="CALLABLE">
{call SELECT_TREE(
#{keyColumn,mode=IN},#{tableName,mode=IN},#{parentColumn,mode=IN},#{keyValue,mode=IN}
,#{returnData,mode=OUT,jdbcType=VARCHAR})}
</select>
</mapper>Map<String, Object> parameters=new HashMap<String, Object>();
parameters.put("keyValue", value);
parameters.put("tableName", table);
parameters.put("keyColumn", keyColumn);
parameters.put("parentColumn", parentColumn);
this.getDao().queryForDataSet(SQL_PRIFIX + "treeParent", parameters);
//结果是从传入参数里面拿，不是从返回结果拿
String returnData = parameters.get("returnData").toString();1.1.3.2 调用报方法read-Only错误
最后还有个报错是事务报错，是因为我项目get开头的方法设置了readOnly=“true”，而调用存储过程如果还设置readOnly属性就会报错，要加上@Transactional(readOnly = false)或改变service方法名。
1.2 Sysdate报错
解决Sysdate替换为sysdate()
1.3 to_char报错
网上查找的资料：
date_format(date,’%Y-%m-%d’) -------------->oracle中的to_char();
str_to_date(date,’%Y-%m-%d’) -------------->oracle中的to_date();
具体还得看链接
1.4 日期的计算
oracle 可以直接用sysdate加减乘除，而mysql不可以，如
近一个月：
Oracle-------->sysdate-29//日期，今天减去29天
Mysql--------->
<if test="REPORT_TIME == '本周'">
and YEARWEEK(date_format(report_time,'%Y-%m-%d')) = YEARWEEK(now())
</if>
<if test="REPORT_TIME == '本月'">
and report_time >=DATE_ADD(curdate(),interval -day(curdate())+1 day) and report_time <= last_day(SYSDATE())
</if>
<if test="REPORT_TIME == '近1个月'">
and report_time BETWEEN date_sub(SYSDATE(),interval 1 month) AND SYSDATE()
</if>
<if test="REPORT_TIME == '近3个月'">
and report_time BETWEEN date_sub(SYSDATE(),interval 3 month) AND SYSDATE()
</if>
<if test="REPORT_TIME == '近1年'">
and report_time BETWEEN date_sub(SYSDATE(),interval 12 month) AND SYSDATE()
</if>1.5 Rownum报错
在mysql中没有Rownum，所以要特殊的实现方法，实现和效果如下，但我项目是用来分页，所以直接就用limit。
SELECT @rownum:=@rownum+1 AS rownum, table .*
FROM (SELECT @rownum:=0) r, table ;1.6 ||运算符报错
Mysql中||表示或的意识，而Oracle中||运算符可以将两个或两个以上的字符串连接在一起，在mysql中用concat代替。
2 其他报错
2.1 表别名报错
别名报错，首先mysql不可以像Oracle一样直接多重查找不加别名，如：
Select key from(select key from database);
改为Select t.key from(select key from database) t;但在删除时又不可以加别名，如 ：
Delete from database r where r.id=?会报错，得改为:
Delete from database where id=?2.2 Mysql没有序列问题
重新解决的办法几个，一个是用uid，另外一个也是根据自定义函数等进行创建一个伪序列，具体创建有一篇博客已经详细介绍了，
2.3 存储类型bigInteger装换toBigDecimal
原来oracle存储数据类型是integer，现在存储类型为decimal，启动时报错，bigInteger cannot to cast toBigDecimal，所以，就得装换一下。
2.4 union报错
Oracle的union和mysql的union的一个细节错误导致，oracle可以用括号括起来，而mysql不可以
