MySQL兼容Oracle，MyBatis该怎么改SQL语句

Open-CV

已于 2024-04-09 15:00:58 修改

阅读量1.3k

点赞数 18

文章标签： mysql oracle mybatis

于 2024-01-22 15:48:51 首次发布

本文链接：https://blog.csdn.net/qq_51304773/article/details/135748269

版权

小白记录一下学习过程

1.MySQL中的反引号“ ` ”

就是键盘ESC下面英文输入法状态下敲出的这个符号，在MySQL中用它来转义一些保留字符，但是Oracle中没有这个符号。好像Oracle中没有保留字符当列名这一说法，只能修改字段名称了（我翻了半天网也是没查到）。有些人写SQL是习惯在表和字段名上加上反引号，所以不是用Oracle保留字段命名的话，直接去掉就行了。

2.MySQL中的CONCAT函数

这个就有意思了，我目前见的项目（其实就一个）中用到的这个函数都是这种拼接模糊查询条件用的

MySQL写法

SELECT 字段 
FROM 表名 
WHERE 字段名 LIKE CONCAT('%',#{param},'%') ESCAPE '/'

Oracle写法

SELECT 字段 
FROM 表名 
WHERE 字段名 LIKE CONCAT(CONCAT('%',#{param}),'%') ESCAPE '/'

这样写是因为CONCAT函数再Oracle中只能有两个参数，等于是先拼接%和一个参数，然后再拼后面的%，这样写能实现，但是还有一种更好的写法就是使用MyBatis的bind标签（这个让我眼前一亮）

<select id="selectList"  resultMap="userMap">
	<bind name="pattern" value="'%'+param+'%'"/>
	SELECT 字段 
	FROM 表名 
	WHERE 字段名 LIKE #{pattern}
</select>

其中name是参数别名，value是传入的条件或者实体类字段，像可以进行字符串拼接的操作

3.MYSQL中的IFNULL、IF函数，TRUE和FALSE

这个Oracle有对应的NVL函数，兼容这两种数据库的写法，我想到的是用CASE WHEN ，像下面这样

IFNULL(字段或表达式, 0)
CASE WHEN 字段或表达式 IS NULL THEN FALSE ELSE TRUE END
<!--可以改成-->
CASE WHEN 字段或表达式 IS NULL THEN 0 ELSE 1 END

注意MYSQL中的TRUE和FALSE在Oracle中要写1和0，因为两个数据库查出来都是数字类型的1和0

4.MySQL中 QUARTER、YEAR、NOW函数的使用获取数字类型的季度，年份，系统时间；DATE_SUB（DATE, INTERVAL n DAY）函数、TO_DAYS函数

这个很让我头疼，这些用来做时间范围查询是真的好用，Oracle中也有对应的函数，但是如果兼容呢，这个我最后解决的方式是将这些条件定义在了查询条件中，XML中只写最简单的大于小于等于比较。举几个例子吧

判断是否是今天

Service中可以这样定义出条件，可以简单写个工具类

// 获取当前时间
LocalDateTime localDateTime = LocalDateTime.now();
// 设置时间查询条件，规则是早上0点到晚上23点之间都算是今天
// 今天0点
dto.setTodayStart(LocalDateTime.of(localDateTime.getYear(), localDateTime.getMonth(), localDateTime.getDayOfMonth(),0,0));
// 今天23点
dto.setTodayEnd(LocalDateTime.of(localDateTime.getYear(), localDateTime.getMonth(), localDateTime.getDayOfMonth(),23,0));

SQL就可以直接比较了

AND TO_DAYS(TIME) = TO_DAYS(NOW())
<!--修改-->
AND TIME <![CDATA[ >= ]]> #{todayStart}
AND TIME <![CDATA[ <= ]]> #{todayEnd}

判断是否是本季度（恕我无能，找不到解法，要是有人会的话可以教一下我谢谢）

AND QUARTER(TIME) = QUARTER(NOW()) 
AND YEAR(TIME)=YEAR(NOW())

过去x天（7、15、30天）

这个和第一个例子有点类似的

// 获取当前时间
LocalDateTime localDateTime = LocalDateTime.now();
// 过去7天
dto.setDaysLastFifteen(localDateTime.minusDays(7L));
// 过去15天
dto.setDaysLastFifteen(localDateTime.minusDays(15L));
// 过去30天
dto.setDaysLastFifteen(localDateTime.minusDays(30L));

SQL还是直接比较大小就行

其实还有一种解决方案是可以根据数据库类型进行判断，去写不同的SQL，我感觉这样是最省事的

（2024.01.25补充）

昨天讨论了一下，感觉豁然开朗，其实可以将MySQL中日期转换字符串，获取当前日期，季度和年份的这些函数，在Oracle中自定义同名函数，像下面这样
需要注意的是，Oracle中创建函数需要管理员授权创建函数的权限

1.DATE_FROMAT函数

CREATE OR REPLACE FUNCTION DATE_FORMAT(p_date IN DATE, mysql_format IN VARCHAR2) RETURN VARCHAR2  
IS formatted_date VARCHAR2(30); 
   oracle_format VARCHAR2(30);  
BEGIN
	
  oracle_format := REPLACE(mysql_format, '%Y', 'yyyy');  
  oracle_format := REPLACE(oracle_format, '%m', 'mm');  
  oracle_format := REPLACE(oracle_format, '%d', 'dd');  
  oracle_format := REPLACE(oracle_format, '%H', 'HH24');  
  oracle_format := REPLACE(oracle_format, '%i', 'MI');  
  oracle_format := REPLACE(oracle_format, '%s', 'SS');  
  SELECT TO_CHAR(p_date, oracle_format) INTO formatted_date FROM dual;  
-- formatted_date := oracle_format;
  RETURN formatted_date; 
 
END DATE_FORMAT;

有了这个自定义函数，那在Oracle中其实如果需要实现MySQL中的YEAR函数，格式只需要传‘%Y’就可以了，当然如果想统一也可以定义出来

2.NOW函数

CREATE OR REPLACE FUNCTION NOW RETURN DATE AS
BEGIN
	-- 返回Oracle的获取系统时间函数函数
  RETURN SYSDATE;
END;

3.QUARTER函数

CREATE OR REPLACE FUNCTION TESTDATA.QUARTER(PARAM1 DATE) RETURN NUMBER AS
BEGIN
  RETURN TO_CHAR(PARAM1, 'Q');
END;

MySQL中文排序兼容Oracle写法(CONVERT函数用NLSSORT函数替换)

先来看看MySQL中文排序的写法

 ORDER BY CONVERT('字段名' USING GBK)

而在Oracle中有同名函数，但是参数不一样，我就不介绍了有很多资料可以搜，常用的用法也是字符编码转换，用法如下

 ORDER BY CONVERT('字段名', 'ZHS16GBK')

或者用NLSSORT（Oracle独有的函数，可以用来进行中文排序，按照拼音、笔划、部首）

-- 按照拼音排序
 ORDER BY NLSSORT('字段名','NLS_SORT=SCHINESE_PINYIN_M')

解决方案

先分析一下，如果能在两种数据库上自定义同名且参数一致的函数就再好不过，函数内部用各自的方法实现字符集转换，但是我在Oracle上创建函数的时候遇到了问题，就是指定了字符集后无法用字符串（VARCHAR2）接收。
解决：在MySQL中自定义NLSSORT函数
同样，需要拥有创建函数的权限
写法如下

CREATE FUNCTION NLSSORT(PARAM1 VARCHAR(1024),PARAM2 VARCHAR(128))
    RETURNS VARCHAR(1024) CHARACTER SET GBK
BEGIN
    RETURN PARAM1;
END;

用法：同Oracle的用法，参数2只是为了保证和Oracle一致，无实际用处

ORDER BY NLSSORT('字段名','NLS_SORT=SCHINESE_PINYIN_M') ASC

中文排出来的效果和用CONVERT函数（USING GBK）是一样的
效率会有所下降，我在本地MySQL数据库测试的结果是，372条数据，使用CONVERT转换，实现排序的时间是10ms，获取5ms;
用NLSSORT转换，实现排序的时间是18ms，获取8ms;

！！！（2024-04-08补充）解决方案2

之前这个函数我也不知道为啥，反正用不了了，现在是改到了Service层处理，因为是工作中遇到的问题所以主要还是举实际例子，主要需求即:根据实体类的某个字段进行中文排序，例如客户名称，现修改如下：

//用到的是这个包，别引错啦，如果用java.text包，有些生僻字是排不出来的
//（这个还能对多音字进行排序，赞！）
import com.ibm.icu.text.Collator;
import com.ibm.icu.util.ULocale;

//这里是查询数据库数据，返回List集合
List<SuspectedVo> suspectedList = opportunityMapper.suspectedList(customerName);
 //按照中文拼音首字母，根据疑似客户名称进行排序
 suspectedList.sort((o1, o2) -> {
     Comparator<Object> comparator = Collator.getInstance(ULocale.CHINA);
     return comparator.compare(o1.getCustomerName(), o2.getCustomerName());
 });