MySQL 与 Oracle 的区别

MySQL 和 Oracle 宏观上的区别：

1、MySQL 与 Oracle都是关系型数据库，应用于各种平台。MySQL 最开始是一个瑞典公司开发的，但后来被 sun 公司收购，后来 sun 又被 Oracle 收购，所以现在可以说 MySQL属于甲骨文公司了，MySQL 开源免费的，而 Oracle 则是收费的，并且价格非常高。

• MySQL 默认端口：3306，默认用户：root
• Oracle 默认端口：1521，默认用户：system

MySQL 的安装卸载很简单，Oracle 很麻烦，安装所用的空间差别也是很大的，MySQL 安装后差不多一两百兆，而 Oracle 则有3G左右，且使用的时候 Oracle 占用特别大的内存空间和其他机器性能。

• MySQL 登录：mysql -hlocalhost -uroot -p密码（h：host、u：user、p：password）
• Oracle 登录：sqlplus user_name/password@IP:port/instance_name;（其中可以把IP地址，端口号，实例名写在一个TNS文件中取一个别名，登陆的时候输入这个别名就行了）

初学阶段，图形化工具，MySQL 可以使用 Navicat，Oracle 一般用 PLSQL，也可以用 sqlyog 等；

• MySQL 的管理工具较少，在Linux下的管理工具的安装有时需要安装额外的包（phpmyadmin，etc），有一定复杂性。
• Oracle 有多重成熟命令行、图形界面、web 管理工具，还有很多第三方的管理工具，管理极其方便高效。

Oracle 支持大并发，大访问量，是 OLTP 最好的工具。

2、数据库的层次结构：

• MySQL： 默认用户是 root，用户下可以创建好多数据库，每个数据库下还有好多表，一般情况下都是使用默认用户，不会创建多个用户。
• Oracle：创建一个数据库，数据库下有好多用户：sys、system、scott 等，不同用户下有好多表，一般情况下只创建一个数据库用。

类型区别

1、数据库中表字段类型：

• MySQL：int、float、double 等数值型，varchar、char 字符型，date、datetime、time、year、timestamp 等日期型。
• Oracle：number（数值型），varchar2、varchar、char（字符型），date（日期型）等。

其中

• char(2) 在 Oracle 中 2 代表两个字节，MySQL 中代表两个字符。

• varchar 在 MySQL 中，必须给长度例如 varchar(10) 不然插入的时候出错。

• MySQL 没有 Number 类型，但有 int/decimal 类型，MySQL 中的数字型类型比较多，分的也比较细，还有 tinyint、smallint、mediumint、bigint 等类型

• Oracle 中的 Number(5,1) 对应 MySQL 中的 decimal(5,1)，Number(5) 对应 int(5)。

• MySQL 中的日期时间类型有Date、Time、Datetime等类型，MySQL中Date类型仅表示日期(年-月-日)，Time类型仅表示时间（时:分:秒），而Datetime类型表示日期时间(年-月-日 时:分:秒)，Oracle 中的 Date 类型和 MySQL 中的 Datetime 类型一致。

函数区别

1、length(str)函数

Oracle 中的 length(str) 是获取字符串长度的函数，MySQL 中对应的函数为 char_length(str)。

2、sys_guid()函数

Oracle 通过 sys_guid() 函数是生成随机序列，MySQL 通过 UUID() 生成随机序列。

3、时间格式化函数

• 将时间转换为字符串型时间: MySQL date_format(NOW(),'%Y-%m-%d') 对应 Oracle 的 to_char(sysdate, 'YYYY-MM-DD');
• 将字符串型时间转换为时间类型: MySQL str_to_date('2019-01-01','%Y-%m-%d') 对应 Oracle 中的 to_date('2019-01-01', 'YYYY-MM-DD');
• 包括时分秒的函数转换：DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s')，str_to_date('2019-01-01','%Y-%m-%d %H:%i:%s')。

4、条件函数（nvl()、nvl2()、decode()）

nvl(tab.columnName, 0)：如果tab.columnName值为空，则返回值取0，否则取tab.columnName；对应的MySQL函数为：ifnull(tab.columnName, 0)。
nvl2(expr1,expr2,expr3)：如果expr1不为null，则返回expr2，否则返回expr3；对应的MySQL函数为：if(expr1,expr2,expr3)。
DECODE(value, val1, val2, val3)：如果value等于val1，则返回val2，否则返回val3；MySQL可用IF函数表示：if(value=val1, val2, val3)；
DECODE(value, if1, val1, if2,val2,...,ifn, valn, val)：如果value等于if1，则返回val1，如果value等于if2，则返回value2…如果value等于ifn，则返回valn，否则返回val；MySQL对于这种判断可以通过case when then else end;l来判断，即：case when value=if1 then val1 when value=if2 then val2,,,when value=ifn then valn else val end;

5、trunc() 函数

TRUNC(12.123)：返回整数(12)；MySQL对应的函数：truncate(12.123, 0)；
TRUNC(12.123, 2)：返回值保留2为小数(12.12)；MySQL对应的函数：truncate(12.123, 2)；
TRUNC(SYSDATE)：返回值为(2019-07-26 00:00:00)；MySQL对应的为cast(now() as datetime)：返回值为(2019-07-26 14:11:38)；
MySQL的cast函数语法为：CAST(xxx AS 类型) （可用类型为：二进制,同带binary前缀的效果:BINARY；字符型,可带参数:CHAR()；日期:DATE；时间:TIME；日期时间型: DATETIME；浮点数: DECIMAL；整数:SIGNED；无符号整数:UNSIGNED）

6、to_char()、to_number()

to_char(123)：将数字 123 转换为字符串 123；MySQL 对应的函数为 CAST(123 AS CHAR(3))；
to_number('123')：将字符串数字 123 转换为数字类型；MySQL对应的函数为 CAST('123' as SIGNED)；

7、sysdate 当前时间

sysdate：返回当前日期+时间； MySQL 对应的函数为 now()；

操作区别

1、字符串连接符 ||**

• Oracle 可用 || 来连接字符串，
• MySQL 不支持 || 连接，MySQL 可通过 concat() 函数链接字符串。

2、主键：

• MySQL 一般使用自动增长类型，在创建表时只要指定表的主键 auto increment，插入记录时，不需要再指定该记录的主键值，MySQL 将自动增长。
• Oracle 没有自动增长类型，主键一般使用的序列，插入记录时将序列号的下一个值赋给该字段即可，只是 ORM 框架是只要是 native 主键生成策略即可。

3、单引号处理：

• MySQL 可以用双引号包起字符串
• Oracle 只可以用单引号包起字符串。

4、分页处理：

• MySQL 是直接在SQL语句中使用limit就可以实现分页
• Oracle 则是需要用到伪列 ROWNUM 和嵌套查询

-- rownum语法如下：
SELECT * FROM XJ_STUDENT WHERE ROWNUM = 1; -- 查询第一条数据
SELECT * FROM XJ_STUDENT WHERE ROWNUM <= 10; -- 获取前10条数据
-- 但rownum不支持查询后几条或第n(n>1)条数据，例如以下sql是不支持的
SELECT * FROM XJ_STUDENT WHERE ROWNUM > 2;
SELECT * FROM XJ_STUDENT WHERE ROWNUM = 3;

-- limit 语法如下：
SELECT * from fw_department limit 3; -- 查询前3条数据
SELECT * from fw_department limit 2, 4; -- 从第2(序号从0开始)条开始，查4条记录

5、对事务提交：

• MySQL 默认是自动提交，可以修改为手动提交
• Oracle 默认不自动提交，需要手动提交，需要在写commit指令或点击commit按钮。

6、对事务的支持：

• MySQL 在 InnoDB 存储引擎的夯机所的情况下才支持事务
• Oracle 则完全支持事务。

7、事务隔离级别：

• MySQL 是 repeatable read 的隔离级别，而 Oracle 是 read commited 的隔离级别；
• 同时二者都支持 serializable 串行化事务隔离级别，可以实现最高级别的读一致性。每个 session 提交后其它 session 才能看到提交的更改；
• Oracle 通过在 undo 表空间中构造多版本数据块来实现读一致性，每个 session 查询时，如果对应的数据块发生变化，Oracle 会在 undo 空间中为这个 session 构造它查询时的旧的数据块；
• MySQL 没有类似 Oracle 的构造多版本数据的机制，只支持 read commited 的隔离级别，一个 session 读取数据时，其他 session 不能更改数据，但可以在表最后插入数据；session 更新数据时，要加上排它锁，其他 session 无法访问数据。

8、并发性：

• MySQL 以表级锁为主，对资源锁定的粒度很大，如果一个 session 对一个表加锁时间过长，会让其他 session 无法更新此表中的数据。虽然 InnoDB 引擎表可以用行级锁，但这个行级锁的机制依赖于表的索引，如果表没有索引，或者sql语句没有使用索引，那么仍然使用表级锁；
• Oracle 使用行级锁，对资源锁定的粒度要小很多，只是锁定 sql 需要的资源，并且加锁是在数据库中的数据行上，不依赖于索引，所以 Oracle 对并发性的支持要好很多。

9、逻辑备份：

• MySQL 逻辑备份时要锁定数据，才能保证备份的数据是一致的，影响业务正常的dml使用
• Oracle 逻辑备份时不锁定数据，且备份的数据是一致的。

10、复制：

• MySQL 复制服务器配置很简单，但主库出问题时，从库可能丢失一定的数据，且需要手工切换从库到主库；
• Oracle 既有堆或拉式的传统数据复制，也有 dataguard 的双机或多机容灾机制，主库出问题时，可以自动切换备库到主库，但配置管理较复杂。

11、性能诊断：

• MySQL 的诊断调优方法较少，主要有慢查询日志；
• Oracle 有各种成熟的性能诊断调优工具，能实现很多自动分析、诊断功能。比如 awr、addm、sqltrace、tkproof 等。

12、保存数据的持久性：

• MySQL 默认提交 sql 语句，但如果更新过程中出现 db 或主机重启的问题，也许会丢失数据；
• Oracle 把提交的 sql 操作先写入了在线联机日志文件中，保持到了硬盘上，可以随时恢复。

13、热备份：

• Oracle 有成熟的热备份工具 rman，不影响用户使用数据库。即使备份的数据库不一致，也可以在恢复时通过归档日志和联机重做日志进行一致的回复。
• MySQL：
  • MyISAM 引擎：MySQL 自带的 MySQL hostcopy 热备时，需要给表加读锁，影响 dml 操作；
  • InnoDB 引擎：它会备份 InnoDB 的表和索引，但是不会备份.frm文件，用 ibbackup 备份时，会有一个日志文件记录备份期间的数据变化，因此可以不用锁表，不影响其它用户使用数据库，但此工具是收费的。
  • innobackup 是结合 ibbackup 使用的一个脚本，它会协助对 .frm 文件的备份。

14、表(左/右)关联(+)

• Oracle 左连接,右连接可以使用(+)来实现
• MySQL 只能使用 left join right join等关键字。

-- Oracle 左关联
select * from taba, tabb where taba.id = tabb.id(+);
-- Oracle 右关联
select * from taba, tabb where taba.id(+) = tabb.id;
-- MySQL 左关联
select * from taba left join tabb on taba.id=tabb.id;
-- MySQL 右关联
select * from taba right join tabb on taba.id=tabb.id;