mysql实现oracle的decode和translate以及管道符拼接
目前要把网站整体业务迁移到云，并且又现在的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_testas
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的过程还是很艰难的。
分享题目：mysql实现oracle的decode和translate以及管道符拼接
分享地址：http://myzitong.com/article/gpppgs.html