oracle转mysql

引入jar包和配置文件修改

1. jar包或maven地址修改

新增jar包

对应的maven地址

<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>8.0.13</version>
</dependency>

2.配置文件修改

springBoot项目修改

#原oracle的配置
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver
spring.datasource.url=jdbc:oracle:thin:@//数据库主机ip:1521/orcl
spring.datasource.username=用户名
spring.datasource.password=密码
#pagehelper
pagehelper.helperDialect=oracle

#修改之后的mysql配置
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://数据库主机ip:3306/drivingtest?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8&useSSL=false
spring.datasource.username=用户名
spring.datasource.password=密码
#pagehelper
pagehelper.helperDialect=mysql

ssm项目修改

## oracle数据库配置
jdbc.driver=oracle.jdbc.OracleDriver
jdbc.url=jdbc:oracle:thin:@数据主机ip:1521:orcl
jdbc.username=用户名
jdbc.pwd=密码

## mysql数据配置
jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://数据主机ip:3306/ncdw626?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8&useSSL=false
jdbc.username=用户名
jdbc.pwd=密码

  	#原oracle设置pageHelper为oracle
		<bean class="com.github.pagehelper.PageInterceptor">
				 <property name="properties">
						<value>
							helperDialect=oracle
							reasonable=true
						</value>
					</property>
				</bean>
    	#修改之后的mysql
			<bean class="com.github.pagehelper.PageInterceptor">
				 <property name="properties">
						<value>
							helperDialect=mysql
							reasonable=true
						</value>
					</property>
				</bean>

IDEA提示修改

进入配置搜索sql，在语言和框架的选项中选择“SQL方言”，之后SQL提示信息就会变成mysql版本

格式改变

1.主键将序列改变

order变成after 序列变成 LAST_INSERT_ID() 主键自增返回

<selectKey resultType="Long" order="AFTER" keyProperty="id">   		select LAST_INSERT_ID
</selectKey>

<insert id="insert" parameterType="com.jrwp.api.entity.ExamFacingFailinfo" useGeneratedKeys="true" keyProperty="id">

</insert>

2.使用mybatis—plus 改变逐渐生成策略将input改成auto（数据库自增）

3.使用rownum的改成limit

-- Oracle版本
select * from core_log where id > 18000 and rownum = 1;

-- 	MySQL版本
select * from core_log where id > 18000 limit 1;

-- Oracle版本排序
select a.*,rownum from core_log;


-- MySQL版本排序
select (@i:= @i+1) as rank_no,a.* from core_log a,(select @i:=0) b;

4.oracle中默认日期加减整数是减天数 mysql使用date_add函数

如果是日期进行相减会返回天数而且是小数 mysql使用

第一个参数返回的单位是什么 ，开始时间  结束时间
SELECT TIMESTAMPDIFF(HOUR,SYSDATE(),STR_TO_DATE('2021-07-21 00:00:00','%Y-%m-%d %H:%i:%s'))/24

5.mysql中不支持begin end语句块执行

解决思路：可以写成存储过程去执行也可以在service中创建事务分步执行
设置数据库链接信息为allowMultiQueries=true

1. 如 jdbc:mysql://192.168.21.128:3306/springBootAll?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false&allowMultiQueries=true

	// 执行多条语句,且语句类型可以不一致,每条语句后面都要根; 但是最后一条语句可以不跟
	<insert id="testMany" useGeneratedKeys="true" keyProperty="user.id">
        update user
        set user_name = '888'
        where id = 1;

        delete
        from user
        where id = 5;

        update user
        set user_name = '777'
        where id = 2;

        insert into user(id, user_name, idCardNumber, phone_number, dept_id, create_time, update_time)
            value (#{user.id}, #{user.userName}, #{user.idCardNumber}, #{user.phoneNumber}, #{user.deptId}, now(),
                   NOW());
    </insert>

6.中间表必须取别名

7.序列查询主键id

// oracle查询序列作为表主键id
select seq_yw_entrustapply.nextval from dual
    
//mysql查询表主键下一个id
SELECT Auto_increment FROM information_schema.`TABLES` WHERE  
    table_name = 'yw_entrustapply' limit 1;

8.oracle中的（+）是一种特殊的用法

(+)表示外连接，并且总是放在非主表的一方。

-- 例如左外连接：
select A.a,B.a from A LEFT JOIN B ON A.b=B.b;
-- 等价于
select A.a,B.a from A,B where A.b = B.b(+);

-- 再举个例子，这次是右外连接：
select A.a,B.a from A RIGHT JOIN B ON A.b=B.b;
-- 等价于
select A.a,B.a from A,B where A.b (+) = B.b;

函数替换

1.模糊查询 || 全部变成concat

oracle中concat只支持两个参数 mysql支持多个

2.connect by 函数不支持使用自定义mysql函数进行递归查询

编写的函数只能适用对应表的递归查询,并不能适用全部类型表

CREATE DEFINER=`root`@`%` FUNCTION `getChildFromPoliceClass`(`priorId` VARCHAR(200)) RETURNS text CHARSET utf8
BEGIN

DECLARE sParentList text ;

DECLARE sParentTemp text;

SET sParentList ='';
SET sParentTemp =CAST(priorId AS CHAR);


WHILE sParentTemp IS NOT NULL DO

SET sParentList = CONCAT(sParentTemp,',',sParentList);

SELECT GROUP_CONCAT(ser_code) INTO sParentTemp FROM policeclass_service WHERE FIND_IN_SET(PARENTCODE,sParentTemp)>0;

END WHILE;

	RETURN sParentList;

END

select * from core_dept c where find_in_set(c.id,getChildFromPoliceClass(0));

注意事项: 我们用到了 group_concat 函数来拼接字符串。但是，需要注意它是有长度限制的，默认为 1024 字节。可以通过 show variables like "group_concat_max_len"; 来查看。这个对于递归查询还是非常致命的。因为一般递归的话，关系层级都比较深，很有可能超过最大长度。(尽管一般拼接的都是数字字符串，即单字节)

解决方法：

修改 MySQL 配置文件 my.cnf ，增加 group_concat_max_len = 102400 #你要的最大长度。
执行以下任意一个语句。SET GLOBAL group_concat_max_len=102400; 或者 SET SESSION group_concat_max_len=102400。
他们的区别在于，global是全局的，任意打开一个新的会话都会生效，但是注意，已经打开的当前会话并不会生效。而 session 是只会在当前会话生效，其他会话不生效。共同点是，它们都会在 MySQL 重启之后失效，以配置文件中的配置为准。所以，建议直接修改配置文件。102400 的长度一般也够用了。假设一个id的长度为10个字节，也能拼上一万个id了。

3. CONNECT_BY_ROOT获取根节点属性

--- connect_by_boot函数的作用就是获取此条数据的跟节点
select  ds.id,ds.servicecentername,ds.parentid, CONNECT_BY_ROOT(id)   
from dic_serviceCenter ds  start with  ds.id =4  connect by prior  ds.id = ds.parentId;

4.CONNECT_BY_ISLEAF查询叶子节点函数

oracle中查询叶子节点数据

-- connect_by_isleaf = 1 代表叶子节点数据,connect_by_isleaf != 1 非叶子节点数据
select *
from (select s.*,
             CONNECT_BY_ISLEAF
      from DIC_SERVICECENTER s
      start with id = 4
      connect by prior
                     id = parentid) A
where a.connect_by_isleaf = 1

mysql版本查询叶子节点数据

-- getChildFromServiceCenter为查询所有子类id集合函数用,分割
-- 当ids与id相等时即为叶子节点
select * from (
           select s.*,getChildFromServiceCenter(s.ID) ids from dic_servicecenter s
           ) ds
where concat(ds.ID , ',') = ids;

叶子节点概念

5.to_char 函数

日期转字符串使用

--oracle版本
to_char(signtime,'yyyy-mm-dd')
-- mysql版本
date_format(wa.CREATETIME,'%Y-%m-%d %H:%i:%s')

其他类型使用

CAST(123  AS  CHAR)

6.to_date函数替换成str_to_date

-- oracle写法
to_date(#{searchEndTime},'yyyy-mm-dd hh24:mi:ss')

--mysql写法
str_to_date(#{createTime},'%Y-%m-%d %H:%i:%s')

7.REGEXP_REPLACE函数

目前mysql低版本不支持这个函数使用replace替换

思路：替换排序码的只需要替换第一个出现的字符串可以先把老的字符串剪切成三份然后再进行拼接

LOCATE函数会返回第一次出现的位置

SELECT concat(
		SUBSTR('123456123' from 1 FOR (LOCATE('123','123456123')-1) ),
'555',
SUBSTR('123456123' from (LOCATE('123','123456123')+LENGTH('123')))
)

replace('字段名','old','new');

8. wm_concat函数替换成 GROUP_CONCAT用法一样

9.trunc函数可以使用date_format进行替代

oracle
select trunc(sysdate) from dual
mysql
SELECT date_format(SYSDATE(),'%Y-%m-%d 00:00:00')

10.nvl函数使用ifnull函数替代

-- Oracle版本
NVL(a,b)
    
-- MySQL版本  
IFNULL(a,b)

11.to_number函数使用cast 转换

CAST('123' AS SIGNED)

12.SYS_GUID函数使用uuid()替换

-- oracle
SELECT SYS_GUID()FROM DUAL;

-- mysql
SELECT REPLACE(UUID(),'-','');

13.获取当前时间

sysdate 改成now()或者sysdate()

select sysdate from dual

select now();
select sysdate();

14.计算时间相差月份

--oracle方法
SELECT months_between(to_date('1997-02','yyyy-MM'), to_date('1996-10','yyyy-MM')) from dual;

--mysql方法,时间格式一定要精确到日才有数据,否则为空
select TIMESTAMPDIFF(MONTH,str_to_date('1996-10-01','%Y-%m-%d'),str_to_date('1997-02-02','%Y-%m-%d'));
--
select TIMESTAMPDIFF(MONTH,'1996-10-01','1997-02-02');
-- 相差年数
SELECT TIMESTAMPDIFF(YEAR,'1993-03-23 00:00:00',DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S'));
--相差季度
SELECT TIMESTAMPDIFF(QUARTER,'1993-03-23 00:00:00',DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S'));
--相差周数
SELECT TIMESTAMPDIFF(WEEK,'1993-03-23 00:00:00',DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S'))

15.增加时间函数

--oracle,当前时间减五个月
select add_months(sysdate,-5) from dual;

-- 减少一个月时间
select DATE_ADD(now(),INTERVAL -1 MONTH);
-- 增加一天时间
select DATE_ADD(now(),INTERVAL 1 DAY);

16 row_number () over ()方法

-- mysql8.0版本以上支持此方法和oracle的用法一样

SELECT *
FROM (
         SELECT a.*,
                row_number() over (
                    PARTITION BY a.MANAGEPERSONID
                    ORDER BY
                        a.SIGNTIME DESC
                    ) as rn
         FROM yw_appsign as a
     ) as t
WHERE rn = 1

17.decode函数替换

-- oracle中decode函数，如果id为空返回0，否则返回1
select decode(h.ID, null, 0, 1) yearMeet
from person_history_meet h;

---MySQL中使用if函数替换
select IF(h.id IS NULL, 0, 1) yearMeet from person_history_meet h;

-- 或者使用case when替换
select case when h.id IS NULL then 0 else 1 end yearMeet
from person_history_meet h;

18.minus函数

minus 函数代表结果集相减如

-- oracle中结果一减去结果二
select * from t_eg_01
minus
select * from t_eg_02;

mysql中可以使用left join替换

 select a.* from t_eg_01 a
 left join  t_eg_02 b on a.id=b.id
 where b.id is null;

oracle项目转为mysql项目步骤