Oraclemysql差异性总结

之前有个项目是用oracle数据库进行开发,需要把数据库改成mysql,遇到了一些地方需要注意的,就简单记了下来。

备注:

再把oracle转成mysql的时候,表中字段的类型转换是比较头疼的,比如oracle中的number转成mysql的时候,你要从“FLOATDOUBLETINYINT SMALLINTMEDIUMINTINTBIGINT”中选择一个合适的,其他数据类型同理;工具转的话经常就会把你带进坑里了。。坑爹,一不小心就报错了,这个错误排查起来还比较麻烦,只有细心应对。

 


 

 

一、

WM_CONCAT对应GROUP_CONCAT;

二、

ronum可用limit代替;

三、

sys_guid()用uuid()代替

四、

add_months(t2.maxTime,-12)用date_add(t2.maxTime,interval -12 month)代替;

date_format(date,'%Y-%m-%d')-->oracle中的to_char();

str_to_date(date,'%Y-%m-%d')-->oracle中的to_date();

%Y:代表4位的年份

%y:代表2为的年份

%m:代表月, 格式为(01……12)  

%c:代表月, 格式为(1……12)

%d:代表月份中的天数,格式为(00……31)  

%e:代表月份中的天数, 格式为(0……31) 

%H:代表小时,格式为(00……23)  

%k:代表 小时,格式为(0……23)  

%h: 代表小时,格式为(01……12)  

%I: 代表小时,格式为(01……12)  

%l :代表小时,格式为(1……12)

%i: 代表分钟, 格式为(00……59) 

%r:代表 时间,格式为12 小时(hh:mm:ss [AP]M)  

%T:代表 时间,格式为24 小时(hh:mm:ss) 

%S:代表 秒,格式为(00……59)  

%s:代表 秒,格式为(00……59) 

例子:

select case when add_months(t2.maxTime,-12)>t1.minTime then to_char(add_months(t2.maxTime,-12),'yyyy-MM') else to_char(t1.minTime,'yyyy-MM') end as startTime,to_char(t2.maxTime,'yyyy-MM') as endTime from(select min(t.log_time) as minTime from t_lm_operlog t) t1,(select max(t.log_time) as maxTime from t_lm_operlog t)t2

转为:

select case when date_add(t2.maxTime,interval -12 month)>t1.minTime then date_format(date_add(t2.maxTime,interval -12 month),'%Y-%m') else date_format(t1.minTime,'%Y-%m') end as startTime,date_format(t2.maxTime,'%Y-%m') as endTime from(select min(t.log_time) as minTime from t_lm_operlog t) t1,(select max(t.log_time) as maxTime from t_lm_operlog t)t2

五、

oracle中:to_char(number)把number转成varchar2,在mysql中用concat(number)

六、

oracle中:to_number在mysql中可以用cast或者convert

 

七、

Oracle中记录行号用Rownum RN;

在mysql中:Select UID,(@rowNum:=@rowNum+1) as rowNo From a,(Select (@rowNum :=0) ) b Order by a.Money Desc;

八、

SmRappfucauthEntityMapper.xml中的insert:merge into

 

九、

在mapper.xml中,oracle语法:

AND rolename like '%'||#{rolename}||'%'

Mysql语法:

AND rolename like CONCAT(CONCAT('%',#{rolename ,jdbcType=VARCHAR}),'%')

十、

mysql报错:mysql中You can't specify target table for update in FROM clause错误的意思是说,不能先select出同一表中的某些值,再update这个表(在同一语句中)。

例如下面这个sql:

delete from tbl where id in 
(
        select max(id) from tbl a where EXISTS
        (
            select 1 from tbl b where a.tac=b.tac group by tac HAVING count(1)>1
        )
        group by tac
)

改成:

delete from tbl where id in 
(
    select a.id from 
    (
        select max(id) id from tbl a where EXISTS
        (
            select 1 from tbl b where a.tac=b.tac group by tac HAVING count(1)>1
        )
        group by tac
    ) a
)

也就是说将select出的结果再通过中间表select一遍,这样就规避了错误。注意,这个问题只出现于mysql,mssql和oracle不会出现此问题。

 

十一、

Oracle中:to_date(#{createtime ,jdbcType=VARCHAR },'yyyy-MM-dd hh24:mi:ss')

Mysql中:

str_to_date(#{createtime ,jdbcType=VARCHAR },'%Y-%m-%d %T')

十二、

Oracle中有递归:start with ...connect by prior;

在mysql中没有写个方法,但可以用函数来实现mysql递归;

例如:

Oracle递归:

select s.sysfuncid from T_SM_SYSFUNC s where s.sysfuncid!=t.sysfuncid start with s.sysfuncid = t.sysfuncid connect by prior s.sysfunccode=s.psysfuncid

Mysql递归:

select s.sysfuncid from T_SM_SYSFUNC s where s.sysfuncid!=t.sysfuncid and FIND_IN_SET(s.sysfuncid,FOO3(t.sysfuncid))

mysql函数FOO3:

DROP FUNCTION IF EXISTS FOO3;

CREATE FUNCTION `FOO3` (userid varchar(255))

RETURNS VARCHAR(4000)

BEGIN

DECLARE sTemp VARCHAR(4000);

DECLARE sTempChd VARCHAR(4000);

SET sTemp = '$';

SET sTempChd = areaId;

WHILE sTempChd is not NULL DO

SET sTemp = CONCAT(sTemp,',',sTempChd);

SELECT group_concat(sysfunccode) INTO sTempChd FROM T_SM_SYSFUNC where FIND_IN_SET(psysfuncid,sTempChd)>0;

END WHILE;

return sTemp;

END