MySQL与Oracle对比及区别说明

目录

• 一、比较
• 1、mysql的特点
• 2、oracle的特点
• 3、MySQL的缺点
• 4、Oracle的缺点
• 5、技术选型
• Oracle适用场景
• MySQL适用场景
• 二、区别对比
• 1、本质的区别
• 2、基本操作区别
• （1）、用户名、端口号
• （2）、登录方式
• （3）、数据库安全性
• 3、语法区别
• （1）、大小写是否敏感
• （2）、常用字段类型
• （3）、时间日期
• （4）、创建表空间 / 数据库
• （5）、创建临时表
• （6）、创建表和插入记录
• （7）、事务隔离级别
• （8）、事务提交方式
• （9）、分页
• （10）、查询语句
• （11）、id 递增的处理
• （12）、null 值处理
• （13）、小数格式化
• （14）、多条件判断
• （15）、单引号的处理
• （16）、模糊查询
• （17）、时间相减
• 总结

一、比较

1、MySQL的特点

• 性能卓越，服务稳定，很少出现异常宕机；
• 开放源代码无版本制约，自主性及使用成本低；
• 历史悠久，社区和用户非常活跃，遇到问题及时寻求帮助；
• 软件体积小，安装使用简单且易于维护，维护成本低；品牌口碑效应；
• 支持多种OS，提供多种API接口，支持多种开发语言，对流行的php，Java很好的支持

2、Oracle的特点

• 兼容性：Oracle产品采用标准SQL，并经过美国u构架标准技术所（NIST）测试，与IBM SQL/DS、DB2、INGRES、IDMS/R等兼容。
• 可移植性：Oracle的产品可运行于很宽范围的硬件与操作系统平台上。可以安装在多种 大、中、小型机上，可在多种操作系统下工作。
• 可联结性：Oracle能与多种通讯网络相连，支持各种协议。
• 高生产率：Oracle产品提供了多种开发工具，能极大地方使用户进行进一步的开发。
• 开放性：Oracle良好的兼容性、可移植性、可连接性和高生产率使Oracle RDBMS具有良好的开放性。

3、MySQL的缺点

• MySQL最大的缺点是其安全系统，主要是复杂而非标准，另外只有到调用mysqladmin来重读用户权限才会发生改变；
• MySQL的另一个主要的缺点是缺乏标准的RI（Referential Integrity-RI）机制，RI限制的缺乏（在给定字段域上的一种固定的范围限制）可以通过大量的数据类型来补偿；
• MySQL不支持热备份；

4、Oracle的缺点

• 对硬件要求很高；
• 价格比较昂贵；
• 管理维护麻烦一些；
• 操作比较复杂，需要技术含量高；

5、技术选型

Oracle适用场景

• 对数据库有高级需求：如果企业对数据库的高级需求较高，如存储复杂数据及其方法，要求高可用性、灾备恢复、安全性等，可以考虑用Oracle。
• 大型企业应用：Oracle在处理大规模、复杂的企业级应用方面表现出色。它能够处理海量的数据和高并发的访问请求，同时支持复杂的数据模型和关系。
• 项目并发量高：使用Oracle，它是是OLTP（联机事务处理）最好的工具。
• 安全性要求高：Oracle使用了许多安全功能，如用户名，密码，配置文件，本地身份验证，外部身份验证，高级安全增强功能等。像金融、银行等对安全性要求高的项目一般都选用Oracle作为数据库。
• 高可用性和容灾需求：Oracle提供了强大的高可用性和容灾解决方案，例如集群配置、数据复制和自动故障转移等，能够确保系统的连续性和数据的可靠性。MySQL付费版也支持，但可靠性不如Oracle。

MjsySQL适用场景

• 中小型企业和个人使用：由于其开源和低成本特性，MySQL广泛应用于中小型企业的业务系统。
• Web应用：因其轻量级和高性能，MySQL是大多数Web应用的理想选择，如内容管理系统（cms）、电子商务平台等。
• 移动应用和桌面应用：适用于各类移动应用和桌面应用的数据库支持。

二、区别对比

1、本质的区别

Oracle数据库是一个对象关系数据库管理系统（ORDBMS）。它通常被称为Oracle RDBMS或简称为Oracle，是一个收费的数据库。

MySQL是一个开源的关系数据库管理系统（RDBMS）。它是世界上使用最多的RDBMS，作为服务器运行，提供对多个数据库的多用户访问。它是一个开源、免费的数据库。

RDBMS和ORDBMS的区别

标准

• RDBMS
• ORDBMS

缩写含义

• 关系数据库管理系统
• 面型对象数据库管理系统

数据存储方式

• 数据存储在实体里面，以包含特定信息的表格的形式存在
• 数据存储在对象中

数据复杂性

• 处理比较简单的数据
• 比 RDBMS 处理更大且更复杂的数据

分组

• 拥有公共定义的实体集合的不同实体类型
• 用类描述拥有公共的关系、行为和相似的属性的一组对象

数据处理

• RDBphpMS 只存储数据
• 存储数据以及方法

主要目标

• 数据独立于应用程序
• 数据封装

主键

• 主键可以明显的标识表中的对象
• 对象标识符 (object identifier, OID) 对于任何一个对象和实体都是明确且持久的

2、基本操作区别

（1）、用户名、端口号

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

（2）、登录方式

• 连接MySQL：

mysql -u root -p
-- 输入密码
-- 查询所有数据库
show databases;
-- 切换到 "test" 这个数据库
use test;
-- 查询该数据库所有表
show tables;
--修改户密码
set password for 用户名@localhost = password('新密码')

• 连接Oracle：

sqlplus
-- 输入用户名
-- 输入密码
-- 查询该用户的表
select TABLE_NAME from user_tables;

--修改密码
ALTER USER 用户名 IDENTIFIED BY 新密码;

（3）、数据库安全性

• MySQL使用三个参数来验证用户，即用户名，密码和位置；
• Oracle使用了许多安全功能，如用户名，密码，配置文件，本地身份验证，外部身份验证，高级安全增强功能等。

3、语法区别

（1）、大小写是否敏感

• MySQL：在 Windows 操作系统下，MySQL 默认表名、列名等是不区分大小写的；在 linux 等类 Unix 系统下，默认情况下数据库名、表名是区分大小写的，列名和索引名是不区分大小写的。不过这些行为可以通过配置文件（my.cnf）中的参数来修改。
• Oracle：表名、列名等通常是不区分大小写的。但是 Oracle 会按照创建对象时定义的大小写形式来存储对象名称，并且在引用对象时如果使用了错误的大小写形式，Oracle 会自动将其转换为正确的形式。

（2）、常用字段类型

• MySQL：有多种常用字段类型。例如，整数类型包括 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT；浮点数类型有 FLOAT 和 DOUBLE；字符串类型有 CHAR、VARCHAR、TEXT 等；日期时间类型有 DATE、TIME、DATETIME、TIMESTAMP。其中，TIMESTAMP 类型会自动更新时间戳，在数据更新操作时非常有用。
• Oracle：整数类型如 NUMBER（p），p 表示精度，可以用来表示不同范围的整数；浮点数也可以用 NUMBER 类型，通过指定精度和小数位数来实现；字符串类型主要有 CHAR、VARCHAR2、CLOB 等；日期时间类型包括 DATE（存储日期和时间）、TIMESTAMP（比 DATE 更精确的时间戳）。Oracle 的 DATE 类型存储了世纪、年、月、日、时、分、秒信息。

（3）、时间日期

• MySQL：可以方便地使用函数来操作日期时间。例如，NOW () 函数获取当前日期和时间，CURDATE () 获取当前日期，CURTIME () 获取当前时间。在日期时间的计算方面，可以通过 DATE_ADD () 和 DATE_SUB () 函数来进行日期时间的加减操作。
• Oracle：通过 SYSDATE 函数获取当前日期和时间，TRUNC (SYSDATE) 获取当前日期部分。日期时间的计算可以使用 INTERVAL 关键字，例如 SYSDATE + INTERVAL ‘1’ DAY 表示在当前日期基础上加一天。

（4）、创建表空间 / 数据库

MySQL：创建数据库相对简单，使用 CREATE DATABASE 语句。

CREATE DATABASE my_database;

对于表空间管理，在 MySQL 中可以通过 InnoDB 存储引擎的文件系统来管理表空间，通过设置参数 innodb_data_file_path 等来配置表空间文件。

Oracle：创建表空间使用 CREATE TABLESPACE 语句，并且可以指定数据文件的位置、大小等多种属性。

CREATE TABLESPACE my_tablespace DATAFILE 'my_tablespace.dbf' SIZE 100M;

创建数据库则需要先创建表空间，然后通过数据库配置助手（DBCA）或一系列复杂js的 SQL 语句来创建数据库，包括定义控制文件、日志文件等。

（5）、创建临时表

MySQL：可以使用 CREATE TEMPphpORARY TABLE 语句创建临时表。

CREATE TEMPORARY TABLE temp_table (id INT, name VARCHAR (20));

临时表只在当前会话中可见，当会话结束时，临时表会自动被删除。

Oracle：同样可以创建临时表，分为会话级临时表和事务级临时表。会话级临时表在整个会话期间存在，使用 CREATE GLOBAL TEMPORARY TABLE 语句创建；

CREATE GLOBAL TEMPORARY TABLE temp_table (id NUMBER, name VARCHAR2 (20)) ON COMMIT PRESERVE ROWS;（ON COMMIT PRESERVE ROWS 表示会话级）

事务级临时表在事务结束时自动删除，创建时使用 ON COMMIT DELETE ROWS。

（6）、创建表和插入记录

MySQL：创建表的基本语法是 CREATE TABLE table_name (column1 datatype, column2 datatype, …)

CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR (50), age INT);

插入记录使用 INSERT INTO 语句，如

INSERT INTO users (name, age) VALUES ('John', 30)

Oracle：创建表的语法类似，如

CREATE TABLE employees (employee_id NUMBER PRIMARY KEY, employee_name VARCHAR2 (100), department_id NUMBER);

插入记录时

INSERT INTO employees (employee_name, department_id) VALUES ('Alice', 101);

Oracle 还支持通过子查询来插入数据，这在复杂的数据加载场景中很有用。

（7）、事务隔离级别

MySQL：支持四种事务隔离级别，分别是 ：

• READ UNCOMMITTED（读未提交）、READ COMMITTED（读已提交）、REPEATABLE READ（可重复读）SERIALIZABLE（可串行化）。
• 默认的隔离级别是 REPEATABLE READ，在这种隔离级别下可以保证在同一个事务中多次读取同一数据的结果是一致的。

Oracle：默认的事务隔离级别是 READ COMMITTED。

• 这意味着一个事务只能看到其他事务已经提交的数据。
• 和 MySQL 一样，Oracle 也支持其他隔离级别，通过设置参数或者在事务中使用 SET TRANSACTION ISOLATION LEVEL 语句来改变隔离级别。

（8）、事务提交方式

• MySQL：使用 COMMIT 语句来提交事务，例如 BEGIN; – 开始事务，执行一系列 SQL 操作，如 INSERT、UPDATE 等，COMMIT; – 提交事务。也可以使用 ROLLBACK 语句来回滚事务，即撤销已经执行的操作。
• Oracle：事务提交也是使用 COMMIT 语句，并且事务控制机制类似。可以在 SQL*Plus 或者其他客户端工具中通过 SET AUTOCOMMIT ON/OFF 来设置自动提交模式。当 AUTOCOMMIT 为 ON 时，每条 SQL 语句执行后会自动提交；当为 OFF 时，需要手动使用 COMMIT 或 ROLLBACK 来控制事务。

（9）、分页

MySQL：在 MySQL 中，分页通常使用 LIMIT 子句。例如，SELECT * FROM table_name LIMIT start, count;，其中 start 是起始行索引（从 0 开始），count 是要返回的行数。这种方式简单直接，适用于简单的分页需求。

Oracle：分页相对复杂一些。可以使用 ROWNUM 伪列来实现分页。例如，

SELECT * FROM (SELECT a.*, ROWNUM rnum FROM (SELECT * FROM table_name) a WHERE ROWNUM <= end_row) WHERE rnum >= start_row;

其中 start_row 和 end_row 分别是起始行和结束行的编号。

从Oracle 12C开始，提供了一种行限制（row limit）的写法，这种写法也可以用于分页查询。

select * from emp order by sal fetch first 5 rows only;
            EMPNO ENAME                                                     MGR HIREDATE         SAL        COM     DEPTNO
       ---------- -------------------------------------------------- ---------- --------- ---------- ---------- ----------
               92  Bryana  Worswick                                          80 03-JUL-18       2504          1         10
               99  Rufus  McDermott-Row                                      63 01-MAR-02       2512         61         10
               36  Jacenta  Joust                                            71 31-JAN-05       2515          9         30
                2  Blisse  Adamoli                                           65 12-APR-14       2517         67         20
               98  Simona  Shropshire                                        52 16-AUG-08       2518         24         88

（10）、查询语句

MySQL：基本的查询语法是

 SELECT column1, column2, … FROM table_name WHERE condition;

支持多种连接查询，如内连接（INNER JOIN）、左连接（LEFT JOIN）、右连接（RIGHT JOIN）等。例如

SELECT * FROM table1 INNER JOIN table2 ON table1.id =UrDMF table2.id;

Oracle：查询语法相似，如

SELECT column1, column2, … FROM table_name WHERE condition;

在连接查询方面，Oracle 也支持标准的 SQL 连接语法，同时还有自己独特的语法，如使用 “(+)” 来表示外连接，不过这种语法现在已经不推荐使用，推荐使用标准的 JOIN 语法。

（11）、id 递增的处理

MySQL：可以在创建表时使用 AUTO_INCREMENT 关键字来定义一个自动递增的主键列。例如

CREATE TABLE products (id INT AUTO_INCREMENT PRIMARY KEY, product_name VARCHAR (100));

每次插入新记录时，不需要指定 id 的值，MySQL 会自动为其分配一个唯一递增的值。

Oracle：没有像 MySQL 那样的 AUTO_INCREMENT 关键字。通常使用序列（SEQUENCE）来实现类似的功能。首先创建一个序列，如

CREATE SEQUENCE product_seq START WITH 1 INCREMENT BY 1;

然后在插入记录时，通过调用序列的 NEXTVAL 属性来获取下一个递增的值插入到 id 列中，例如

INSERT INTO products (id, product_name) VALUES (product_seq.NEXTVAL, 'Product A');

（12）、null 值处理

MySQL：在查询中，可以使用 IS NULL 或者 IS NOT NULL 来判断列值是否为 null。例如，

SELECT * FROM table_name WHERE column_name IS NULL;

在函数使用中，一些函数在遇到 null 值时会返回特定的结果，如 SUM 函数在计算包含 null 值的列时会忽略 null 值。

Oracle：同样使用 IS NULL 和 IS NOT NULL 来判断 null 值。在函数处理方面，Oracle 的函数对 null 值的处理方式也有自己的规则，例如 NVL 函数可以将 null 值转换为指定的值，NVL (column_name, 0) 会将 column_name 中的 null 值转换为 0。

（13）、小数格式化

• MySQL：可以使用 ROUND 函数来对小数进行四舍五入，例如 ROUND (number, decimal_places)，其中 number 是要格式化的数字，decimal_places 是要保留的小数位数。另外，也可以通过 CONVERT 函数来转换数据类型，从而实现一定程度的小数格式化。
• Oracle：也有 ROUND 函数，使用方式类似。同时，Oracle 还可以使用 TO_CHAR 函数将数字转换为字符格式来进行更灵活的格式化，例如 TO_CHAR (number, ‘FM9999.99’) 可以将数字格式化为指定的小数格式，其中 ‘FM’ 表示去除前面多余的空格。

（14）、多条件判断

MySQL：在 WHERE 子句中可以使用 AND、OR 逻辑运算符来组合多个条件。例如，

SELECT * FROM table_name WHERE condition1 AND condition2;

也可以使用 CASE 语句在查询结果中进行多条件判断，例如

SELECT column_name, CASE WHEN condition1 THEN value1 WHEN condition2 THEN value2 ELSE value3 END AS new_column FROM table_name;

Oracle：逻辑运算符 AND 和 OR 的使用方式与 MySQL 相同。Oracle 也支持 CASE 语句，并且还支持 DECODE 函数进行简单的多条件判断，例如

 SELECT column_name, DECODE (condition, value1, result1, value2, result2, default_result) AS new_column FROM table_name;

（15）、单引号的处理

MySQL：在 SQL 语句中，字符串常量通常需要用单引号括起来。如果字符串本身包含单引号，需要使用反斜杠（）进行转义，例如

 INSERT INTO table_name (column_name) VALUES ('It's a sample');

Oracle：同样，字符串常量用单引号括起来。如果有单引号，需要使用两个单引号来表示一个单引号，例如

 INSERT INTO table_name (column_name) VALUES ('It''s a sample');

（16）、模糊查询

MySQL：使用 LIKE 关键字进行模糊查询，支持通配符 “%”（表示任意字符序列）和 “_”（表示任意单个字符）。例如，

SELECT * FROM table_name WHERE column_name LIKE '% keyword%';

Oracle：也使用 LIKE 关键字进行模糊查询，通配符的使用方法与 MySQL 相同。此外，Oracle 还提供了一些高级的文本搜索功能，如使用 CONTAINS 函数（需要安装并配置 Oracle Text 组件）来进行更复杂的文本搜索。

（17）、时间相减

oracle对于计算前7天，时间减7就行了，计算前1个小时，时间减1/24就行了，加法同理：

select TO_DATE('2023-03-28 15:51:20', 'yyyy-mm-dd hh24:mi:ss') -7 from dual;
-- 计算前7天，结果：2023-03-21 15:51:20

select TO_DATE('2023-03-28 15:51:20', 'yyyy-mm-dd hh24:mi:ss') -1/24 from dual;
-- 计算前1个小时，结果：2023-03-28 14:51:20

mysql计算前7天，或者前1个小时，需要用不同的关键字：

select date_sub('2023-03-28 15:51:20' ,interval 7 day);
-- 计算前7天，结果：2023-03-21 15:51:20

select date_sub('2023-03-28 15:51:20' ,interval 1 hour);
-- 计算前1个小时，结果：2023-03-28 14:51:20

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持编程客栈(www.devze.com)。