You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
193 lines
7.1 KiB
193 lines
7.1 KiB
12 months ago
|
# MySQL转换为Oracle涉及改造点:
|
||
|
|
|
||
|
|
## MySQL改为Oracle数据库配置
|
||
|
|
|
||
|
|
#数据库驱动
|
||
|
|
spring.datasource.driver-class-name: oracle.jdbc.OracleDriver
|
||
|
|
#数据库地址,重写中间件配置,业务系统自定义
|
||
|
|
spring.datasource.url: jdbc:oracle:thin:@10.1.5.189:29008/invoice_pdb
|
||
|
|
#数据库用户名,重写中间件配置,业务系统自定义
|
||
|
|
spring.datasource.username: sims
|
||
|
|
#数据库密码,重写中间件配置,业务系统自定义
|
||
|
|
spring.datasource.password: sims_123456_DXHY
|
||
|
|
#数据库线程池名称,业务系统可以自定义配置
|
||
|
|
spring.datasource.hikari.pool-name: CommonHikariCP
|
||
|
|
spring.datasource.hikari.connection-test-query: SELECT 1 FROM DUAL
|
||
|
|
|
||
|
|
## MySQL改为kingbase数据库配置
|
||
|
|
|
||
|
|
#数据库驱动
|
||
|
|
spring.datasource.driver-class-name: com.kingbase8.Driver
|
||
|
|
#数据库地址,重写中间件配置,业务系统自定义
|
||
|
|
spring.datasource.url: jdbc:kingbase8://10.1.5.151:54321/
|
||
|
|
#数据库用户名,重写中间件配置,业务系统自定义
|
||
|
|
spring.datasource.username: system
|
||
|
|
#数据库密码,重写中间件配置,业务系统自定义
|
||
|
|
spring.datasource.password: dxhy@123
|
||
|
|
#数据库线程池名称,业务系统可以自定义配置
|
||
|
|
spring.datasource.hikari.pool-name: CommonHikariCP
|
||
|
|
spring.datasource.hikari.connection-test-query: SELECT 1 FROM DUAL
|
||
|
|
|
||
|
|
- 人大金仓数据库处理时按照和Oracle一致规则进行处理,只有批量插入时,按照和mysql一样的逻辑处理
|
||
|
|
-
|
||
|
|
|
||
|
|
## 对于MySQL转Oracle后mybatis逻辑判断调整
|
||
|
|
|
||
|
|
如果有需要区分为MySQL还是Oracle,按照这种方式区分,(默认按照这种进行区分)
|
||
|
|
<choose>
|
||
|
|
<when test="_databaseId=='mysql'">
|
||
|
|
and DATE_FORMAT(qi.sqjsrq,'%Y-%m-%d')>=#{params.sqStartTime}
|
||
|
|
</when>
|
||
|
|
<when test="_databaseId=='oracle'">
|
||
|
|
and TO_CHAR(qi.sqjsrq,'YY-MM-DD')>=#{params.sqStartTime}
|
||
|
|
</when>
|
||
|
|
</choose>
|
||
|
|
|
||
|
|
如果需要整个sql进行区分
|
||
|
|
<insert id="batchInsert" parameterType="java.util.List" databaseId="oracle">
|
||
|
|
|
||
|
|
## 日期格式转换
|
||
|
|
|
||
|
|
mysql: date_format()
|
||
|
|
数据类型为字符串格式
|
||
|
|
oracle: to_char(kprq, 'yyyy-mm-dd')
|
||
|
|
数据类型为日期格式
|
||
|
|
oracle: to_date('2024-01-01' ,'yyyy-mm-dd')
|
||
|
|
oracle: to_date(concat( '2024-04-01', ' 23:59:59'),'yyyy-mm-dd hh24:mi:ss')
|
||
|
|
|
||
|
|
## 系统日期格式转换
|
||
|
|
|
||
|
|
now() 改为 SYSDATE
|
||
|
|
|
||
|
|
## 涉及到limit
|
||
|
|
|
||
|
|
1.需要在原有的sql基础上新增rownumber
|
||
|
|
select * from (原有sql) where ROWNUM <= 100
|
||
|
|
2.或者使用函数,替代limit X(此处需要修改,按照实际填写)
|
||
|
|
OFFSET 0 ROWS FETCH NEXT X(此处需要修改,按照实际填写) ROWS ONLY
|
||
|
|
|
||
|
|
## 表名后面不能跟as
|
||
|
|
|
||
|
|
去掉as， select * FROM dm_spxx AS spxx 改为 select * FROM dm_spxx spxx
|
||
|
|
|
||
|
|
## group by 补全字段名
|
||
|
|
|
||
|
|
Oracle调整:group的字段,必须在查询条件中出现,
|
||
|
|
|
||
|
|
## CONCAT_WS分组
|
||
|
|
|
||
|
|
concat_ws(',',clzldm,clzlmc,cldlmc) 改为 clzldm || ',' || clzlmc || ',' || cldlmc
|
||
|
|
|
||
|
|
## 模糊查询CONCAT拼接%逻辑
|
||
|
|
|
||
|
|
CONCAT('%',#{req.mbMc},'%') 改为 CONCAT(CONCAT('%',#{req.mbMc}),'%')
|
||
|
|
|
||
|
|
## 使用group_concat
|
||
|
|
|
||
|
|
MySQL:
|
||
|
|
GROUP_CONCAT( t3.factor ) factor,
|
||
|
|
Oracle修改:
|
||
|
|
listagg(t3.factor,',') WITHIN GROUP (ORDER BY t3.factor) AS factor,
|
||
|
|
|
||
|
|
MySQL:
|
||
|
|
GROUP_CONCAT( DISTINCT t3.factor ) factor,
|
||
|
|
Oracle修改:
|
||
|
|
regexp_replace(listagg(t3.factor,',') WITHIN GROUP (ORDER BY t3.factor), '([^,]+)(,\1)*(,|$)', '\1\3') AS factor,
|
||
|
|
|
||
|
|
如果修改为
|
||
|
|
listagg(t3.factor,',') WITHIN GROUP (ORDER BY t3.factor) AS factor,
|
||
|
|
调整为:
|
||
|
|
xmlagg(xmlparse(t3.factor||',' wellformed) order by t3.factor).getclobval()
|
||
|
|
|
||
|
|
## MySQL使用反引号(`)来引用标识符，在Oracle中使用双引号(")或者不使用引号。
|
||
|
|
|
||
|
|
## 批量insert，改法参考：https://www.cnblogs.com/Yanjie153/p/16285095.html
|
||
|
|
|
||
|
|
mysql示例
|
||
|
|
<insert id="insertBatch" useGeneratedKeys="false" databaseId="mysql">
|
||
|
|
INSERT INTO LY_TEST (id, name, age )
|
||
|
|
VALUES
|
||
|
|
<foreach collection ="list" item="user" separator ="," close = ";">
|
||
|
|
(#{user.id}, #{user.name}, #{user.age})
|
||
|
|
</foreach>
|
||
|
|
</insert>
|
||
|
|
Oracle示例:
|
||
|
|
<insert id="insertBatch" useGeneratedKeys="false" databaseId="oracle">
|
||
|
|
INSERT ALL
|
||
|
|
<foreach collection="list" item="user" index="index"
|
||
|
|
close="SELECT * FROM dual" >
|
||
|
|
INTO LY_TEST (id, name, age)
|
||
|
|
VALUES (#{user.id}, #{user.name}, #{user.age})
|
||
|
|
</foreach>
|
||
|
|
</insert>
|
||
|
|
|
||
|
|
## mysql表id自增
|
||
|
|
|
||
|
|
1.创建序列化脚本
|
||
|
|
CREATE SEQUENCE XXL_JOB_REGISTRY_ID
|
||
|
|
INCREMENT BY 1
|
||
|
|
START WITH 1
|
||
|
|
NOMAXVALUE
|
||
|
|
NOCYCLE
|
||
|
|
CACHE 10;
|
||
|
|
2.insert语句中新增
|
||
|
|
<insert id="registrySave" >
|
||
|
|
INSERT INTO xxl_job_registry( registry_group , registry_key , registry_value, update_time)
|
||
|
|
VALUES( #{registryGroup} , #{registryKey} , #{registryValue}, #{updateTime})
|
||
|
|
<selectKey resultType="java.lang.Integer" order="BEFORE" keyProperty="id">
|
||
|
|
select XXL_JOB_REGISTRY_ID.NEXTVAL from dual
|
||
|
|
</selectKey>
|
||
|
|
</insert>
|
||
|
|
|
||
|
|
## order by排序
|
||
|
|
|
||
|
|
1.可以使用别名
|
||
|
|
2.如果不使用别名,同时存在分组情况,必须和分组的数据保持一致
|
||
|
|
|
||
|
|
## 批量更新，使用foreache时，增加 open="begin" close=";end;"
|
||
|
|
|
||
|
|
<update id="batchSaveDraft">
|
||
|
|
<foreach collection="list" item="item" separator=";" open="begin" close=";end;">
|
||
|
|
UPDATE LY_SJS SET SFSC = '2',CZSJ = SYSDATE,
|
||
|
|
NETID = #{item.userId},
|
||
|
|
|
||
|
|
WHERE SFSC = '3'
|
||
|
|
AND ID = #{item.id}
|
||
|
|
</foreach>
|
||
|
|
</update>
|
||
|
|
|
||
|
|
## 批量删除,
|
||
|
|
|
||
|
|
<delete id="xxx" parameterType="list">
|
||
|
|
delete from LY_TEST
|
||
|
|
where id in
|
||
|
|
<foreach collection="list" item="item" open="(" close=")" separator=",">
|
||
|
|
#{item}
|
||
|
|
</foreach>
|
||
|
|
</delete>
|
||
|
|
|
||
|
|
## 字符串为null判断
|
||
|
|
|
||
|
|
在oracle中，空字符串当null处理，所以想找到空字符串，就可以用 XXX is null来获取
|
||
|
|
获取非空字段，就直接用is not null来获取
|
||
|
|
如果用 “ =''” 或者 “ != '' ” 来作为条件的话，就会找不到结果
|
||
|
|
|
||
|
|
## ifnull函数处理
|
||
|
|
|
||
|
|
IFNULL(column_name, default_value)需要转换为NVL(column_name, default_value)
|
||
|
|
|
||
|
|
## 所有的mysql建议改为if函数部分,不要调整,为了兼容Oracle,只能使用case when
|
||
|
|
|
||
|
|
## 问题排查
|
||
|
|
|
||
|
|
- ORA-00933: SQL 命令未正确结束， 原因：mybatis 语句带分号，需要去掉sql语句结尾分号（;）
|
||
|
|
|
||
|
|
- java.sql.SQLException: 无效的列类型: 1111， 修改mybatis-config.xml配置，增加下面配置：
|
||
|
|
<!-- 设置但JDBC类型为空时,某些驱动程序 要指定值,default:OTHER -->
|
||
|
|
<setting name="jdbcTypeForNull" value="NULL"/>
|
||
|
|
|
||
|
|
- ORA-00925: 缺失 INTO 关键字， insert语句缺少 into，需要加上
|
||
|
|
- mybatis的实体类与sql表字段名不一致
|
||
|
|
- ORA-01861: 文字与格式字符串不匹配
|
||
|
|
- 需要调整日期格式和字符串格式
|