Oracle、 MySQL兼容适配

WM_CONCAT && GROUP_CONCAT

-- Oracle
select WM_CONCAT (DICT_VALUE1) from SYS_DICT t WHERE t.ROOT_KEY = 'XBDM';

-- MySQL
select GROUP_CONCAT(DICT_VALUE1) from SYS_DICT t WHERE t.ROOT_KEY = 'XBDM';

 

NVL && COALESCE

Oracle：COALESCE()、NVL()

MySQL：COALESCE()

-- COALESCE函数在MySQL跟Oracle都适用，NVL函数在Oracle中适用，COALESCE可替换NVL。

-- Oracle
SELECT NVL(SUCCESS_VISIT,0) FROM table;

-- MySQL
SELECT COALESCE(SUCCESS_VISIT,0) FROM table;

 

TO_DATE && STR_TO_DATE

-- Oracle
SELECT TO_DATE(field,'yyyy-MM-dd') from dual;
SELECT TO_DATE(field,'yyyy-MM-dd HH24:mi:ss') from dual;

-- MySQL
SELECT STR_TO_DATE(field,'%Y-%m-%d');
SELECT STR_TO_DATE(field,'%Y-%m-%d %H:%i:%s');

 

TO_CHAR && DATE_FORMAT

-- Oracle
SELECT TO_CHAR(field,'yyyy-MM-dd');
SELECT TO_CHAR(field,'yyyy-MM-dd HH24:mi:ss');

-- MySQL
SELECT DATE_FORMAT(field,'%Y-%m-%d');
SELECT DATE_FORMAT(field,'%Y-%m-%d %H:%i:%s');

 

TO_CHAR && CONVERT

-- Oracle：
SELECT TO_CHAR(field);

-- MySQL：
SELECT CONVERT(field,CHAR) from dual;

 

TO_NUMBER && CONVERT

-- Oralce
SELECT TO_NUMBER(field) FROM A;

-- MySQL
SELECT CONVERT(field, UNSIGNED INTEGER) FROM A;

 

LIKE && CONCAT

-- Oracle：
SELECT A.* FROM A WHERE field like '%'|| #{keyword} ||'%';

-- MySQL：
SELECT A.* FROM A WHERE field like CONCAT('%',#{keyword},'%');

 

SYSDATE && NOW()

-- Oracle：
SELECT SYSDATE FROM DUAL;

-- MySQL：
SELECT NOW();

 

SYSTIMESTAMP && CURRENT_TIMESTAMP

-- Oracle：
SELECT SYSTIMESTAMP FROM DUAL;

-- MySQL：
SELECT CURRENT_TIMESTAMP;

 

DECODE

Oracle函数DECODE

-- Oracle
SELECT A.*,
       DECODE(A.KLX,
              '01',
              '居民身份证',
              '02',
              '居民户口簿',
              '03',
              '护照',
              '04',
              '军官证',
              '05',
              '驾驶证',
              '06',
              '港澳居民来往内地通行证',
              '07',
              '台湾居民来往内地通行证',
              '其他')
  FROM A;

 

MySQL函数IF可实现Oracle中DECODE效果

-- MySQL
SELECT A.*,
       IF(A.KLX = '01',
          '居民身份证',
          IF(A.KLX = '02',
             '居民户口簿',
             IF(A.KLX = '03',
                '护照',
                IF(A.KLX = '04',
                   '军官证',
                   IF(A.KLX = '05',
                      '驾驶证',
                      IF(A.KLX = '06',
                         '港澳居民来往内地通行证',
                         IF(A.KLX = '07', '港澳居民来往内地通行证', '其他')))))))
  FROM A;

 

正则表达式

-- Oracle
SELECT CASE
         WHEN REGEXP_LIKE(field, '^[[:digit:]]+$') THEN
          field
         ELSE
          NULL
       END
  FROM DUAL;
  
-- MySQL
SELECT CASE
         WHEN field REGEXP '^[[:digit:]]+$' THEN
          field
         ELSE
          NULL
       END;

 

日期区间计算

Oracle日期区间计算，

SELECT A.*
  FROM A
 WHERE field >= to_timestamp('2022-01-01', 'yyyy-mm-dd hh24:mi:ss')
   AND field <= to_timestamp('2022-09-30', 'yyyy-mm-dd hh24:mi:ss');

-- 等价于
SELECT A.*
  FROM A
 WHERE TO_CHAR(field, 'yyyy-mm-dd') BETWEEN '2022-01-01' AND '2022-09-30';

 

MySQL日期区间计算，

SELECT A.* FROM A WHERE DATE(field) BETWEEN '2022-01-01' AND '2022-09-30';

-- 等价于
SELECT A.*
  FROM A
 WHERE DATE_FORMAT(field, '%Y-%m-%d') >=
       DATE_FORMAT('2022-01-01', '%Y-%m-%d')
   AND DATE_FORMAT(field, '%Y-%m-%d') <=
       DATE_FORMAT('2022-09-30', '%Y-%m-%d');

 

MySQL 实现 row_number() over()

MySQL 实现递归查询

 

参考

https://blog.csdn.net/yxd179/article/details/121630628