oracle转mysql

引入jar包和配置文件修改

1. jar包或maven地址修改

1. 新增jar包

1. 对应的maven地址

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

   2.配置文件修改

2. springBoot项目修改 ```properties

   原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

2. ssm项目修改
```properties
## 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提示修改

1. 进入配置搜索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语句块执行

1. 解决思路：可以写成存储过程去执行 也可以在service中创建事务分步执行

2. 设置数据库链接信息为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中的（+）是一种特殊的用法

1. (+)表示外连接，并且总是放在非主表的一方。 ```plsql — 例如左外连接： 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;

<a name="e789d6e6"></a>
## 函数替换
<a name="JaSWE"></a>
### 1.模糊查询   || 全部变成concat
oracle中concat只支持两个参数  mysql支持多个
<a name="X8VuW"></a>
### 2.connect by 函数不支持使用自定义mysql函数进行递归查询
编写的函数只能适用对应表的递归查询,并不能适用全部类型表
```java
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”; 来查看。这个对于递归查询还是非常致命的。因为一般递归的话，关系层级都比较深，很有可能超过最大长度。(尽管一般拼接的都是数字字符串，即单字节)
解决方法：

1. 修改 MySQL 配置文件 my.cnf ，增加 group_concat_max_len = 102400 #你要的最大长度 。
2. 执行以下任意一个语句。SET GLOBAL group_concat_max_len=102400; 或者 SET SESSION group_concat_max_len=102400。

3. 他们的区别在于，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查询叶子节点函数

4. 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

5. mysql版本查询叶子节点数据

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

6. 叶子节点概念

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用法一样

详细描述可以查看这篇文章

1. wm_concat()是将同属于一个组的（group by）同一个字段拼接在一起变成一行。mysql是一样的，只不过mysql用的是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;
-- mysql多条件判断
IF(expr1,expr2,IF(expr1,expr2,IF(expr1,expr2,expr3)))
-- 如
select if(ct.check_result = 0, '不合格',
          if(ct.check_result is null, '未检测', if(ct.check_result = 1, '合格', '未知'))) as checkResult,
       if(tl.revert_statue = 0, '未借出', if(tl.revert_statue is null, '目前没有借用',
                                          if(tl.revert_statue = 1, '已还', '未知')))   as lendStstue,
       if(tl.lend_statue = 0, '未借出', if(tl.lend_statue is null, '目前没有借用',
                                        if(tl.lend_statue = 1, '已借出', '未知')))   as lendStstue,
       ct.first_check_time
from lhm_check_tool_b ct
         left join lhm_check_tool_lend_b tl on ct.id = tl.check_tool_id
where ct.id=1
-- 或者使用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;