目前要把网站整体业务迁移到云,并且又现在的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://cdkjz.cn/article/gpppgs.html