MYSQL 迁移oracle过程遇到的问题

Myth丶恋晨 2022-12-24 03:00 172阅读 0赞

1.日期格式化  
MySQL数据库通过DATE\_FORMAT(date,format)函数将日期格式化为字符串，其中format可用的标识符有：%Y(年)、%m(月)、%H(小时)、%i(分钟)、%S(秒)等等，常用的格式有'%Y-%m-%d'、'%H:%i:%S'、'%Y%m'等。  
Oracle数据库使用TO\_CHAR(date,format)函数对日期进行格式化，如对当前日期进行格式化：TO\_CHAR(SYSDATE,'yyyy-mm-dd HH:mi:ss')。  
2、去除重复列，  
在MySQL中使用到group by,而Oracle对group by 定义不同.  
防止在oracle分页查询时报错：ORA-00918: 未明确定义列。  
3、出现ORA-00942: 表或视图不存在错误，可能是数据库表名错误，也可能是没有权限或者账号不存在等等。  
4、MySQL 字符串支持单引号和双引号，但Oracle只支持单引号,查询语句时,表名不能用引号。  
5.Oracle 中别名不能加单引号  
6. MySQL 的批量插入、修改转换为 Oracle 的批量插入、更新时需加上begin end  
  
7.添加sequence  
Oracle表的主键自增是通过sequence实现的，故需要为表自增主键添加sequence，sequence创建示例如下  
  
CREATE SEQUENCE SEQ\_AUDIT\_INFO INCREMENT BY 1 START WITH 1 minvalue 1 NOMAXVALUE NOCACHE;  
  
Oracle的sequence方式如下 xml 代码  
  
<selectKey resultType="java.lang.Long" keyProperty="id" order="BEFORE" >  
SELECT SEQ\_PAPER\_TEXT.NEXTVAL FROM DUAL  
</selectKey>  
  
  
DROP SEQUENCE ROLE\_MEMBER\_SEQ;  
  
create sequence seq\_newsId  
increment by 1  
start with 84000;  
  
  
8.大写有个点,就是你如果返回的map,那么你Java中get 的key值 就必须大写  
  
9.  
PageInfo pageInfo=new PageInfo(page,limit);  
searchParams.put("pageStart", pageInfo.getStartIndex());  
searchParams.put("pageEnd", pageInfo.getEndIndex());  
分页 定式 (不带排序的)  
首:  
<if test="pageStart != null and pageEnd != null">  
select a.\* from(  
</if>  
标记:  
ROWNUM numrow,  
结尾:  
<if test="pageStart != null and pageEnd != null">  
) a where a.numrow <!\[CDATA\[>\]\]> \#\{pageStart\} and a.numrow <!\[CDATA\[<=\]\]> \#\{pageEnd\}  
</if>  
分页 定式 (有排序的)  
  
(1)首: <if test="pageStart != null and pageEnd != null">  
select \* from(  
SELECT T.\* FROM(  
</if>  
标记:  
ROWNUM numrow,  
尾:  
<if test="pageStart != null and pageEnd != null">  
) T WHERE numrow <!\[CDATA\[<=\]\]> \#\{pageEnd\}  
) where numrow <!\[CDATA\[>\]\]> \#\{pageStart\}  
</if>  
  
(2) 首:  
<if test="pageStart != null and pageEnd != null">  
select \* from(  
SELECT T.\*,ROWNUM numrow FROM(  
</if>  
  
尾:  
<if test="pageStart != null and pageEnd != null">  
) T WHERE ROWNUM <!\[CDATA\[<=\]\]> \#\{pageEnd\}  
) where numrow <!\[CDATA\[>\]\]> \#\{pageStart\}  
</if>  
  
  
10.查询时,to\_char返回string,to\_date返回时间  
11. 查询日期 当天  
between  
TO\_DATE(concat(\#\{date\},' 00:00:00') ,'yyyy-MM-dd HH24:mi:ss')  
and TO\_DATE(concat(\#\{date\},' 23:59:59') ,'yyyy-MM-dd HH24:mi:ss')  
  
日期查询 一段时间  
<if test="entrustDateStart!=null and entrustDateStart!=''">  
and ci.entrust\_date <!\[CDATA\[>=\]\]> to\_date(\#\{entrustDateStart\},'yyyy-mm-dd')  
</if>  
<if test="entrustDateEnd!=null and entrustDateEnd!=''">  
and ci.entrust\_date <!\[CDATA\[<=\]\]> to\_date(\#\{entrustDateEnd\},'yyyy-mm-dd')  
</if>  
日期查询 起止日加入其中  
<if test="createTimeStart != null and createTimeStart!= '' ">  
AND t1.create\_time <!\[CDATA\[>=\]\]> to\_date(concat(\#\{createTimeStart\},' 00:00:00'),'yyyy-MM-dd HH24:mi:ss')  
</if>  
<if test="createTimeEnd != null and createTimeEnd!= '' ">  
<!\[CDATA\[ AND t1.create\_time <= to\_date(concat(\#\{createTimeEnd\},' 23:59:59'),'yyyy-MM-dd HH24:mi:ss') \]\]>  
</if>  
12. LIKE '%' || \#\{lenders\} || '%'  
13.MySQL中：  
  
GROUP\_CONCAT( t3.factor ) factor,  
listagg(factor,',') WITHIN GROUP (ORDER BY factor) AS factor,  
那还有一种，有去重的，咋办~如下：  
GROUP\_CONCAT( DISTINCT t3.factor ) factor,  
修改为  
regexp\_replace(listagg(factor,',') WITHIN GROUP (ORDER BY factor), '(\[^,\]+)(,\\1)\*(,|$)', '\\1\\3') AS factor,  
  
192.168.99.13:8081/console/index/main  
14.新增  
自己设置id  
  
INSERT INTO ares\_lawsuit\_case\_info\_ext(  
id,  
ext,  
create\_time,  
update\_time  
)  
<foreach close=")" collection="list" item="item" index="index" open="(" separator="union">  
select  
\#\{item.ext.id\},  
\#\{item.ext.ext\},  
SYSDATE,  
SYSDATE  
from dual  
</foreach>  
  
  
case\_id IN (  
<foreach collection="caseIdLists" item="item" index="index" separator=",">  
\#\{item\}  
</foreach>  
)  
  
自增id  
1. <selectKey keyProperty="id" order="BEFORE" resultType="java.lang.Long">  
select PROGRESS\_FAKE\_SEQ.nextval AS id from dual  
</selectKey>  
  
2. INSERT INTO ares\_lawsuit\_case\_document(  
id,  
case\_id, document\_name, create\_time, update\_time, del\_flag, is\_upload, is\_required,  
progress\_id,  
lf\_document\_id)  
  
SELECT PROGRESS\_FAKE\_SEQ.NEXTVAL,a.\* FROM (  
<foreach collection="list" item="item" index="index" separator="union all">  
select  
\#\{item.caseId\}, \#\{item.documentName\}, SYSDATE, NULL, 0, 0, 0,  
(select lf\_progress\_id from case\_progress\_fake where case\_id= \#\{item.caseId\} limit 1),  
(select id from case\_progress\_fake where case\_id= \#\{item.caseId\} limit 1)  
FROM dual  
</foreach>  
) a  
  
批量新增  
<insert id="insertAll" parameterType="java.util.List">  
begin  
<foreach collection="list" item="item" index="index" separator=";">  
insert into SYNC\_MESSAGE (ID, COHORT\_DEF\_ID, PROJECT\_ID,  
REQUEST\_PATH, EXE\_TIME, ADV\_ANALYSIS\_ID,  
METHOD\_NAME, TYPE, STATUS, TARGET\_NODE, INPUT\_JSON, CHECK\_SUM)  
values (  
SYNC\_MESSAGE\_SEQ.NEXTVAL,  
\#\{item.cohortDefId,jdbcType=BIGINT\},  
\#\{item.projectId,jdbcType=BIGINT\},  
\#\{item.requestPath,jdbcType=VARCHAR\},  
\#\{item.exeTime,jdbcType=TIMESTAMP\},  
\#\{item.advAnalysisId,jdbcType=BIGINT\},  
\#\{item.methodName,jdbcType=VARCHAR\},  
\#\{item.type,jdbcType=VARCHAR\},  
\#\{item.status,jdbcType=VARCHAR\},  
\#\{item.targetNode,jdbcType=VARCHAR\},  
\#\{item.inputJson,jdbcType=CLOB\},  
\#\{item.checkSum,jdbcType=VARCHAR\}  
)  
</foreach>  
;end;  
</insert>  
  
16 批量更新  
<update id="batchUpdateSplitSinglePickCurrency" parameterType="java.util.List">  
<foreach collection="list" item="item" index="index" open="begin" close=";end;" separator=";">  
UPDATE ZC\_TR\_MULTI\_ORDER\_CURRENCY  
<set>  
<if test="item.sysCorderCode != null">  
SYS\_CORDER\_CODE = \#\{item.sysCorderCode,jdbcType=VARCHAR\},  
</if>  
  
<if test="item.sysPorderCode != null">  
SYS\_PORDER\_CODE = \#\{item.sysPorderCode,jdbcType=VARCHAR\},  
</if>  
  
<if test="item.bizPorderCode != null">  
BIZ\_PORDER\_CODE = \#\{item.bizPorderCode,jdbcType=VARCHAR\},  
</if>  
  
<if test="item.originalOrderCode != null">  
ORIGINAL\_ORDER\_CODE = \#\{item.originalOrderCode,jdbcType=VARCHAR\},  
</if>  
  
<if test="item.splitUserId != null">  
SPLIT\_USER\_ID = \#\{item.splitUserId,jdbcType=VARCHAR\},  
</if>  
  
<if test="item.createDate != null">  
CREATE\_DATE = \#\{item.createDate\},  
</if>  
  
<if test="item.updateDate != null">  
UPDATE\_DATE = \#\{item.updateDate\},  
</if>  
</set>  
where id = \#\{item.id,jdbcType=VARCHAR\}  
</foreach>  
</update>  
  
begin  
UPDATE ZC\_TR\_MULTI\_ORDER\_CURRENCY SET is\_del=1 where id in  
(  
<foreach collection="list" item="item" index="index" separator=",">  
</foreach>  
)  
;end;  
  
15.左连接 去重只要一条数据  
  
left join (  
SELECT \* FROM  
( SELECT a.\*, row\_number () over ( partition BY CASE\_ID ORDER BY DUE\_DATE ) r FROM ares\_property\_preservation a )  
WHERE r = 1 )  
  
有时候需要展示所有的kjxyh:  
select test1.\*,b.kj from test1,  
( select test1.id,wm\_concat(test2.kjxyh) kj from test1  
left join test2 on test1.name=test2.name and test1.sex=  
test2.sex group by test1.name,test1.sex,test1.id) b  
where test1.id in(b.id)  
  
  
  
,uad.ADDRESS as homeAddress  
  
left join (  
SELECT \* FROM  
( SELECT a.\*, row\_number () over ( partition BY CASE\_ID ORDER BY id ) r FROM REMINDER\_UPDATE\_ADDRESS a  
<where>  
<if test="homeAddress != null and homeAddress !=''">  
a.ADDRESS like '%' || \#\{homeAddress\} || '%'  
</if>  
</where>  
)  
WHERE r = 1 )  
uad ON uad.case\_id = ci.id  
  
16.更改表字段类型  
  
alter table LAW\_AUTHORITY\_ROLE add tmp\_col varchar2(3999);-- 添加临时列  
  
update LAW\_AUTHORITY\_ROLE set tmp\_col = ORGID ; --将目标字段中数据加入到临时列中  
  
update LAW\_AUTHORITY\_ROLE set ORGID = null; --将目标字段数据清空  
  
alter table LAW\_AUTHORITY\_ROLE modify (ORGID VARCHAR2(100)); --更改目标字段类型  
  
update LAW\_AUTHORITY\_ROLE set ORGID = tmp\_col; --将临时列数据加回到目标字段中  
  
alter table LAW\_AUTHORITY\_ROLE drop column tmp\_col; --清除临时列  
  
17. 修改序列号初始值  
  
ALTER SEQUENCE PROGRESS\_FAKE\_SEQ INCREMENT BY 10000;  
select PROGRESS\_FAKE\_SEQ.nextval from dual;  
ALTER SEQUENCE PROGRESS\_FAKE\_SEQ INCREMENT BY 1;