mysql达成oracle的decode和translate以及管道符拼接
发布时间：2022-03-26 05:54:24 所属栏目：编程 来源：互联网
导读：目前要把网站整体业务迁移到云，并且又现在的oracle转换成mysql数据库，实现去ioe,现在有个任务是把oracle的一个视图在mysql中创建上，相关的基表已经创建完毕，想当然觉得只要把oracle的创建语句有拿出来，在mysql执行就可以了，其实真正过程遇到了很多问题
|
目前要把网站整体业务迁移到云，并且又现在的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的过程还是很艰难的。 （编辑：核心网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
