oracle改造为mysql相关修改
-
查询表的字段如果有table."properties"类似的,去掉引号
方法:
-
打开网站:https://c.runoob.com/front-end/61
-
粘贴html和js代码:
<script src="https://cdn.staticfile.org/jquery/2.2.4/jquery.min.js"></script> <!DOCTYPE html> <html> <head> <meta charset="utf-8"> <title>文档标题</title> </head> <body> <textarea id="text" style="width: 100%;height:100%">转换代码</textarea> </body> </html>
let text = `要替换的代码`; $(function () { text = prompt(); console.log(text); replaceStr(); }); function replaceStr() { let textReg = /\."[a-zA-Z0-9]*"/ig; // 替换oracle中的查询字段名添加引号的情况 text.replace(textReg, function (match, param, offset, string) { let temp = match.replaceAll('"', ''); text = text.replace(match, temp); }); // 替换查询到为空设置默认值 nvl-》ifnull text = text.replaceAll(/nvl/ig, "ifnull"); // 替换sysdate text = text.replaceAll(/sysdate/ig, "now()"); // 替换to_date text = text.replaceAll(/to_date/ig, "str_to_date"); // 替换转换时间的to_char let dateToStrReg = /to_char[ ]*\([ a-zA-Z0-9_\.]*,[ ]*'[a-zA-Z0-9- :%]*'[ ]*\)/ig; text.replace(dateToStrReg, function (match, param, offset, string) { let temp = match.replace(/to_char/ig, 'date_format'); text = text.replace(match, temp); }); // 替换转换clob的to_char let blobToStrReg = /to_char\([a-zA-Z0-9_\.]*\)/ig; text.replace(blobToStrReg, function (match, param, offset, string) { let temp = match.replace(/to_char\(/ig, '').replace(')', ""); text = text.replace(match, temp); }); // 修改序列nextval currval let seqReg = /[a-zA-Z0-9_]*\.(nextval|currval)/ig; text.replace(seqReg, function (match, param, offset, string) { let temp = match.split("."); let repSqe = temp[0] + "."; let newSqe = temp[1] + "('" + temp[0] + "')"; text = text.replace(repSqe, ""); text = text.replace(temp[1], newSqe); }); // 替换listagg let concatReg = /listagg[ ]*\([a-z0-9\._]*,[ ',_]*\)[ a-z0-9\._\(]*\)/ig; text.replace(concatReg, function (match, param, offset, string) { let temp = match.split(/within[ ]*group/ig); let concatStr = temp[0].replace(/listagg[ ]*/ig, "group_concat"); text = text.replace(match, concatStr); }); // 替换decode let decodeStrReg = /decode[ ]*\(.*\)/ig; text.replace(decodeStrReg, function (match, param, offset, string) { let temp = match.split(","); temp[0] = temp[0].replace(/decode[ ]*\(/ig, " case "); for (let i = 0; i < temp.length; i++) { if (i + 1 < temp.length) { if (i % 2 == 0) { temp[i] = temp[i] + " when "; } else { temp[i] = temp[i] + " then "; } } else { // 最后一个元素 temp[i] = temp[i].replace(")", ""); temp[i] = temp[i] + " end "; } } let decodeStr = temp.join(""); text = text.replace(match, decodeStr); }); // 替换表取别名Oracle中使用双引号的情况 let aliasReg = /from|join[ ]*[\r\n\t]*[ ]*"*[a-z0-9\._\-]*"*[ ]+["a-z0-9\._]*/ig; text.replace(aliasReg, function (match, param, offset, string) { let temp = match.replaceAll('"', ''); text = text.replace(match, temp); }); // 替换dbms_lob.substr let clobReg = /dbms_lob.substr[ ]*\([a-z0-9\._ ]*,[ 0-9]*\)/ig; text.replace(clobReg, function (match, param, offset, string) { let temp = match.replaceAll(/dbms_lob.substr[ ]*\(/ig, ''); temp = temp.replaceAll(/,[ 0-9]*\)/ig, ""); text = text.replace(match, temp); }); // 替换trunc let truncReg = /trunc[ ]*\([a-z0-9\._]*\)/ig; text.replace(truncReg, function (match, param, offset, string) { let temp = match.replaceAll(/trunc[ ]*\(/ig, 'date_format('); temp = temp.replaceAll(')', ",'%Y-%m-%d 00:00:00')"); text = text.replace(match, temp); }); // 替换时间格式 短横线分隔 text = text.replaceAll(/yyyy-mm-dd hh24:mi:ss/ig, "%Y-%m-%d %H:%i:%s"); text = text.replaceAll(/yyyy-mm-dd hh24:mi/ig, "%Y-%m-%d %H:%i"); text = text.replaceAll(/yyyy-mm-dd hh24/ig, "%Y-%m-%d %H"); text = text.replaceAll(/yyyy-mm-dd/ig, "%Y-%m-%d"); text = text.replaceAll(/yyyy-mm/ig, "%Y-%m"); // 冒号分隔 text = text.replaceAll(/yyyy:mm:dd hh24:mi:ss/ig, "%Y:%m:%d %H:%i:%s"); text = text.replaceAll(/yyyy:mm:dd hh24:mi/ig, "%Y:%m:%d %H:%i"); text = text.replaceAll(/yyyy:mm:dd hh24/ig, "%Y:%m:%d %H"); text = text.replaceAll(/yyyy:mm:dd/ig, "%Y:%m:%d"); text = text.replaceAll(/yyyy:mm/ig, "%Y:%m"); // 反斜杠分隔 text = text.replaceAll(/yyyy\/mm\/dd hh24:mi:ss/ig, "%Y/%m/%d %H:%i:%s"); text = text.replaceAll(/yyyy\/mm\/dd hh24:mi/ig, "%Y/%m/%d %H:%i"); text = text.replaceAll(/yyyy\/mm\/dd hh24/ig, "%Y/%m/%d %H"); text = text.replaceAll(/yyyy\/mm\/dd/ig, "%Y/%m/%d"); text = text.replaceAll(/yyyy\/mm/ig, "%Y/%m"); text = text.replaceAll(/yyyy/ig, "%Y"); text = text.replaceAll(/hh24:mi:ss/ig, "%H:%i:%s"); text = text.replaceAll(/hh24:mi/ig, "%H:%i"); text = text.replaceAll(/mi:ss/ig, "%i:%s"); $("#text").text(text); $("#text").focus(); }
-
点击运行,输入需要替换的代码,右下角拷贝替换好的sql语句
-
-
使用ifnull替换oracle中的nvl(
直接替换函数名即可
)已自动化处理 -
sysdate修改为sysdate()或者now() 已自动化处理
-
序列.nextval修改为nextval(‘序列’)
注意:前提是mysql中已经构造好了序列
已自动化处理 -
to_date修改为str_to_date 已自动化处理
-
where rownum<10修改为limit 10
-
to_char修改为date_format(注意to_char转换clob) 已自动化处理
-
oracle中获取本周星期一零点零分零秒:
TRUNC(NEXT_DAY(SYSDATE-8,1)+1)
mysql中替换为:
DATE_FORMAT(subdate(curdate(),date_format(curdate(),’%w’)-1), ‘%Y-%m-%d %H:%i:%s’)
-
trunc函数替换:
-- oracle trunc(t.duty_start_time) -- mysql date_format(t.duty_start_time,'%Y-%m-%d 00:00:00')
-
listagg替换为group_concat,删除后面的within group(order by xxx) 已自动化处理
-
复杂替换:
-- oracle代码 SELECT * FROM ( SELECT l.id "id", row_number() OVER(PARTITION BY l.MESSAGE_SOURCE_CODE ORDER BY l.RECEIVE_TIME DESC) flag FROM SKY_MESSAGE_LIST l LEFT JOIN SKY_MESSAGE_RECEIVER_REL r ON l.ID = r.MESSAGE_ID WHERE r.RECEIVER_PERSON = #{loginUserId} ) WHERE flag = 1
-- mysql代码 SELECT * FROM ( SELECT l.id "id" FROM SKY_MESSAGE_LIST l LEFT JOIN SKY_MESSAGE_RECEIVER_REL r ON l.ID = r.MESSAGE_ID WHERE r.RECEIVER_PERSON = #{loginUserId} ORDER BY l.RECEIVE_TIME DESC limit 1 )
-
mysql中子查询表的别名不能和主查询中相同且子查询必须取别名
-
注释也可能会引起异常
-
替换Oracle中的add_months:
ADD_MONTHS(sysdate, -1) -- mysql ADDDATE(now(),INTERVAL -1 MONTH)
-
替换Oracle中的时间格式已自动化处理:
-- oracle mysql yyyy-mm-dd hh24:mi:ss %Y-%m-%d %H:%i:%s yyyy/mm/dd hh24:mi:ss %Y/%m/%d %H:%i:%s yyyy:mm:dd hh24:mi:ss %Y:%m:%d %H:%i:%s