基于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;