oracle 数据库 迁移至 mysql

oracle 数据库 迁移至 mysql

数据迁移工具 navicat premium 完成表从Oracle 迁移至 MySql

oracle 建表

CREATE TABLE demo (
  dir_id number,
  domain_id number
) 

oracle 语句块

DECLARE
  v_variater number := 10;
  CURSOR v_cursor(v_var number) IS
  SELECT col_a, col_b FROM demo WHERE id = v_var;
BEGIN
  FOR i v_cursor(v_variater ) LOOP
    INSERT INTO demo1 (column1, column2 ) VALUES (i.col_a, i.col_b);
  END LOOP;
END;

开启output显示功能

set serveroutput on size 10000;

数据格式化

Oracle中Date类型的字段无法直接转换到MySql，须进行如下处理

Date 类型 替换为 timestamp(6)
ALTER TABLE table_name MODIFY (column_name timestamp(6))

创建主键

ALTER TABLE table_name ADD CONSTRAINT pk_primary_name PRIMARY KEY (column_name)

Oracle 中 number 字段转换到 Mysql 中，为 decimal(65,30) 类型 ，须根据业务需要在MySql中修改为 int(11)、 bigint(20) 、float等

ALTER TABLE table_name MODIFY COLUMN column_name int(11)

批量修改表多个字段类型

ALTER TABLE table_name
CHANGE COLUMN column1 column1 int(11),
CHANGE COLUMN column2 column2 int(11),
CHANGE COLUMN column3 column3 float

清空表数据

TRUNCATE TABLE table_name

存储过程处理树级结构平表

循环

MYSQL 游标循环多执行一条数据的问题 解决方案

在循环体内，二次验证 done 是否有效

DECLARE tmpId int(11);
DECLARE done boolean DEFAULT true;
DECLARE cur CURSOR FOR SELECT id FROM dual;
DECLARE CONTINUE HANDLER FOR not FOUND SET done = false;
OPEN cur;
    WHILE done DO
        FETCH cur INTO tmpId;
        IF done THEN
            INSERT INTO test_table VALUES (tmpId);
         END IF;
    END WHILE;
CLOSE cur;

mysql 存储过程

注意，我们直接执行语句块会报错，暂定需要循环写入的操作都放在存储过程中。

DROP PROCEDURE IF EXISTS `demo_procedure`;

DELIMITER $$
CREATE DEFINER = `root`@`%` PROCEDURE `demo_procedure`()
BEGIN
……
END $$
DELIMITER ;

call demo_procedure();

修改数据

在 mysql 和 oracle 数据库中，用表 table_a 中的字段 column_a 的值，更新表 table_b 中与表 table_a 的 id 字段相匹配的记录的 column_b 的值为 column_a ，均可以采用如下语法

UPDATE table_b b SET b.column_b = (SELECT column_a FROM table_a a WHERE a.id = b.id);

注意：两表中基于id的匹配关系应当唯一匹配
如果mysql 运行在 safe-updates下，以上脚本无法执行，因为该模式下mysql不允许非主键条件更新或删除。
临时关闭语法如下：

SET SQL_SAFE_UPDATES = 0;
……
SET SQL_SAFE_UPDATES = 1;

删除数据

删除数据时，如果存在外键约束关系，将会报如下错误：

Error Code : 1701. Cannot truncate a table referenced in a foreign key constraint ( …… )

解决方案

SET foreign_key_checks = 0
……
SET foreign_key_checks = 1

分组数据排序取第一条

oracle使用 row_number() over(partition by 分组列 order by 排序列 desc) 函数
mysql 参考 《MySQL 实现对商品分类排序，并根据排序占比贴标签》