mysql實現oracle的decode和translate以及管道符拼接

賀子_DBA時代發表於2017-05-24
MySqlOracle
目前要把網站整體業務遷移到雲，並且又現在的oracle轉換成mysql資料庫，實現去ioe,現在有個任務是把oracle的一個檢視在mysql中建立上，相關的基表已經建立完畢，想當然覺得只要把oracle的建立語句有拿出來，在mysql執行就可以了，其實真正過程遇到了很多問題，具體如下：
1,mysql 沒有oracle的decode函式，
2,mysql t沒有oracle的translate函式，
3,mysql create view 不能有子查詢 （ 檢視 第1349號錯誤解決方法）ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause
我的解決辦法是 檢視中包含檢視
4，mysql CONCAT_WS和CONCAT的區別
首先看一下oracle當前的檢視建立sql:
create or replace view infoservice.mail_tasks as
select a.mid as member_id,a.dingyue_id as dingyue_id ,a.cust_email as cust_email,duration_days,
memberinfo.cust_right_group as level1,
'{"member_name":"'|| TRANSLATE (memberinfo.CUST_NAME,'''"','__') ||'","keyword":"'||
TRANSLATE (a.keyword,'''"','__')||'","table_name2":"'||a.topicid||'",
"area_id":"'||a.areaid||'","category_id":"'||a.industryid||'"}' as query
from
(
select t.record_id as dingyue_id ,t.member_id as mid,t.cust_email as cust_email,
t.duration_days as duration_days,t.keyword as keyword,t.table_name,
t.industryid,
t.areaid,
decode( t.topicid , ',' ,decode(t.table_name,'zbxx',',a,b,c,d,e,f,g,h,','xmxx',',i,j,k,'), t.topicid) as topicid
from infoservice.t_member_my t,infoservice.t_member_my_info i
where i.my_id='1'
and t.member_id=i.member_id and t.sign = 0
and length(t.cust_email)>3
)
a ,infoservice.t_member_info memberinfo
where a.mid=memberinfo.record_id and memberinfo.cust_right_group != '0' and memberinfo.cust_status='正式'；
針對遇到的問題，來作出相應的調整：
1，mysql 沒有oracle的decode函式：
oracle中的decode函式的用處：
decode(條件,值1,返回值1,值2,返回值2,...值n,返回值n,預設值)
該函式的含義如下：
IF 條件=值1 THEN
　　　　RETURN(翻譯值1)
ELSIF 條件=值2 THEN
　　　　RETURN(翻譯值2)
　　　　......
ELSIF 條件=值n THEN
　　　　RETURN(翻譯值n)
ELSE
　　　　RETURN(預設值)
END IF
decode(欄位或欄位的運算，值1，值2，值3）
這個函式執行的結果是，當欄位或欄位的運算的值等於值1時，該函式返回值2，否則返回值3
當然值1，值2，值3也可以是表示式，這個函式使得某些sql語句簡單了許多.
解決辦法:
用case when 來替換：
把decode( t.topicid , ',' ,decode(t.table_name,'zbxx',',a,b,c,d,e,f,g,h,','xmxx',',i,j,k,'), t.topicid) as topicid
替換成
CASE WHEN t.topicid=',' then (case when t.table_name='zbxx' then ',a,b,c,d,e,f,g,h,' when t.table_name='xmxx' then ',i,j,k,' end ) else t.topicid end as topicid
2.mysql 沒有oracle的translate函式
首先oracle的translate函式的作用：
TRANSLATE(string,from_str,to_str)
返回將（所有出現的）from_str中的每個字元替換為to_str中的相應字元以後的string。TRANSLATE 是 REPLACE 所提供的功能的一個超集。如果 from_str 比 to_str 長，那麼在 from_str 中而不在 to_str 中的額外字元將從 string 中被刪除，因為它們沒有相應的替換字元。to_str 不能為空。Oracle 將空字串解釋為 NULL，並且如果TRANSLATE 中的任何引數為NULL，那麼結果也是 NULL。

注意：一定注意oracle的translate的函式是一一對應的替換，並且它針對的是單個字元，而且是把from_str裡面出現的字元全部都對應著換掉（要麼換成to_str中對應的字元，要沒有對應的就直接去掉)，要區別於replace，replace針對的是字串，必須要全部對應上，才能整體把from_str替換成to_str。
oracle TRANSLATE例項：
語法：TRANSLATE(expr,from,to)
expr: 代表一串字元，from 與 to 是從左到右一一對應的關係，如果不能對應，則視為空值。
舉例：
SQL> select translate('abcbbaadef','ba','#@') from dual; （b將被＃替代，a將被＠替代）

TRANSLATE(
----------
@#c##@@def　
SQL> select translate('abcbbaadef','bad','#@') from dual; （b將被＃替代，a將被＠替代，d對應的值是空值，將被移走）

TRANSLATE
---------
@#c##@@ef　
oracle replace例項：
SQL> select replace('abcbbaadef','ba','#@') from dual; 將出現的整體ba替換成了#@

REPLACE('A
----------
abcb#@adef
SQL> select replace('abcbbaadef','bad','#@') from dual; ##沒有完全匹配上的的bad，就沒有替換

REPLACE('A
----------
abcbbaadef
針對mysql 沒有oracle的translate函式的解決辦法：
將TRANSLATE (memberinfo.CUST_NAME,'''"','__')替換成replace(replace(memberinfo.CUST_NAME,'''','_'),'"','_')，也就是先用replace替換單引號‘，然後在用個replace替換雙引號“，（注意在sql中兩個單引代表一個單引號）。
3,mysql create view 不能有子查詢，否則報錯ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause
解決辦法：把相關子查詢提前建立成一個檢視，如下所示：
建立雲上的檢視：
create or replace view info.mail_tasks as
select a.mid as member_id,a.dingyue_id as dingyue_id ,a.cust_email as cust_email,duration_days,
memberinfo.cust_right_group as level1,
CONCAT_WS('','{"member_name":"',replace(replace(memberinfo.CUST_NAME,'''','_'),'"','_'),'","keyword":"',replace(replace(a.keyword,'''','_'),'"','_'),'","table_name2":"',a.topicid,'","area_id":"',a.areaid,'","category_id":"',a.industryid,'"}') as query
from info.mail_task_test
a ,info.v_member_info memberinfo
where a.mid=memberinfo.id and memberinfo.cust_right_group != '0' and memberinfo.cust_status='正式';
建立雲上的子檢視：
create view mail_task_test as
select t.record_id as dingyue_id ,t.member_id as mid,t.cust_email as cust_email,
t.duration_days as duration_days,t.keyword as keyword,t.table_name,
t.industryid,
t.areaid,
CASE WHEN t.topicid=',' then (case when t.table_name='zbxx' then ',a,b,c,d,e,f,g,h,' when t.table_name='xmxx' then ',i,j,k,' end ) else t.topicid end as topicid
from info.v_member_my t,info.v_member_my_info i
where i.my_id='1'
and t.member_id=i.member_id and t.sign = 0
and length(t.cust_email)>3
)
4.最後總結下mysql 中CONCAT_WS和CONCAT的區別：
因為mysql中不能像oracle那樣使用管道符||在sql中拼接字串，但是可以使用CONCAT或者CONCAT_WS函式來實現拼接的目的。
MySQL字串處理函式concat_ws()和MySQL字串處理函式concat()類似，但是處理的字串不太一樣，concat_ws()函式, 表示concat with separator,即有分隔符的字串連線 ，當然分隔符為空的情況就更類似於concat()。
1）如連線後以逗號分隔
MariaDB [(none)]> select concat_ws(',','11','he2','liu');
+---------------------------------+
| concat_ws(',','11','he2','liu') |
+---------------------------------+
| 11,he2,liu |
+---------------------------------+
2）連線後以空分割，可以理解為沒有分隔。非常類似於concat()
MariaDB [(none)]> select concat_ws('','11','he2','liu');
+--------------------------------+
| concat_ws('','11','he2','liu') |
+--------------------------------+
| 11he2liu |
+--------------------------------+
MariaDB [(none)]> select concat('','11','he2','liu');
+-----------------------------+
| concat('','11','he2','liu') |
+-----------------------------+
| 11he2liu |
+-----------------------------+
1 row in set (0.00 sec)
3)concat_ws()和concat()不同的是, concat_ws()函式在執行的時候,不會因為NULL值而返回NULL.
MariaDB [(none)]> select concat_ws(',','11','22',NULL);
+-------------------------------+
| concat_ws(',','11','22',NULL) |
+-------------------------------+
| 11,22 |
+-------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> select concat_ws('','11','22',NULL);
+------------------------------+
| concat_ws('','11','22',NULL) |
+------------------------------+
| 1122 |
+------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> select concat('11','22',NULL);
+------------------------+
| concat('11','22',NULL) |
+------------------------+
| NULL |
+------------------------+
1 row in set (0.00 sec)
oracle和mysql還是有很多不一樣的地方，去ioe的過程還是很艱難的。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29654823/viewspace-2139808/，如需轉載，請註明出處，否則將追究法律責任。

相關文章