mysql数据迁移至浪潮数据库

2022-08-11 19:59:24

记录一次数据迁移的过程和遇到的坑。

浪潮数据库是个国产数据库，用法跟Oracle有些类似。数据迁移的时候，百度了很久一直没找到相关文档，只能仿照Oracle做数据迁移了

1.添加第三方jar包（inspur11-jdbc-dbg.jar）

注意：打包时如果要将第三方jar包一起打上，要修改pom.xml

可以参考：https://blog.csdn.net/LGHunter/article/details/82631483

2.修改yml配置文件

spring:
    datasource:
    druid:
      stat-view-servlet:
        loginUsername: 
        loginPassword: 
    dynamic:
      druid: # 全局druid参数，绝大部分值和默认保持一致。(现已支持的参数如下,不清楚含义不要乱设置)
        # 连接池的配置信息
        # 初始化大小，最小，最大
        initial-size: 5
        min-idle: 5
        maxActive: 20
        # 配置获取连接等待超时的时间
        maxWait: 60000
        # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
        timeBetweenEvictionRunsMillis: 60000
        # 配置一个连接在池中最小生存的时间，单位是毫秒
        minEvictableIdleTimeMillis: 300000
        validationQuery: SELECT 1 FROM DUAL
        testWhileIdle: true
        testOnBorrow: false
        testOnReturn: false
        # 打开PSCache，并且指定每个连接上PSCache的大小
        poolPreparedStatements: true
        maxPoolPreparedStatementPerConnectionSize: 20
        # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，‘wall‘用于防火墙
        filters: stat,wall,slf4j
        # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
        connectionProperties: druid.stat.mergeSql\=true;druid.stat.slowSqlMillis\=5000
      datasourcenum: 1  #数据源数量，注意！！！！数据源数量和下一行的datasource数量对应！！！！！！
      datasource:
        master:
          url: jdbc:inspur:thin:@IP:端口:ecidi
          username: bi
          password: 123456
          driver-class-name: com.inspur.jdbc.KdDriver

3.数据迁移

最麻烦的过程了，navicat不支持浪潮数据库不然直接用navicat的做数据迁移就方便很多。

只好折中安装完Oracle数据库后，将mysql的数据迁移至Oracle中；再将Oracle的数据导出成sql，导入到浪潮数据库

　　3.1 mysql的数据迁移至Oracle

　　　　打开navicat-工具-数据传输

　　　　下一步，可在选项中修改传输配置（跳过报错）

　　　　选择要同步的表，开始。执行完毕后关闭

　　3.2 Oracle的数据导出成sql，导入到浪潮数据库

　　　　使用navicat导出oracle的数据和结构，sql文件需要修改以下几点：

1. 浪潮数据库不识别“·”，需要将 ·字段· 修改为字段
2. 将表名左右的“”删除
3. 浪潮数据库不支持varchar2以及nvarchar2，将字符串类型修改为varchar

DROP TABLE buildingaccount;
CREATE TABLE buildingaccount (
  id VARCHAR(64) NOT NULL ,
  pk NUMBER(11) NOT NULL ,
  name VARCHAR(256) ,
  code VARCHAR(256) ,
  building_end DATE ,
  building_start DATE ,
  building_pers_usernames VARCHAR(256) ,
  building_pers_names VARCHAR(256) ,
  building_org VARCHAR(256) ,
  design_org VARCHAR(256) ,
  description NCLOB ,
  inputer_name VARCHAR(256) ,
  inputer_fullname VARCHAR(256) ,
  del_flag VARCHAR(2) ,
  create_by VARCHAR(32) ,
  create_time DATE ,
  update_by VARCHAR(32) ,
  update_time DATE ,
  corp_code VARCHAR(64) ,
  prj_code VARCHAR(64) 
)
TABLESPACE bi
LOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 1
STORAGE (
  INITIAL 65536 
  NEXT 1048576 
  MINEXTENTS 1
  MAXEXTENTS 2147483645
  BUFFER_POOL DEFAULT
)
PARALLEL 1
NOCACHE
DISABLE ROW MOVEMENT
;
COMMENT ON COLUMN buildingaccount.id IS ‘主键ID‘;
COMMENT ON COLUMN buildingaccount.pk IS ‘pk‘;
COMMENT ON COLUMN buildingaccount.name IS ‘名称‘;
COMMENT ON COLUMN buildingaccount.code IS ‘编码‘;
COMMENT ON COLUMN buildingaccount.building_end IS ‘施工起始时间‘;
COMMENT ON COLUMN buildingaccount.building_start IS ‘施工结束时间‘;
COMMENT ON COLUMN buildingaccount.building_pers_usernames IS ‘建筑物主人用户名‘;
COMMENT ON COLUMN buildingaccount.building_pers_names IS ‘建筑物主人名称‘;
COMMENT ON COLUMN buildingaccount.building_org IS ‘施工单位‘;
COMMENT ON COLUMN buildingaccount.design_org IS ‘设计单位‘;
COMMENT ON COLUMN buildingaccount.description IS ‘结构概况‘;
COMMENT ON COLUMN buildingaccount.inputer_name IS ‘请假人用户编码‘;
COMMENT ON COLUMN buildingaccount.inputer_fullname IS ‘请假人姓名‘;
COMMENT ON COLUMN buildingaccount.del_flag IS ‘删除状态（0，正常，1已删除）‘;
COMMENT ON COLUMN buildingaccount.create_by IS ‘创建人‘;
COMMENT ON COLUMN buildingaccount.create_time IS ‘创建时间‘;
COMMENT ON COLUMN buildingaccount.update_by IS ‘更新人‘;
COMMENT ON COLUMN buildingaccount.update_time IS ‘更新时间‘;
COMMENT ON COLUMN buildingaccount.corp_code IS ‘所属租户‘;
COMMENT ON COLUMN buildingaccount.prj_code IS ‘所属项目‘;

　　　　打开命令行输入 kdsql bi/123456 登录数据库

　　　　@路径/xx.sql导入数据

　　　　注意：因为Oracle不支持主键自增设置，需要另外写触发器；导入数据完成后字段的默认值需要重新设置

创建序列
CREATE SEQUENCE EQUIPACCOUNT_SEQ
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9999999999999999999999999999
NOCYCLE 
CACHE 20
NOORDER

创建触发器
create or replace trigger BI.trg_equipaccount before insert on equipaccount for each row 
begin 
if(: new.pk is null) then
select fan_relation_seq.nextval into : new.pk from dual;
end if;
end;

4.修改mybatis-plus配置（mybatis层字段值为null时处理，不添加如果传入字段为null时会报错）

　　mybatis-plus.jdbc-type-for-null: ‘null‘

#mybatis plus 设置
mybatis-plus:
  mapper-locations: classpath*:**/xml/*Mapper.xml
  global-config:
    db-config:
      logic-delete-value: 1
      logic-not-delete-value: 0
  configuration:
    #浪潮数据库null值处理
    jdbc-type-for-null: ‘null‘

5.修改分页查询接口

　　mybatis-plus的增删改接口支持Oracle数据，同样也适配浪潮数据库。不过分页查询接口需要修改，根本原因在于Oracle不支持limit字段。分页是根据rownum来实现。

　　可以参考我的sql

<select id="queryEquipAccountList" resultType="com.ecidi.modules.account.entity.EquipAccount">
        select *
        from (select at.* , rownum as rowno
        from(select a.* from equipaccount a
        <where>
            <if test="params.id !=null and params.id !=‘‘">
                and id like ‘%${params.id}%‘
            </if>
            <if test="params.pk !=null and params.pk !=‘‘">
                and pk like ‘%${params.pk}%‘
            </if>
            <if test="params.name !=null and params.name !=‘‘">
                and name like ‘%${params.name}%‘
            </if>
            <if test="params.code !=null and params.code !=‘‘">
                and code like ‘%${params.code}%‘
            </if>
            <if test="params.productDate !=null and params.productDate !=‘‘">
                and product_date like ‘%${params.productDate}%‘
            </if>
            <if test="params.installDate !=null and params.installDate !=‘‘">
                and install_date like ‘%${params.installDate}%‘
            </if>
            <if test="params.shipmentDate !=null and params.shipmentDate !=‘‘">
                and shipment_date like ‘%${params.shipmentDate}%‘
            </if>
            <if test="params.lifeUnit !=null and params.lifeUnit !=‘‘">
                and life_unit like ‘%${params.lifeUnit}%‘
            </if>
            <if test="params.lifeValue !=null and params.lifeValue !=‘‘">
                and life_value like ‘%${params.lifeValue}%‘
            </if>
            <if test="params.productOrg !=null and params.productOrg !=‘‘">
                and product_org like ‘%${params.productOrg}%‘
            </if>
            <if test="params.installOrg !=null and params.installOrg !=‘‘">
                and install_org like ‘%${params.installOrg}%‘
            </if>
            <if test="params.equipMasterUsernames !=null and params.equipMasterUsernames !=‘‘">
                and equip_master_usernames like ‘%${params.equipMasterUsernames}%‘
            </if>
            <if test="params.equipMasterNames !=null and params.equipMasterNames !=‘‘">
                and equip_master_names like ‘%${params.equipMasterNames}%‘
            </if>
            <if test="params.description !=null and params.description !=‘‘">
                and description like ‘%${params.description}%‘
            </if>
            <if test="params.alterRecord !=null and params.alterRecord !=‘‘">
                and alter_record like ‘%${params.alterRecord}%‘
            </if>
            <if test="params.techparamName !=null and params.techparamName !=‘‘">
                and techparam_name like ‘%${params.techparamName}%‘
            </if>
            <if test="params.techparamValue !=null and params.techparamValue !=‘‘">
                and techparam_value like ‘%${params.techparamValue}%‘
            </if>
            <if test="params.techparamUnit !=null and params.techparamUnit !=‘‘">
                and techparam_unit like ‘%${params.techparamUnit}%‘
            </if>
            <if test="params.techparamAccessory !=null and params.techparamAccessory !=‘‘">
                and techparam_accessory like ‘%${params.techparamAccessory}%‘
            </if>
            <if test="params.inputerName !=null and params.inputerName !=‘‘">
                and inputer_name like ‘%${params.inputerName}%‘
            </if>
            <if test="params.inputerFullname !=null and params.inputerFullname !=‘‘">
                and inputer_fullname like ‘%${params.inputerFullname}%‘
            </if>
            <if test="params.createBy !=null and params.createBy !=‘‘">
                and create_by like ‘%${params.createBy}%‘
            </if>
            <if test="params.minStartTime !=null and params.minStartTime !=‘‘">
                and create_time >= #{params.minStartTime}
            </if>
            <if test="params.maxStartTime !=null and params.maxStartTime !=‘‘">
                and create_time &lt;= #{params.maxStartTime}
            </if>
            and del_flag =0
            order by create_time
        </where>) at
        <where>
            <if test="params.end!=null and params.end!=‘‘">
                and  rownum &lt;= ${params.end}
            </if>
        </where>) att
        <where>
            <if test="params.start!=null and params.start!=‘‘">
                and  att.rowno >= ${params.start}
            </if>
        </where>
    </select>

mysql数据迁移至浪潮数据库

码农公寓

相关文章