这两天要把一个系统从oracle上移植到mysql上,顺便简单整理一下两者语法的不同
-
decode
mysql中没有decode,可以使用case when语句代替,oracle也支持case when语句,看来以后为了移植方便还是使用case when比较好。
select NAME,
case name
when 'sam' then 'yong'
when 'lee' then 'handsome'
else 'good' end
from lee;
下面是同一条sql,decode与case when比较:
sum(decode(sign(similarity-${param1})+sign(similarity-${param2}),0,1,-1,1,0)) as sus_tort_count
sum(case sign(similarity-${param1})+sign(similarity-${param2}) when 0 then 1 when -1 then 1 else 0 End
2.分页代码:
oracle中分页代码使用rownum,具体参见:http://qingfeng825.iteye.com/blog/779462
----oracle排序写法
SELECT * FROM (
SELECT list.*, rownum as RNUM FROM (
SELECT * FROM CIP_Test_User
ORDER BY ID desc )
list WHERE ROWNUM < 11 ) WHERE RNUM >=1
1和11都代表行号
mysql分页使用limit
----mysql排序写法
SELECT * FROM CIP_Test_User
ORDER BY ID desc
limit 1,10
1代表偏移量,10代表要取出来的记录个数
3.数字转字符串
mysql: concat(site_id,'')
oracle:to_char(site_id)
4.取当前日期(mysql 要加括号)
mysql:now(),sysdate() 具体参见:http://blog.sina.com.cn/s/blog_6d39dc6f0100m7eo.html
oracle:sysdate
5.触发器的不同,下面是两个相同功能的触发器
CREATE TRIGGER `IMS_EVIDENCE_UPDATE_TRIGGER` BEFORE UPDATE ON `bas_page_evidence`
FOR EACH ROW
BEGIN
if ((NEW.WEB_IMAGE IS NOT NULL AND OLD.WEB_IMAGE IS NULL)
OR(NEW.WEB_URL_IMAGE IS NOT NULL AND OLD.WEB_URL_IMAGE IS NULL)
OR(NEW.HTML_SNAP IS NOT NULL AND OLD.HTML_SNAP IS NULL)) then
set new.Evidence_Done_Count=new.Evidence_Done_Count+1;
if(new.EVIDENCE_DONE_COUNT>=new.EVIDENCE_COUNT) then
set new.Evidence_date=sysdate();
if(new.EVIDENCE_STATUS!='4' and old.EVIDENCE_STATUS!='4') then
set new.Evidence_status = '3';
end if;
update bas_monitor_result set status='5' where status='4' and url_Id=new.url_id;
end if;
end if;
END;
(mysql版本)
CREATE OR REPLACE TRIGGER IMS_EVIDENCE_UPDATE_TRIGGER
before UPDATE OF Web_image,Web_url_image,Html_snap
ON ims_page_evidence
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
WHEN ((new.Web_image is not null and old.Web_image is null)
or(new.Web_url_image is not null and old.Web_url_image is null)
or(new.Html_snap is not null and old.Html_snap is null))
BEGIN
:new.Evidence_Done_Count := :old.Evidence_Done_Count+1;
if :old.Evidence_Done_Count+1 >= :old.Evidence_Count then
:new.Evidence_date := sysdate;
if :old.Evidence_status !='4' and :new.Evidence_status !='4' then
:new.Evidence_status := '3';
end if;
--更新result
update ims_monitor_result set status='5' where status='4' and url_Id=:new.url_id;
end if;
END;
(oracle版本)
几点不同: mysql没有replace trigger, 要修改一个trigger只能是drop trigger后重新建;
oracle在before update 与 on 表名之间可以加入 of 列名1,列名2,列表3,表示当前trigger只对这几列有用,在mysql中没有找到这种写法。
oracle中用到new old都要加: ,mysql不用。