mysql date类型_Oracle和MySql之间SQL区别(等效转换以及需要注意的问题)

>本篇博文是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') - 日期类型转stringsql 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

效果如下:

b19dd6481c3a535de35d98eb066c5350.png
<!-- 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

效果如下:

752c456ae0860579f85f05b77f116db7.png

删除问题

<!-- 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

效果图:

014e863f54541286872fdc08b85a2911.png

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);

2d022369965ed9d9b92dd896344bd00d.png
  • substr(str from pos); sql SELECT SUBSTR('2018-08-17' FROM 6);

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

c728ced223325933f8a62769fa58d5c6.png
  • substr(str from pos len); sql SELECT SUBSTR('2018-08-17' FROM 6 FOR 7);

eb81c2494dfa2a66facd759504762015.png

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)

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值