基于Spring和MybatisPlus下的Oracle转mysql语句语法适配修改

    本文讲述了在Spring+MybatisPlus环境中，oracle数据库转为mysql后查询语法和代码的主要修改。

一、insert语句

Oracle的主键采用序列，mysql有自增主键,转成mysql后取消<seleect>序列主键，取消id设置。

Eg: oracle中

<insert id="insertFactoryAbnormalApproval" parameterType="FactoryAbnormalApproval">
    <selectKey keyProperty="id" resultType="long" order="BEFORE">
        SELECT seq_factory_abnormal_approval.NEXTVAL as id FROM DUAL
    </selectKey>
    insert into factory_abnormal_approval
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="id != null">id,</if>
        <if test="abnormalId != null">abnormal_id,</if>
        <if test="approval != null">approval,</if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
        <if test="id != null">#{id},</if>
        <if test="abnormalId != null">#{abnormalId},</if>
        <if test="approval != null">#{approval},</if>
    </trim>
</insert>

Eg: mysql中

<insert id="insertFactoryAbnormalApproval" parameterType="FactoryAbnormalApproval">

    insert into factory_abnormal_approval
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="abnormalId != null">abnormal_id,</if>
        <if test="approval != null">approval,</if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
        <if test="abnormalId != null">#{abnormalId},</if>
        <if test="approval != null">#{approval},</if>
    </trim>
</insert>

二、日期

（1）、日期格式

to_char( , ‘yyyy-MM-dd’) ==>  date_format(, ‘%Y-%m-%d’)

Oracle:

select to_char(t.HAPPEN_TIME,'yyyy-MM-dd') from table_name;

Mysql:

select date_format(t.HAPPEN_TIME,'%Y-%m-%d') from table_name;

（2）、字符串转日期

to_date ==> str_to_date

to_char( , ‘yyyy-MM-dd’) ==>  date_format(, ‘%Y-%m-%d’)

三、返回条数

where rownum<  ==>   limit

四、表别名

mysql中子查询表的别名不能和主查询中相同且子查询必须取别名

Oracle

Select * from (select * from b);

==>

mysql

Select * from (select * from b) alia;