Oracle数据库迁移到MySQL数据库
1 字段类型差异
Oracle | MySQL | 解决方案 |
varchar2 | varchar | varchar |
date | date/time/datetime/timestamp | datetime |
clob | tinytext/text/mediumtext/longtext | longtext |
blob | tinyblob/blob/mediumblob/longblob | longblob |
number | tinyint/smallint/mediumint/int/bigint decimal/float/double | Oracle number的取值范围为1-38位 NUMBER(1-2)–TINYINT， NUMBER(3-4)–SMALLINT ， NUMBER(5-6)–MEDIUMINT， NUMBER(7-9)–INT， NUMBER(10-19)–BIGINT， NUMBER(20+)或NUMBER(int,int)–DECIMAL 若ORM框架用的Hibernate，建议使用decimal代替number |
2 函数差异(附部分案例)
差异项 | Oracle | MySQL | 案例连接 |
分页 | ROWNUM | limit或自定义变量 | |
空值转换 | NVL(s1,r1)：若s1为null，返回r1，否则返回s1; SELECT NVL(NULL, ‘R’) FROM DUAL; SELECT NVL(‘S’, ‘R’) FROM DUAL; Oracle也支持coalesce函数 | ifnull(s1,r1)、coalesce(s1,r1) SELECT coalesce(NULL, ‘R’); SELECT coalesce(‘S’, ‘R’); | |
空值转换2 | NVL2(s1,r1,r2)：若s1为null，返回r2，否则返回r1; SELECT NVL2(NULL, ‘R1’, ‘R2’) FROM DUAL; SELECT NVL2(‘S’, ‘R1’, ‘R2’) FROM DUAL; | 可以用case when代替 select case when null is null then ‘R2’ else ‘R1’ end; select case when ‘S’ is null then ‘R2’ else ‘R1’ end; | |
截取 | TRUNC()可以用于数值、日期 | 对于截取日期可以用date_format+str_to_date实现； 对于数字截取可用truncate() | |
序列 | 序列.NEXTVAL | 无序列 | |
系统日期 | SYSDATE | sysdate()、NOW() | |
添加月份 | ADD_MONTHS(date, int) SELECT add_months(to_date(‘2021-05-19’,‘yyyy-MM-dd’), 1) FROM dual; | date_add(date, INTERVAL int month) select date_add(str_to_date(‘2021-05-19’,’%Y-%m-%d’), interval 1 month); | |
两日期间隔的月份 | MONTHS_BETWEEN(date1,date2) | （date1-date2的天数)/31，保留9位小数 | |
类型转换 | TO_DATE() SELECT TO_DATE(‘2021-05-19 17:15:33’,‘yyyy-MM-dd hh24:mi:ss’) FROM DUAL; | str_to_date() select str_to_date(‘2021-05-19 17:15:33’,’%Y-%m-%d %H:%i:%s’); | |
TO_CHAR(date) TO_CHAR(number) | date_format(date) cast(number as char) convert(number,char) | SELECT CONVERT(‘test’, CHAR CHARACTER SET utf8); SELECT CAST(‘test’ AS CHAR CHARACTER SET utf8); | |
字符串截取 | SUBSTR(str,int,int) 第二个参数可正负0 | SUBSTR(str,int,int) 第二个参数可正负，若为0则返回空字符串 | |
INSTR | INSTR(str,subStr,pos)三个参数，可指定开始位置，pos=0返回0，pos<0，从尾面开始查找 | instr(str,subStr)两个参数 locate(subStr,str,pos)：pos<=0时返回0 | |
字符串连接 | ||、CONCAT(s1,s2)，concat只能有两个参数 | concat(s1,s2,s3,…)可有多个参数 | |
字符串替换 | replace(str, substr) replace(str,substr,repstr) | 仅支持replace(str,substr,repstr) | |
行转列 | WM_CONCAT、LISTAGG | group_concat | |
条件选择 | DECODE | case when | |
正则 | REGEXP_LIKE(expr,pat) | expr REGEXP pat |
3 其他差异
差异项 | Oracle | MySQL |
全局临时表 | 支持 | 不支持 |
表分区 | 支持 | 支持，但有主键是分区字段必须是主键字段 |
FULL JOIN | 支持 | 不支持，left join + right join |
DELETE | 可以没有from关键字 可以用exists | 必须有from 不可以用exists |
正则表达式 | REGEXP_SUBSTR（） | REGEXP |
集合相减 | Minus | 无，可用not in |
NULL和’’ | ORACLE认为’‘等同于NULL LENGTH(’’)为NULL TO_DATE(’’,‘YYYYMMDD’)为空 TO_NUMBER(’’,1)为NULL oracle中’’,数值类型的插入，NULL oracle中’’,字符类型，NULL oracle中’’,时间类型，NULL | NULL和’‘不同 LENGTH(’’)为0 TO_DATE(’’,‘YYYYMMDD’)，报错 TO_NUMBER(’’,1)，报错 MySQL中’’ 数值类型的插入，报错 MySQL中’’ 字符类型，’’ MySQL中’’ 时间类型，报错 |
合并 | MERGE INTO | 无此语法，用存储过程 |
group by having | oracle having语句可以在group by之前 | MySQL having语句必须在group by之后 |
去重 | unique、distinct | distinct |
不等于 | Oracle中 ! =，< >操作符中间允许有空格 | MySQL中不可以，且字符类型与数字类型不可比较 |
别名 | 派生表（即子查询形成的视图）有无别名皆可 ，别名可重复 | MySQL派生表必须有别名 ，别名不可重复，DELETE不能有别名 |
其它 | 支持中文空格，中文逗号 | 不支持 |
数字+and/or 之间可以无空格 | 不支持 |
