mysql语法与decode语法的不同

这两天要把一个系统从oracle上移植到mysql上，顺便简单整理一下两者语法的不同

 

1. 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不用。