Shulou(Shulou.com)06/01 Report--

At present, we want to migrate the whole business of the website to the cloud, and now oracle is converted into a mysql database to achieve ioe. Now there is a task to create a view of oracle in mysql, and the relevant base table has been created. I take it for granted that as long as the creation statement of oracle is taken out, it can be executed in mysql. In fact, the real process has encountered a lot of problems, as follows: 1Moore MySQL does not have the decode function of oracle 2 ERROR MySQL t does not have the translate function of oracle, 3 Magi MySQL create view cannot have a subquery (view 1349 error resolution) ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause my solution is to include view 4 in the view The difference between mysql CONCAT_WS and CONCAT first take a look at oracle's current view creation 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. Tableaccounnamereparamagement zbxrewrecorder) decode (t.tablechimnamerezbxreparentific) and t.member_id=i.member_id and t.sign brecricialcrecedredlemewyrewwdrewledrewledrewnrewnwnwnwywyxmxrewywywywywywywo; t.topicid) as topicid from infoservice.t_member_my treachememembers: I where I. mycircle info I (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=' official' In view of the problems encountered, make corresponding adjustments: 1 there is no decode function in oracle: the use of the decode function in oracle: decode (condition, value 1, return value 1, value 2, return value 2. The meaning of this function is as follows: IF condition = value 1 THEN RETURN (translation value 1) ELSIF condition = value 2 THEN RETURN (translation value 2). ELSIF condition = value n THEN RETURN (translation value n) ELSE RETURN (default value) END IF decode (field or field operation, value 1, value 2, value 3) the result of this function is that when the value of the field or field operation is equal to the value 1, the function returns value 2, otherwise the return value 3 of course value 1, value 2, value 3 can also be an expression, this function makes some sql statements much easier. Solution: replace decode (t.topicid,',', decode) with case when: replace decode (case when,',', decode) with CASE WHEN t. Tableqnamepari ZBX then 'then', and CASE WHEN t. Tablepurnameparamagement 'then' (case when. Tablepurnameprocyzbx` then'). The translate function of else t.topicid end as topicid 2.mysql without oracle first the function of oracle's translate: TRANSLATE (string,from_str,to_str) returns the string that replaces each character in from_str with the corresponding character in to_str. TRANSLATE is a superset of the functionality provided by REPLACE. If from_str is longer than to_str, extra characters in from_str rather than to_str will be removed from string because they do not have corresponding replacement characters. To_str cannot be empty. Oracle interprets the empty string as NULL, and if any parameter in TRANSLATE is NULL, the result is also NULL.

Note: be sure to note that the translate function of oracle is an one-to-one replacement, and it is for a single character, and all the characters that appear in from_str are replaced (either with the corresponding characters in to_str, or if there is no corresponding to be removed directly), to be different from replace,replace for strings, it must be fully matched in order to replace from_str with to_str as a whole. Oracle TRANSLATE example: syntax: TRANSLATE (expr,from,to) expr: represents a string of characters. From and to have an one-to-one correspondence from left to right. If they do not correspond, they will be regarded as null values. For example: SQL > select translate ('abcbbaadef','ba','#@') from dual; (b will be replaced by #, a will be replaced by @)

TRANSLATE (- @ # c##@@def SQL > select translate ('abcbbaadef','bad','#@') from dual; (b will be replaced by #, a will be replaced by @, and the value corresponding to d will be null and will be removed)

TRANSLATE-@ # c##@@ef oracle replace instance: SQL > select replace ('abcbbaadef','ba','#@') from dual; replaces the overall ba that appears with # @

REPLACE ('A-abcb#@adef SQL > select replace ('abcbbaadef','bad','#@') from dual; # # does not exactly match the bad on, so there is no replacement

REPLACE (A-abcbbaadef's solution to mysql's translate function without oracle: replace TRANSLATE (memberinfo.CUST_NAME,''' ",'_') with replace (replace (memberinfo.CUST_NAME,'''','_'),'','_'), that is, replace single quotes with replace, and then replace double quotes with a replace. (note that two single quotes represent a single quote in sql). 3Perfect MySQL create view cannot have subqueries, or ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause solution: create related subqueries into a view in advance As follows: create views on the cloud: 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 and memberinfo.cust_status=''"," category_id ":"', a.industryidjold'"}') 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=' 'formal' Create sub-views on the cloud: 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.topicidwritten parallel then 'then (case when t.tablepowernamerecording recording zbxx' then', a, when, tablewriting, recording, writing, etc. Where I. Myopia info 1 'and t.member_id=i.member_id and t.sign = 0 and length (t.cust_email) > 3) 4. Finally, summarize the difference between CONCAT_WS and CONCAT in mysql: because pipe characters cannot be used in mysql like oracle | | concatenate strings in sql, but you can use CONCAT or CONCAT_WS functions to achieve the purpose of concatenation. The MySQL string processing function concat_ws () is similar to the MySQL string handling function concat (), but the string handling function is not quite the same. The concat_ws () function represents concat with separator, that is, a string concatenation with a delimiter, of course, when the delimiter is empty, it is more similar to concat (). 1) if MariaDB [(none)] > select concat_ws (',','11) is separated by a comma after connection. +-- + | concat_ws (',','11,')) | +-- + | 11Magnehe2 Liu | +-- + 2) split in space after connection It can be understood that there is no separation. It is very similar to concat () MariaDB [(none)] > select concat_ws ('','11') +-- + | concat_ws ('','11','11'. 'liu') | +-+ | 11he2liu | +-+ MariaDB [(none)] > select concat ('','11') +-- + | concat ('','11','11'. 'liu') | +-+ | 11he2liu | +-+ 1 row in set (0.00 sec) 3) concat_ws () differs from concat () in that the concat_ws () function does not return NULL because of the NULL value when it executes. MariaDB [(none)] > select concat_ws (',','11,'11,'11, 22, 22, 10, 22, 22, 10, 10, 11, 11, 11, 22, 22, 14, 14, 14, 11, 11, 11, 22, 14, 14, 14, 11, 11, 11, 11, 22, 14, 14, 11, 11, 11, 11, 22, 14, 14, 11, 11, 11, 11, 22, 22, 10, 10, 11, 11, 11, 11, 22, 22, 10, 10, 11, 11, 11, 22, 22, 10, 10, 11, 11, 11, 22, 22, 10, 10, 11, 11, 11, 22, 22, 10, 10, 11, 11, 22, 22, 10, 10, 11, 11, 11, 22, 22, 10, 10, 11, 11, 11, +-- + | concat_ws (',','11 row in set 22) | +-+ | 11 sec 22 | +-+ 1 sec)

MariaDB [(none)] > select concat_ws ('','11','11','11','11,'11,'11,'11,'11,'11,'11,'11,'11,'11,'11,'11,'11,'11,'11,'11,'11,'11,'11,'11,'11,'11,'11,'11,'11,'11,'11','11','11','11','11','11', 22-year-old, 22-year-old, null) +-- + | concat_ws (','11 row in set 22 sec null) | +-+ | 0 | +-+ 1 sec)

MariaDB [(none)] > select concat ('11, 22, 22, and null) +-+ | concat ('11 sec oracle 22 sec null) | +-+ | NULL | +-+ 1 sec (0.00 sec) there are still many differences between oracle and mysql, and the process of going to ioe is still very difficult.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.