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