tidb与mysql兼容 oceanbase mysql兼容

转载

mob64ca14147fe3 2023-10-27 11:51:55

文章标签 tidb与mysql兼容 mysql oracle big data 数据库 文章分类 MySQL 数据库

最近公司的项目需要用oceanBase oracle数据库。公司原来用的是mysql数据库，中间换的国产的达梦数据库。其中一些语法放在oceanBase oracle 里面不适用，所以需要对程序，数据库做一些修改，使项目能在oceanBase oracle 上面正常运行起来。下面会挨个说一下遇到的问题，和解决的方法。这里做一下记录。

目前发想的问题主要有：

1.表里面数据库关键字的使用，

目前发现的有resource 和size. 对于数据库关键字，我们只能去修改代码，把字段信息改了。好在涉及到的地方不是很多。

2.数据库字段类型的对应。

对于数据字段类型的匹配可以直接看mysql与oracle数据类型对照表来解决。

3.对于数据库特殊函数的兼容。

我这里遇到的有CHAR_LENGTH，CONCAT，IFNULL，ISNUMERIC，LEFT，RIGHT，YEAR 具体解决方式是在数据库里面定义同名函数来代替。具体实现为

CREATE OR REPLACE function CHAR_LENGTH(
parm1 in VARCHAR) 
return integer as 
v1 int; 
begin 
select length(parm1) into v1 from dual;
RETURN v1;
end;
-- CONCAT函数 oceanBase 只支持两个单数，这里定义一个 多个参数的同名函数，给参数的默认值设置为-- ''.  就能实现其它数据库CONCAT多个参数的功能。不过调用的时候需要加上用户名加函数名称才可以，直-- 接用CONCAT 会被识别成系统默认的CONCAT。 
-- 调用方式：select SYS.CONCAT('a','b','c') from dual;
CREATE OR REPLACE function CONCAT(
param1 in VARCHAR DEFAULT '', 
param2 in VARCHAR DEFAULT '', 
param3 in VARCHAR DEFAULT '', 
param4 in VARCHAR DEFAULT '', 
param5 in VARCHAR DEFAULT '', 
param6 in VARCHAR DEFAULT '',
param7 in VARCHAR DEFAULT '',
param8 in VARCHAR DEFAULT '',
param9 in VARCHAR DEFAULT '',
param10 in VARCHAR DEFAULT '',
param11 in VARCHAR DEFAULT '',
param12 in VARCHAR DEFAULT '',
param13 in VARCHAR DEFAULT '',
param14 in VARCHAR DEFAULT '',
param15 in VARCHAR DEFAULT ''
) 
return varchar2 as 
v1 int; 
begin 
return param1 || param2 || param3 || param4 || param5 || param6 || param7 || param8 || param9 || param10 || param11 || param12 || param13 || param14 || param15;
end;
CREATE OR REPLACE function IFNULL(
param1 in STRING, 
param2 in STRING) 
return STRING as 
v1 int; 
v2 STRING;
begin 
select nvl(param1, param2) INTO v2 from dual;
return v2;
end;

CREATE OR REPLACE function ISNUMERIC(
param1 in STRING) 
RETURN   NUMBER
IS
   v_str  FLOAT ;
begin 
 IF   param1   IS   NULL
    THEN
       RETURN   0 ;
    ELSE
       BEGIN
          SELECT  TO_NUMBER ( param1 )
            INTO  v_str
            FROM  DUAL;
      EXCEPTION
          WHEN  INVALID_NUMBER
          THEN
             RETURN   0 ;
       END ;
       RETURN   1 ;
    END   IF ;
end;
-- 由于LEFT 也是oceanBase 的系统保留关键字，所以调用的时候也需要在函数名称前面加上用户名。
-- select SYS.LEFT('asdfasdf', 3) from dual ; 
CREATE OR REPLACE function LEFT(
param in STRING, 
num in integer) 
return varchar2 as 
v1 int; 
v2 STRING;
begin 
select SUBSTR(param, 1, num) into v2 from dual;
RETURN v2;
end;

CREATE OR REPLACE function RIGHT(
param in STRING, 
num in integer) 
return varchar2 as 
v1 int; 
v2 STRING;
begin 
select SUBSTR(param, LENGTH(param) -(num -1), num) into v2 from dual;
RETURN v2;
end;
CREATE OR REPLACE function YEAR(
param1 in DATE) 
return integer as 
v1 int; 
begin 
select  to_number(TO_CHAR(param1, 'yyyy')) into v1 from dual ;
RETURN v1;
end;

其中函数 CONCAT 和函数 LEFT 由于是系统关键字，所以调用的时候需要在函数前面加上用户名才能正常调用。调用方式为

select SYS.CONCAT('a','b','c') from dual;

select SYS.LEFT('asdfasdf', 3) from dual ;

需要在函数前面加上用户名 SYS. 这样的话在项目里面所有用到这两个函数的地方都需要单独修改。为了避免大批量的修改，给mybatis加了一个拦截器。在拦截器里面首先判断当前数据库环境，如果是oceanBase 则匹配sql语句中的 CONCAT( 和 LEFT( 替换成 SYS.CONCAT( 和SYS.LEFT( 具体代码如下

@Component
@Intercepts(@Signature(type = StatementHandler.class, method = "prepare", args = { Connection.class,Integer.class }))
public class ObFunctionInterceptor implements Interceptor {
    @Autowired
    @Lazy
    private SqlSessionTemplate sqlSessionTemplate;

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        String databaseId = sqlSessionTemplate.getConfiguration().getDatabaseId();
        //判断当前数据库版本是不是oceanBase版本，因为用的是oceanBase oracle 我们这里直接当做         oracle适配的
        if (DBType.Oracle.getCode().equals(databaseId) && invocation.getTarget() instanceof StatementHandler) {
            StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
            BoundSql boundSql = statementHandler.getBoundSql();
            //获取sql语句
            String sql = boundSql.getSql();
            MetaObject metaStatementHandler = SystemMetaObject.forObject(statementHandler);
            //替换CONCAT(
            if(sql.contains("CONCAT(")){
                sql = sql.replaceAll("CONCAT\\(", "SYS.CONCAT(");
            }
            //替换LEFT(
            if(sql.contains("LEFT(")){
                sql = sql.replaceAll("LEFT\\(", "SYS.LEFT(");
            }
            //把修改后的sql设置回去。
            metaStatementHandler.setValue("delegate.boundSql.sql", sql);
        }
        return invocation.proceed();
    }

    @Override
    public Object plugin(Object target) {
        if (target instanceof StatementHandler) {
            return Plugin.wrap(target, this);
        } else {
            return target;
        }
    }

    @Override
    public void setProperties(Properties properties) {

    }
}

4.对于自增ID的处理。

oracle数据库不支持ID自增，想要自增只能通过一些方法来实现。实现主要有两个思路，一种是数据库层面，用序列来实现ID自增，另一种是代码层面，通过查询max值来实现自增。两种方法各有优劣，一个节省资源，对数据库依赖较高。第二种有一定的资源浪费，不过不依赖数据库，可以非常容易的进行数据库平移。这里我用的第一种方式，第二种后面有机会说一下。

数据层面用序列实现自增，就是给表加一个触发器。在插入的时候获取序列的nextval作为ID。

create or replace TRIGGER TG_XXL_JOB_GROUP BEFORE
	INSERT ON "XXL_JOB_GROUP"
	FOR EACH ROW
	ENABLE
BEGIN
 --your trigger body
   select XXL_JOB_GROUP_SEQ.nextval into:new.id from dual;
END

5.对于分页的处理。

不同数据库分分页方式不一样，mysql用的是limit, oracle这里用的是rownum. 在这里用了一个分页插件PageHelper. 它可以根据不同的数据库来生成对应的分页语句。具体应用为

导入PageHelper包

<!--pagehelper-->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.3.0</version>
            <exclusions>
                <exclusion>
                    <groupId>org.mybatis.spring.boot</groupId>
                    <artifactId>mybatis-spring-boot-starter</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

在配置文件设置对应参数

pagehelper:
# 关于pagehelper支持哪些数据库方言参考：com.github.pagehelper.page.PageAutoDialect
  helper-dialect: oracle     //这里最重要，这里是区分不同数据库类型来生成对应的分页语句的。
#  helper-dialect: postgresql
#  helper-dialect: mysql
#  helper-dialect: oscar
  reasonable: true
  support-methods-arguments: true
  params: count=countSql

配置号之后就可以使用了。使用方法很简单。

PageInfo<Map<String, Object>> pageInfo =
    PageHelper.startPage(pageDTO.getPage(), pageDTO.getLimit())
        .doSelectPageInfo(() ->accessRecordMapper.findAccessRecordByPageDTO(pageDTO));

6.对于VARCHAR字段类型的长度问题。

oracle数据类型一个urf8编码的中文需要占用3个字节。为了避免因为字段长度报错，需要吧所有表里面VARCHAR类型的数据的长度都扩大到3倍。这里我们用了一个数据库建模工具PdMan.用这个工具可以很容易的把字符串的数据类型增加3倍并生成DDL脚本。在程序里面的DDL也是根据数据库类型进行处理（长度* 3）。

7.对于CLOB字段类型问题。

oracle里面CLOB数据类型主要有两个问题一个是CLOB转字符串问题。另一个是CLOB做等于不等于的比较出错问题。第一个问题CLOB 转字符串问题，如果mybatis 的返回类型是一个Map.返回CLOB对应的字段类型是

com.alipay.oceanbase.jdbc.Clob

为了转字符串定义了一个Record类继承 HashMap作为查询返回的对象。重新里面的put() get()方法。对于Clob类型的数据进行转换。

public class DynamicRecord<K, V> extends HashMap<K, V> {

    /**
     *
     */
    private static final long serialVersionUID = 3870456784929696124L;

    @Override
    public V put(K key, V value) {
        if(value instanceof Clob){
            Clob cvalue = (Clob) value;
            value = (V) ClobToString(cvalue);
        }

        key = (K)key.toString().toLowerCase();// 结果集所有key都转小写
        return super.put(key, value);
    }

    @Override
    public V get(Object key) {
        V v = super.get(key);
        if (v != null)
            return v;
        return super.get(key.toString().toUpperCase());// 由于关键字返回的结果集key都是大写，如果没找到再转成大写找一遍
    }

    public static String ClobToString(Clob clob) {
        if(clob == null){
            return null;
        }
        String reString = "";
        try {
            //mysql处理clob转string的方式
            reString = clob.getSubString(1, (int) clob.length());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return reString;
    }
}

CLOB做等于不等于的比较出错的问题由于我们比较的值是一个确定的。我们这里直接用的一个函数dbms_lob.substr(),这个函数如果字段长度过长会截取一部分出来。由于我们的字段很短。用这个没有问题。