部署：数据库从oracle到mysql

oracle的专属函数和mysql的专属函数，以及对应关系
1.wm_concat-----group_concat
u_id       goods            num
------------------------------------------
1                苹果                2
2                 梨子               5
1                 西瓜               4
3                 葡萄               1
3                香蕉                1
1               橘子                 3

想要的结果为:
--------------------------------
u_id          goods_sum
____________________
1              苹果,西瓜,橘子
2              梨子
3              葡萄,香蕉

oracle:select u_id,wm_concat(goods) goods_sum from shopping group by u_id;

2.ROWNUM----LIMIT
AND ROWNUM<=10------LIMIT 10
and ROWNUM<10 ----- LIMIT 9

3.START WITH
select count(1) count from st_menu t start with  t.pid='0'connect by prior t.pkid =t.pid;
select count(1) count from st_menu t where FIND_IN_SET(t.PID, getChildLst('0'));

4.REPEAT、KEY在mysql为关键字
使用mysql，sql语句包含REPEAT，应该写成`REPEAT`;

5.decode--if
SUM(DECODE(T.DEAL_STATE, 0, 1, 0)) AS UNTREATED
SUM(if(T.DEAL_STATE=0,1,0)) AS UNTREATED

6.模糊查询REGEXP_LIKE---REGEXP
and REGEXP_LIKE(DEVICE_SN,#{sn})
and DEVICE_SN REGEXP #{sn}

7.模糊查询字段拼接:"pkid_route"
pkid||'_'||route;concat_ws('_','pkid','route');concat('pkid','_','route');
and ACQUIRER_NAME like '%'||#{acquirerInfo.acquirerName,jdbcType=VARCHAR}||'%'；
and ACQUIRER_NAME like '%${acquirerInfo.acquirerName}%'；
#{acquirerInfo.acquirerName,jdbcType=VARCHAR} -----‘收单机构名称’
区别：${acquirerInfo.acquirerName}  -------收单机构名称

8.to_char---date_format；to_date---str_to_date
and to_char(t.create_time,'yyyy-MM-dd')
and date_format(t.create_time,'%Y-%m-%d')

to_date(#{nowDate},'yyyy-MM-dd')
str_to_date(#{nowDate},'%Y-%m-%d')

9.nvl----IFNULL
nvl(t.trans_amount, 0) trans_amount,
IFNULL(t.trans_amount, 0) trans_amount,

10.ojdbc--mysql-connector-java
分别对应oracle、mysql的驱动

11.spring配置文件添加分页插件spring_dao.xml

<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource" />
        <property name="mapperLocations" value="classpath*:com/*/omp/**/mapper/*Mapper.xml" />
        <property name="plugins">
            <list>
                <bean
                    class="com.github.miemiedev.mybatis.paginator.OffsetLimitInterceptor"
                    id="Offset">
                    <property name="dialectClass"
                        value="com.github.miemiedev.mybatis.paginator.dialect.OracleDialect"></property>
                </bean>
            </list>
        </property>
    </bean>

                
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource" />
        <property name="mapperLocations" value="classpath*:com/*/omp/**/mapper/*Mapper.xml" />
        <property name="plugins">
            <list>
                <bean
                    class="com.github.miemiedev.mybatis.paginator.OffsetLimitInterceptor"
                    id="Offset">
                    <property name="dialectClass"
                        value="com.github.miemiedev.mybatis.paginator.dialect.MySQLDialect"></property>
                </bean>
            </list>
        </property>
    </bean>