本篇博文是Oracle和MySQL之间的等效SQL转换和不同，目前市面上没有转换两种SQL的工具，小编觉得以后也不一定会有，于是在业余时间整理了一下，如果有什么错误之处请留言告知，小编也是刚入门的小白，望理解。

关于数据库连接配置

关于日期处理

1. 日期类型转换

string转日期类型

select to_date('2020-01-01 08:00:00', 'yyyy-mm-dd hh24:mi:ss') from dual

select str_to_data('2020-01-01 08:00:00','%Y-%m-%d %H:%i:%s')

日期类型转string

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual

select date_format(now(),'%Y-%m-%d %H:%i:%s')

ps:

格式OracleMysql年月日yyyy-mm-dd%Y-%m-%d

年月日 时分秒yyyy-mm-dd hh24:mi:ss%Y-%m-%d %H:%i:%s

日期函数sysdatesysdate() / now()

2. 日期计算

select sysdate + numtodsinterval(3,'hour') from dual;

select date_add(str_to_date(now(), '%Y-%m-%d %H:%i:%s'), interval 3 HOUR)

ps:

oracleMySqlnumtodsinterval(x,y) / numtoyminterval(x,y)interval expr unit(配合date_add() / date_sub()使用)

numtoyminterval 与numtodsinterval函数类似,将x转为interval year to month数据类型常用的单位有’year’,‘month’；

date_add(date,interval expr unit) / date_sub(date,interval expr unit)参数说明如下：

类型描述interval是间隔类型关键字

expr是一个表达式，对应后面的类型

unit时间间隔的单位(间隔类型)，如：HOUR->小时 、MINUTE->分 、SECOND->秒、MICROSECOND->毫秒、YEAR->年、MONTH->月、DAY->日、WEEK->周、QUARTER->季、YEAR_MONTH->年和月、DAY_HOUR->日和小时、DAY_MINUTE->日和分钟、DAY_ SECOND->日和秒、HOUR_MINUTE->小时和分、HOUR_SECOND->小时和秒、MINUTE_SECOND->分钟和秒

时间差计算

在Oracle中，两个时间相减，默认是用 后者 减 前者 即(被减数-减数)，得到的结果是以天为单位；

在MySQL中，则使用TIMESTAMPDIFF(unit,starttime,endtime)函数进行时间计算

例如：

select to_date(a.endtime,'yyyy-mm-dd hh24-mi-ss') - to_date(a.starttime,'yyyy-mm-dd hh24-mi-ss') from a

select TIMESTAMPDIFF(MINUTE,str_to_date(a.starttime,'%Y-%m-%d%H:%i:%s'),str_to_date(a.endtime,'%Y-%m-%d %H:%i:%s') from a

控制转换函数

判断某个值是否为空值，若不为空值则输出，若为空值，返回指定值。

OracleMysqlNVL(a,b)IFNULL(a,b)

关于mysql在update时不能在set和where中用子查询

update biz_mold_product_asso set multicavityflag = ''

where multicavityflag = (

select multicavityflag from biz_mold_product_asso where productid = 183 and moldid = 1053

) and moldid = 1053

update biz_mold_product_asso a (

select multicavityflag from biz_mold_product_asso where productid = 183 and moldid = 1053) b

set a.multicavityflag = ''

where a.multicavityflag = b.multicavityflag and moldid=1053

Oracle中的merge into

简单的说就是，判断表中有没有符合on()条件中的数据，有了就更新数据，没有就插入数据。使用mysql时，被更新的表必须有主键

案例1:

merge into biz_dev_order_phq b

using biz_order_child a

on (a.id = b.OrderChildId and a.DeviceId = b.DeviceId)

when matched then

update set b.Quantity = a.CompleteQuantity,b.LastUpdateTime = to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')

where a.deviceid = '' and a.status = 'osWork'

insert into biz_dev_order_phq (

orderchildid,deviceid

)

select a.Id,a.DeviceId

from biz_order_child a

left join biz_dev_order_phq b on a.ID = b.orderchildid and a.deviceid = b.deviceid

where b.OrderChildId is not null and a.deviceid = '' and status = 'osWork'

on duplicate key update Quantity = values(Quantity),LastUpdateTime = values(LastUpdateTime)

案例2:

merge into sys_ip_user a using (

select '#{ipaddress}' as ipaddress,'#{userid}' as userid from dual ) b

on (a.ipaddress = b.ipaddress)

when matched then

update set userid =b.userid,updatetime = to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')

when not matched then

insert (ipaddress,userid,updatetime) values(b.ipaddress,b.userid,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));

INSERT INTO sys_ip_user VALUES(1, 1,DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s') )

ON DUPLICATE KEY UPDATE userid =3,updatetime = DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s')

分组排序 (Oracle中的row_number() over())

在使用 row_number() over()函数时候，over()里头的分组以及排序的执行晚于 where 、group by、 order by 的执行。

select a.*,row_number() over(partition by a.orderchildId order by a.CheckEndTime desc) as rum_num

from biz_qa_check_first a

select @rownum:=@rownum+1 rownum,a.*, if(@orderchildId=a.orderchildId,@rank:=@rank+1,@rank:=1) as row_number,@orderchildId:=a.orderchildId

from(select * from biz_qa_check_first order by orderchildId,CheckEndTimedesc) a,

(select @rownum:=0,@orderchildId:=null,@rank:=0) b

关键字转义

MySql中若有表的列明是关键字，需要用反引号( 它在键盘的~这个键上 )转义

select * from sys_res_i18n where `key` = '123'

Substr使用注意项

mysql中substr开始位置不能为0,示例返回结果均为’12’

select substr('1234567890',0,2) from dual

select substr('1234567890',1,2)

MySQL报错之子句找不到列

有时候mysql运行报错Unknown column ‘o.status’ in ‘on clause’，这时候需要把多个查询联合的表或者语句用括号全部括起来。

select o.*, e.Name,@rownum:=@rownum+1 rownum,if(@status:=o.status,@rank:=@rank+1,@rank:=1) as row_number,@status:=o.status

from (

(select * from biz_order_child order by status,orderchildno) o,

(select @rownum:=0,@status:=null,@rank:=0) b)

left join v_sys_dict e on o.status = e.Code and e.pcode = 'OrderState' and e.type ='en_US'

Oracle中的Decode

在MySQL中使用 case when来实现。

select decode(mod(quantity,7),0,'A',1,'B','C') as qtype from biz_order

Select case mod(quantity,7)

when 0 then 'A'

when 1 then 'B'

else 'C'

end as qtype

from biz_order

实现numtodsinterval

函数把值转为interval day to second数据类型，转换后的值显示结果分为四段，分别为(天、小时、分钟、秒)

Oracle中select numtodsinterval(200,‘minute’) from dual;

显示为： +000000000 03:20:00.000000000

应用实例：获取设备故障维修的 等待时间 和 维修时长

select a.*,

case when length(ltrim(substr(numtodsinterval(ceil(a.reportperiod),'second'),2,15),'0')) = 6

then '0天' || substr(numtodsinterval(ceil(a.reportperiod), 'second'),12,8)

when a.reportperiod is null

then ''

else ltrim(substr(numtodsinterval(ceil(a.reportperiod), 'second'),2,9),'0') || '天' ||substr(numtodsinterval(ceil(a.reportperiod), 'second'),12,8)

end as ReportWaitTime,

case when length(ltrim(substr(numtodsinterval(ceil(a.repairperiod * 60), 'second'),2,15),'0')) = 6

then '0天' || substr(numtodsinterval(ceil(a.repairperiod), 'second'),12,8)

when a.repairperiod is null

then ''

else ltrim(substr(numtodsinterval(ceil(a.repairperiod), 'minute'),2,9),'0') || '天' ||substr(numtodsinterval(ceil(a.repairperiod), 'minute'),12,8)

end as RepairTime

from biz_dev_fault_repair_result a

效果如下：

SELECT a.*,

CONCAT(

FLOOR( a.reportperiod / 86400 ),

'天',

LPAD( FLOOR( a.reportperiod % 86400 / 3600 ), 2, 0 ),

':',

LPAD( FLOOR( a.reportperiod % 86400 % 3600 / 60 ), 2, 0 ),

':',

LPAD( CEIL( a.reportperiod % 86400 % 3600 % 60 ), 2, 0 )

) AS ReportWaitTime,

CONCAT(

FLOOR( a.repairperiod / 86400 ),

'天',

LPAD( FLOOR( a.repairperiod % 86400 / 3600 ), 2, 0 ),

':',

LPAD( FLOOR( a.repairperiod % 86400 % 3600 / 60 ), 2, 0 ),

':',

LPAD( CEIL( a.repairperiod % 86400 % 3600 % 60 ), 2, 0 )

) AS RepairTime

FROM biz_dev_fault_repair_result a

效果如下：

删除问题

delete from ${slave} b

where exists (select 1 from ${master} a where a.id = #{keyVal} ${keyAttrJoin})

delete b from ${slave} as b

where exists (select 1 from ${master} a where a.id = #{keyVal} ${keyAttrJoin})

获取行号

启示：mysql中没有获取行号的函数，因此需要通过一些自定义语句来进行获取。通常做法是，通过定义用户变量@rownum来保存表中的数据。通过赋值语句@rownum：=@rownum+1来累加达到递增行号。

例如：

select a.* ,@rownum:=@rownum+1 from a,(select @rownum:=0) r;

后半部分语句的select @rownum:=0 相当于创建了r的新表，其表的列为@rownum，数值为0.通过利用变量将@rownum的行进行重新赋值，并显示。可以应用于获取行号或名次排列。

select * from(

select a.*,b.Name,rowNum

from biz_machine_process_flow a

left join v_sys_dict b on a.Status = b.Code

where 1 = 1 and (a.endTime is null or a.endTime = '') order by a.startTime desc )

where rowNum = 1

select * from(

select d.*,@rownum := @rownum+1 AS rowNum FROM (SELECT @rownum:=0) r,(

select a.*,b.name

from biz_machine_process_flow a

left join v_sys_dict b on a.Status = b.Code

where 1 = 1 and (a.endTime is null or a.endTime = '')order by a.startTime desc) d

) where rowNum = 1

Oracle数字处理

--1、TO_CHAR(number, '格式')

TO_CHAR(salary,’$99,999.99’);

--2、TO_NUMBER 使用TO_NUMBER函数将字符转换为数字

TO_NUMBER(char[, '格式'])

Oracle中的to_number在mysql中的转换

--oracle中的to_number在mysql中的转换

-- 1、转为整型：

select cast(producecycle as unsigned int) from biz_mold

-- 2、转为浮点型： (10,2)代表数字共十位，保留小数点后两位。

select cast(producecycle as decimal(10,2)) from biz_mold

别名

MySql要求每一个派生出来的表都需要有一个自己的别名

否则会报如下的错：

Every derived table must have its own alias(每一个派生出来的表都必须有一个自己的别名)

位运算

方式OracleMySql右移moveright(a.EventId,16)a.EventId>>16

左移moveleft(a.EventId,16)a.EventId<<16

字符拼接

select a.Id,to_char(listAgg(c.Name,',') within group (order by a.id)) as TypeName from biz_produce_switch a

left join biz_produce_switch_type b on a.Id = b.Pid

left join sys_dict c on b.Type = c.Code and c.Pcode = 'ProduceSwitchType'

group by a.Id

select a.Id, group_concat(c.Name separator ',') as TypeName

from biz_produce_switch a

left join biz_produce_switch_type b on a.Id = b.Pid

left join (select Code,Name from sys_dict where Pcode = 'ProduceSwitchType') c on b.Type = c.Code

group by a.Id

效果图：

substr()

1. Oracle中的substr()

方法说明substr(string string, int a, int b);string 需要截取的字符串 ；a 截取字符串的开始位置(注：当a等于0或1时，都是从第一位开始截取)；b 要截取的字符串的长度

substr(string string, int a) ;string 需要截取的字符串 ；a 可以理解为从第a个字符开始截取后面所有的字符串。

举例：

select substr('HelloWorld',0,3) value from dual; //返回结果：Hel，截取从“H”开始3个字符

select substr('HelloWorld',1,3) value from dual; //返回结果：Hel，截取从“H”开始3个字符

select substr('HelloWorld',0,100) value from dual; //返回结果：HelloWorld，100虽然超出预处理的字符串最长度，但不会影响返回结果，系统按预处理字符串最大数量返回。

select substr('HelloWorld',5,3) value from dual; //返回结果：oWo

select substr('Hello World',5,3) value from dual; //返回结果：o W (中间的空格也算一个字符串，结果是：o空格W)

select substr('HelloWorld',-1,3) value from dual; //返回结果：d (从后面倒数第一位开始往后取1个字符，而不是3个。原因：下面红色 第三个注解)

select substr('HelloWorld',-2,3) value from dual; //返回结果：ld (从后面倒数第二位开始往后取2个字符，而不是3个。原因：下面红色 第三个注解)

select substr('HelloWorld',-3,3) value from dual; //返回结果：rld (从后面倒数第三位开始往后取3个字符)

select substr('HelloWorld',-4,3) value from dual; //返回结果：orl (从后面倒数第四位开始往后取3个字符)

select substr('HelloWorld',-1) value from dual; //返回结果：d，从最后一个“d”开始 往回截取1个字符

select substr('HelloWorld',-2) value from dual; //返回结果：ld，从最后一个“d”开始 往回截取2个字符

select substr('HelloWorld',-3) value from dual; //返回结果：rld，从最后一个“d”开始 往回截取3个字符

说明：

1.当a等于0或1时，都是从第一位开始截取，

2.假如HelloWorld之间有空格，那么空格也将算在里面；

3.当只有两个参数时，不管是负几，都是从最后一个开始 往回截取

2. MySql中的substr()

substr(str,pos);SELECT SUBSTR('2018-08-17',6);

substr(str from pos);SELECT SUBSTR('2018-08-17' FROM 6);

substr(str,pos,len);//str:字符串，pos：起始位置，len：截断长度SELECT SUBSTR('2018-08-17',6,7);

substr(str from pos len);SELECT SUBSTR('2018-08-17' FROM 6 FOR 7);

to_char()函数之报错信息：ORA-01722: 无效数字

问题SQL:

SELECT A.*,B.FUND_CODE AS FUND_CODE,B.FUND_FULLNAME AS Fund_Fullname

FROM BSP_SYS_ATTACH A,BSP_FUND_BASE B

WHERE A.ENTITY_ID=B.FUND_ID

报错原因：此处A.ENTITY_ID的类型为VARCHAR;B.FUND_ID的类型为NUMBER;两者类型不匹配所以报错。

更改后正确sql：

SELECT A.*,B.FUND_CODE AS FUND_CODE,B.FUND_FULLNAME AS Fund_Fullname

FROM BSP_SYS_ATTACH A,BSP_FUND_BASE B

WHERE A.ENTITY_ID=TO_CHAR(B.FUND_ID)