从迁移开发角度看差异：Oracle vs MySQL

随着近些年来数据库技术发展演进，及国内数据库日益活跃。越来越多的企业将数据库从传统商业数据库迁移到开源或国产数据库平台。本文对比了最为常见的一种情况，从Oracle迁移到MySQL需要关注的一些差异点。这方便应用研发在迁移之初做好必要的评估备。此外，因MySQL生态发展很广泛，很多数据库产品会将MySQL作为兼容的首选。因此，很多其他类型的数据库迁移，也可以参考此文内容。

❖ 本文MySQL功能，是以5.7版本为基础，8.0版本存在不小差异。

1. 功能差异说明

人生基本上就是两件事，选题和解题。最好的人生是在每个关键点上，既选对题，又解好题。人生最大的痛苦在于解对了题，但选错了题，而且还不知道自己选错了题。正如人生最大的遗憾就是，不是你不行，而是你本可以。

1).数据类型

2).函数与伪列

3).SQL（DML部分）

4).SQL（DDL部分）

5).SQL（其他部分）

2. 部分测试示例

人生基本上就是两件事，选题和解题。最好的人生是在每个关键点上，既选对题，又解好题。人生最大的痛苦在于解对了题，但选错了题，而且还不知道自己选错了题。正如人生最大的遗憾就是，不是你不行，而是你本可以。

下面针对常见部分函数用法，测试说明。前面为Oracle写法，后面为对应MySQL写法。部分示例引用表结构如下：

CREATE TABLE `t1` (

`id` int(11) DEFAULT NULL,

`val` varchar(10) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `t2` (

`id` int(11) DEFAULT NULL,

`val` varchar(10) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

✦ 空值判断

select id,nvl(val,'null') from t1;

select id,ifnull(val,'null') from t1;

select id,coalesce(val,'null') from t1;

Oracle使用nvl，MySQL使用ifnull或coalesce函数。

✦ 分支判断

select decode(id,1,'one',2,'two','other') from t1;

select case id when 1 then 'one' when 2 then 'two' else 'other' end from t1;

Oracle使用decode，MySQL使用case…when。

✦ 外连接

select * from t1,t2 where t1.id(+)=t2.id;

select * from t1 right join t2 on t1.id=t2.id;

左/右外连接，Oracle使用(+)这种特有写法，MySQL使用了标准SQL写法

✦ 字符串截取

select substr('123',0,2) from dual;

select substr('123',1,2) from dual;

截取子串操作，都使用substr函数，但起点位置不同。Oracle从0开始，MySQL从1开始。

✦ 字符串查找

select instr('abcde', 'bc') from dual;

select locate('bc', 'abcde') from dual;

Oracle使用insert函数，MySQL中换成locate函数，但是需要把前后位置与Oracle相反。

✦ 字符串长度

select length(‘abcd’) from dual;

select char_length('abcd') from dual;

Oracle使用length，MySQL使用char_length。

✦ 时间截取

select trunc(sysdate, 'iw') from dual;

select DATE_FORMAT( DATE_SUB(NOW(), INTERVAL WEEKDAY(NOW()) DAY), '%Y-%m-%d ') from dual;

Oracle用trunc，MySQL需要组合若干函数实现。

✦ 数字精度

select trunc(1.2345,2) from dual;

select convert(1.2345, decimal(6,2)) from dual;

✦ 字符转数字

select to_number('123') from dual;

select cast('123' as SIGNED ) from dual;

Oracle使用to_number，MySQL的cast函数则更为通用，可转换多种类型。

✦ 取向上最小整数

select ceil(1.234) from dual;

select ceiling(1.234) from dual;

获取不小于参数的最小整数，Oracle使用ceil函数，MySQL使用ceiling。

✦ 日期（月份加法）

select add_months(sysdate, 2) from dual;

select date_add(sysdate(), interval 2 month) from dual;

Oracle使用add_months，MySQL使用date_add

✦ 日期减法

select to_date('2021-11-01','yyyy-mm-dd') - to_date('2021-10-22','yyyy-mm-dd') from dual;

select datediff(cast('2021-11-01' as DATE) ,cast('2021-10-22' as DATE)) from dual;

Oracle使用运算符-，MySQL使用函数datediff。

✦ 字符串拼接

select concat('a','b') from dual;

select 'a'||'b' from dual;

select concat_ws('','a','b') from dual;

Oracle使用concat函数（只能限于两个参数）或运算符||，MySQL使用concat_ws函数（第一个参数为字符串间的拼接符）

✦ 日期转字符

select to_char(sysdate,'yyyy-mm-dd') from dual;

select date_format(now(),'%Y-%m-%d') from dual;

select cast(now() as CHAR(10)) from dual;

Oracle使用to_char函数，MySQL可使用date_format函数或cast函数。

✦ 字符转日期

select to_date('2020-06-20','yyyy-mm-dd') from dual;

select str_to_date('2020-06-20', '%Y-%m-%d') from dual;

select cast('2020-06-20' as DATE) from dual;

Oracle使用to_date函数，MySQL使用str_to_date函数或cast函数。

✦ 字符转整数

select to_number('123') from dual;

select cast('123' as SIGNED INTEGER) from dual;

Oracle使用to_number函数，MySQL使用cast函数。

✦ 空字符串处理

select nvl2(1,'not null','null') from dual;

select case 1 when null then 'null' else 'not null' end from dual;

Oracle使用nvl2函数，MySQL使用case…when语句。

✦ 记录聚合

select listagg(val,',') within group(order by val) vals from t3 group by id;

select id, group_concat( val order by val separator ',' ) vals from t3 group by id;

记录聚合是指将多条记录汇聚为一条，Oracle使用listadd函数，MySQL使用group_concat函数。

✦ 根据编码取字符

select chr('65') from dual;

select char(65 using ascii) chr from dual;

Oracle使用chr函数，MySQL使用char函数。

✦ 高精度时间

select systimestamp from dual;

select sysdate(6) from dual;

✦ 日期抽取

select extract (year from sysdate) year from dual;

select year(sysdate()) from dual;

✦ 时间戳转换

select to_timestamp('2021-11-01 00:00:00','yyyy-mm-dd hh24:mi:ss') year from dual;

select str_to_date('2021-11-01 00:00:00','%Y-%m-%d %H:%i:%s') from dual;

✦ 正则表达式替换

select regexp_replace('a1b2c3','[0-9]+','*') from dual;

在MySQL中尚无完全匹配功能

✦ 环境信息

select sys_context('USERENV','SESSION_USER') from dual;

select substring_index (user(),'@',1);

Oracle可使用sys_context获得用户环境信息，MySQL仅支持部分信息的获取，例如上面从User()函数中获得

✦ 分页

select * from t1 where rownum<2;

select * from t1 limit 1;

✦ 强制索引

select /*+ index(index_name) */ * from t1;

select * from t1 force index(index_name);

✦ 行号

select rownum,t1.* from t1;

SET @rownum=0;

SELECT t1.*, @rownum:=@rownum+1 AS rownum FROM t1;

✦ 函数空格

select count (*) from t1;

set session sql_mode = 'IGNORE_SPACE';

select count (*) from t1;

在MySQL中通过设置sql_mode参数来忽略函数括号前的空格，该参数需要指定完整的sql_mode参数，不只是添加ignore_space。

✦ 全连接

select * from t1 full outer join t2 on t1.id=t2.id;

select * from t1 left outer join t2 on t1.id=t2.id

union

select * from t1 right outer join t2 on t1.id=t2.id;

在Oracle使用全连接，在MySQL中则使用左右连接的结果集取并集。

✦ 运算符空格

select * from t1 where id > =2;

select * from t1 where id >=2;

在运算符中如果有空格，Oracle是可以识别的，MySQL则需要去掉空格。运算符包括>=、<=、<>、==、!=等。

✦ NULL排序

select * from t1 order by val;

select * from t1 order by -val desc;

oracle中支持nulls first/nulls last在处理含有空值的排序排列中，将空值字段记录放到最前或最后显示。mysql 默认的实现方式是null first，如需实现null last则在需排序字段前加符号-。

✦ 子查询带别名

select * from (select * from t1) ;

select * from (select * from t1) a;

MySQL要求子查询必须带有别名，Oracle则没有这个约束。