>本篇博文是Oracle和MySQL之间的等效SQL转换和不同,目前市面上没有转换两种SQL的工具,小编觉得以后也不一定会有,于是在业余时间整理了一下,如果有什么错误之处请留言告知,小编也是刚入门的小白,望理解。
csdn阅读效果更佳:
Oracle和MySql之间SQL区别(等效转换以及需要注意的问题)_qq_36449960的博客-CSDN博客blog.csdn.net关于数据库连接配置
<!-- Oracle -->
<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
<property name="url" value="jdbc:oracle:thin:@127.0.0.1:1521:test"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
<!-- MySql -->
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:<u>mysql://127.0.0.1:3306/test"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
关于日期处理
1. 日期类型转换
- string转日期类型 ```sql 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
sql select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual
select date_format(now(),'%Y-%m-%d %H:%i:%s') ``` - ps: | 格式 |Oracle | Mysql | | --- | --- | --- | | 年月日 | yyyy-mm-dd | %Y-%m-%d | 年月日 时分秒 | yyyy-mm-dd hh24:mi:ss | %Y-%m-%d %H:%i:%s | 日期函数 | sysdate | sysdate() / now()2. 日期计算
<!-- Oracle -->
select sysdate + numtodsinterval(3,'hour') from dual;
<!-- MySql -->
select date_add(str_to_date(now(), '%Y-%m-%d %H:%i:%s'), interval 3 HOUR)
- ps:
| oracle| MySql | |--- |--- | numtodsinterval(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)函数进行时间计算==
例如:
<!-- Oracle -->
select to_date(a.endtime,'yyyy-mm-dd hh24-mi-ss') - to_date(a.starttime,'yyyy-mm-dd hh24-mi-ss') from a
<!-- MySql -->
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
控制转换函数
判断某个值是否为空值,若不为空值则输出,若为空值,返回指定值。
| Oracle | Mysql | | --- | --- | | NVL(a,b) | IFNULL(a,b)
关于mysql在update时不能在set和where中用子查询
<!-- Oracle -->
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
<!-- MySql -->
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:
<!-- Oracle -->
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'
<!-- MySql -->
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:
<!-- Oracle -->
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'));
<!-- MySql -->
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 的执行。
<!-- Oracle -->
select a.*,row_number() over(partition by a.orderchildId order by a.CheckEndTime desc) as rum_num
from biz_qa_check_first a
<!-- MySql -->
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'
<!-- Oracle -->
select substr('1234567890',0,2) from dual
<!-- MySql -->
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来实现。
<!-- Oracle -->
select decode(mod(quantity,7),0,'A',1,'B','C') as qtype from biz_order
<!-- MySql -->
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
应用实例:获取设备故障维修的 等待时间 和 维修时长
<!-- Oracle -->
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
效果如下:
<!-- MySql -->
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
效果如下:
删除问题
<!-- Oracle -->
delete from ${slave} b
where exists (select 1 from ${master} a where a.id = #{keyVal} ${keyAttrJoin})
<!-- MySql -->
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的行进行重新赋值,并显示。可以应用于获取行号或名次排列。
<!-- Oracle -->
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
<!-- MySql -->
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(每一个派生出来的表都必须有一个自己的别名)
位运算
| 方式 | Oracle | MySql |--- | --- | --- | 右移 | moveright(a.EventId,16) | a.EventId>>16 | 左移 | moveleft(a.EventId,16) | a.EventId<<16
字符拼接
<!-- Oracle -->
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
<!-- MySql -->
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);
sql SELECT SUBSTR('2018-08-17',6);
- substr(str from pos);
sql SELECT SUBSTR('2018-08-17' FROM 6);
- substr(str,pos,len);//str:字符串,pos:起始位置,len:截断长度
sql SELECT SUBSTR('2018-08-17',6,7);
- substr(str from pos len);
sql SELECT SUBSTR('2018-08-17' FROM 6 FOR 7);
to_char()函数之报错信息:ORA-01722: 无效数字
问题SQL:
<!-- Oracle -->
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:
```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)