Oracle向mysql改造

---

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 &lt;= 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不可以