我的mybatis从oracle迁移转换mysql的差异【原】

时间：2023-03-09 10:00:17
我的mybatis从oracle迁移转换mysql的差异【原】

仅此作为笔记

分页差异

oracle 
<select id="select" parameterClass="java.util.Map" resultClass="com.bobo.code.model.LoginMember">

     select * from

             (select  ROWNUM rn , D.*   FROM

                          ( select

                                  <include refid= "selectSql" ></include>

                                <include refid= "pageCondition" ></include>

                             order by A.t_crt_tm DESC

                             ) D

                              <![CDATA[

                                 WHERE ROWNUM <= #maxRowNum:VARCHAR#

                             ]]>

                 )

                 <![CDATA[

                        WHERE rn > #minRowNum:VARCHAR#

                 ]]>

    </select>
mysql 
    <select id="select" parameterClass="java.util.Map" resultClass="com.bobo.code.model.LoginMember">

                           select

                                  <include refid= "selectSql" ></include>

                                <include refid= "pageCondition" ></include>

                                 limit #minRowNum:VARCHAR# , #pageSize:VARCHAR#

    </select>
   

因为oracle分页需要3个select 达到最佳性能,具体原因自行百度

而mysql分页基本语句如下

收到客户端{pageNo:1,pagesize:10}

select * from table limit (pageNo-1)*pageSize, pageSize;

收到客户端{pageNo:5,pageSize:30}

select * from table limit (pageNo-1)*pageSize,pageSize;

所以仔细分析差异后, 还是使用oracle的参数,那么mysql的分页条件就变成

表名差异

oracle是不关心表名大小写的, 但是mysql却大小写敏感

oracle mysql
INSERT INTO WEB_KING_LOGIN_MEMBER INSERT INTO web_king_login_member

日期差异

oracle mysql timestamp
sysdate now()
TO_CHAR(t_crt_tm, 'YYYY-MM-DD HH24:MI:SS')    AS tCrtTm,  FROM_UNIXTIME( UNIX_TIMESTAMP(t_upd_tm) , '%Y-%m-%d %H:%i:%s')    AS tCrtTm, 
A.t_crt_tm = TO_DATE(#cCrtTm:VARCHAR#,'yyyy-mm-dd hh24:mi:ss'); A.t_crt_tm = unix_timestamp(#cCrtTm:VARCHAR#);

uuid差异

oracle mysql
SYS_GUID() REPLACE(UUID(),"-","")

like差异

oracle mysql 
A.c_desc like '%' || #cDesc:VARCHAR# || '%'
 
A.c_desc like concat('%', #cDesc:VARCHAR#, '%')

其它差异

MySQL与Oracle 差异比较之三函数=>https://www.cnblogs.com/HondaHsu/p/3641190.html

相关文章