通过Oracle DB了解MySQL
通过Oracle DB了解MySQL
Oracle数据库是业界的翘楚，各行各业拥有大量的Oracle DB的DBA，本文尝试通过将Oracle与MySQL数据库的架构、安全、模式对象、数据类型及数据存储进行对比，以方便熟悉Oracle数据库的人可以快速了解MySQL。
数据库架构
MySQL与Oracle架构上不同，Oracle数据库是一个多进程的架构，MySQL是一个多线程的架构。架构的不同导致两种数据库对服务器资源的开销存在在很大的差异。
数据库安全
与Oracle一样，MySQL用户也由数据库管理。MySQL使用一组授权表来跟踪用户及其所拥有的权限。MySQL使用这些授权表对用户执行身份验证，授权和访问控制。
- 数据库身份验证
与Oracle和大多数其他仅使用用户名和密码对用户进行身份验证的数据库不同，MySQL使用location参数来对用户进行身份验证。location参数通常是主机名，IP地址或通配符（“％”）。MySQL可以使用该参数将用户对数据库的访问限制为域中的特定主机。它还允许用户根据连接的不同主机，使用不同的密码和权限。
- 权限
MySQL的权限系统是通过继承起作用的分层系统。在较高级别授予的权限将隐式传递到所有较低级别，但是在较低级别设置的相同权限可能优先。MySQL允许用户按照以下权限范围的降序授予五个不同级别的权限：
- 全局
- 每个主机
- 数据库级
- 特定表
- 特定列（一个表中的一列）
数据库中的每个级别都有一个对应的授权表。当执行权限检查时，MySQL按照权限范围的降序检查每个表，在较低级别授予的权限优先于在较高级别授予的相同权限。
MySQL支持的权限分为两种：管理权限和每个对象权限。管理权限是影响整个服务器并与MySQL功能相关的全局权限。这些管理权限包括FILE，PROCESS，REPLICATION，SHUTDOWN和SUPER权限。每个对象的权限会影响数据库对象，例如表，列，索引和存储过程，并且可以在不同程度上进行授予。
模式
模式包含表，视图，索引，用户，约束，存储过程，触发器和其他特定于数据库的对象的定义。大多数关系数据库都使用相似的对象。
- 模式对象的相似性
Oracle和MySQL模式对象有许多相似之处。下表显示了Oracle和MySQL之间的区别。
Oracle | MySQL |
|---|---|
AFTER触发器 | 触发器 |
BEFORE触发器 | 触发器 |
检查约束 | 检查约束 |
列默认值 | 列默认值 |
数据库 | 数据库 |
外键 | 外键 |
索引 | 索引 |
包 | 不适用 |
PL / SQL函数 | 存储程序 |
PL / SQL程序 | 存储程序 |
主键 | 主键 |
角色 | 角色 |
模式 | 模式 |
顺序 | 列的AUTO_INCREMENT |
快照 | 不适用 |
同义字 | 不适用 |
表 | 表 |
表空间 | 表空间 |
临时表 | 临时表 |
触发器每一行 | 触发器每一行 |
唯一键 | 唯一键 |
用户名 | 用户名 |
视图 | 视图 |
- 模式对象名称
Oracle忽略对象名称的大小写。Oracle模式对象名称以大写形式存储。
与Oracle一样，MySQL列名称，索引名称，存储过程名称，触发器名称和列别名在所有平台上都不区分大小写。但是，MySQL的数据库名称和表名称的情况与Oracle的情况不同。MySQL的数据库对应于数据目录中的目录，而表对应于数据库目录中的一个或多个文件。因此，数据库和表名的区分大小写取决于操作系统的大小写。这意味着数据库和表名在Windows上不区分大小写，但在大多数UNIX变体上都区分大小写。但是，MySQL使用系统变量lower_case_table_names来确定数据库和表名如何存储在磁盘。
Oracle和MySQL允许将保留字加上引号用作对象名。但是，在MySQL中，一些保留字（例如DATE和TIMESTAMP）可以不使用引号。在Oracle中不允许这样做。
MySQL和Oracle之间的标识符定义之间有一些细微的差异。在MySQL中，未加引号的标识符可以以数字开头，并且带引号的标识符可以使用双引号。但是，Oracle标识符均不允许。MySQL的引号字符是反引号（`）。如果设置了SQL模式ANSI_QUOTES，则还可以使用双引号将标识符引起来。Oracle的标识符用双引号引起来。
另外需要注意的是，MySQL里面没有模式所有者的概念，即使是删除了用户，该用户创建的对象也不会被删除。
- 表设计注意事项
- 字符数据类型
MySQL和Oracle在支持的字符类型以及存储和检索字符值的方式之间存在一些差异。
对于小于65,535字节的字符类型，MySQL支持CHAR和VARCHAR。CHAR类型的最大长度为255个字节。VARCHAR类型的最大长度为65,535字节。Oracle支持四种字符类型（CHAR，NCHAR，NVARCHAR2和VARCHAR2）。可以为所有Oracle字符类型声明的最小值是1个字节。CHAR和NCHAR的最大大小为2,000字节，NVARCHAR2和VARCHAR2的最大大小为4,000字节。
存储时，MySQLCHAR值将在右边用空格填充到指定的长度，而在检索时将尾随空格删除（SQL模式PAD_CHAR_TO_FULL_LENGTH除外）。另一方面，VARCHAR值是使用指定的确切字符数存储的。如果值小于列长度，则Oracle会将CHAR和NCHAR值用空格填充，直到该值小于列长度，并且在检索时不修剪尾随空白。对于NVARCHAR2和VARVHAR2数据类型的列，Oracle将存储并检索指定的值，包括尾随空格。
如果为字符类型列分配的值超过指定的长度，MySQL将截断该值，并产生警告，如果设置了STRICT SQL模式，会生成错误。如果分配给字符类型列的值超过指定的长度，Oracle会报错。
MySQL所有字符类型（CHAR，VARCHAR和TEXT）的列都有列的字符集和排序规则。如果未在列定义中明确定义字符集或排序规则，它将使用表的字符集或排序规则。如果未指定表的字符集或排序规则，则选择数据库字符或排序规则。Oracle的CHAR和VARCHAR2字符集由数据库字符集定义，而NCHAR和NVARCHAR字符集由各个国家字符集定义。
在MySQL中声明CHAR或VARCHAR类型时，默认长度是字符而不是字节。Oracle中的默认长度，CHAR和VARCHAR2类型是字节，NCHAR和NVARCHAR2类型是字符。
- 列的默认值
MySQL和Oracle处理列的默认值为NOT NULL方式不同。
MySQL将数据插入表中时确定列默认值。该默认值是列数据类型的隐式默认值。但是，如果启用了STRICT模式，MySQL会生成错误，回滚INSERT语句。
Oracle在将数据插入表中时，必须为所有NOT NULL的列指定数据。Oracle不会为具有NOT NULL约束的列生成默认值。
- 临时表
在MySQL中，临时表是一个数据库对象，仅对当前用户会话可见，并且在用户会话结束时会自动删除。
Oracle对临时表的定义与MySQL稍有不同，因为一旦创建临时表，该表将一直存在直到被显式删除，并且对具有适当权限的所有会话都是可见的。但是，临时表中的数据仅对将数据插入表中的用户会话可见，并且在事务或用户会话的整个生命周期内都可以保留数据。
数据类型
- 默认数据类型映射
MySQL数据类型 | Oracle数据类型 |
|---|---|
BIGINT | NUMBER(19, 0) |
BIT | RAW |
BLOB | BLOB、RAW |
CHAR | CHAR |
DATE | DATE |
DATETIME | DATE |
DECIMAL | FLOAT(24) |
DOUBLE | FLOAT(24) |
DOUBLE PRECISION | FLOAT(24) |
ENUM | VARCHAR2 |
FLOAT | FLOAT |
INT | NUMBER(10, 0) |
INTEGER | NUMBER(10, 0) |
LONGBLOB | BLOB、RAW |
LONGTEXT | CLOB、RAW |
MEDIUMBLOB | BLOB、RAW |
MEDIUMINT | NUMBER(7, 0) |
MEDIUMTEXT | CLOB、RAW |
NUMERIC | NUMBER |
REAL | FLOAT(24) |
SET | VARCHAR2 |
SMALLINT | NUMBER(5, 0) |
TEXT | VARCHAR2、CLOB |
TIME | DATE |
TIMESTAMP | DATE |
TINYBLOB | RAW |
TINYINT | NUMBER(3, 0) |
TINYTEXT | VARCHAR2 |
VARCHAR | VARCHAR2、CLOB |
YEAR | NUMBER |
- 数据类型比较
- 数值类型
下表比较了MySQL和Oracle数字类型。
MySQL | 大小 | Oracle |
|---|---|---|
BIGINT | 8字节 | NUMBER(19,0) |
BIT | 约(M+7)/8字节 | RAW |
DECIMAL(M,D) | D > 0时M+2字节，D = 0时M+1字节(M < D时D+2) | FLOAT(24)、BINARY_FLOAT |
DOUBLE | 8字节 | FLOAT(24)、BINARY_FLOAT、BINARY_DOUBLE |
DOUBLE PRECION | 8字节 | FLOAT(24)、BINARY_DOUBLE |
FLOAT(25<=X <=53) | 8字节 | FLOAT(24)、BINARY_FLOAT |
FLOAT(X<=24) | 4字节 | FLOAT、BINARY_FLOAT |
INT | 4字节 | NUMBER(10,0) |
INTEGER | 4字节 | NUMBER(10,0) |
MEDIUMINT | 3字节 | NUMBER(7,0) |
NUMERIC | D > 0时M+2字节，D = 0时M+1字节(M < D时D+2) | NUMBER |
REAL | 8字节 | FLOAT(24)、BINARY_FLOAT |
SMALLINT | 2字节 | NUMBER(5,0) |
TINYINT | 1字节 | NUMBER(3,0) |
- 日期和时间类型
下表比较了MySQL和Oracle日期时间类型。
MySQL | 大小 | Oracle |
|---|---|---|
DATE | 3字节 | DATE |
DATETIME | 8字节 | DATE |
TIMESTAMP | 4字节 | DATE |
TIME | 3字节 | DATE |
YEAR | 1字节 | NUMBER |
- 字符串类型
下表比较了MySQL和Oracle字符串类型。
注意事项：M表示最大显示尺寸。最大有效显示尺寸为255。L是浮点类型，表示小数点后的位数。 |
|---|
MySQL | 大小 | Oracle |
|---|---|---|
BLOB | L + 2字节(L<2^16) | RAW、BLOB |
CHAR(m) | M字节(1<=M<=255) | CHAR |
ENUM (VALUE1, VALUE2, ...) | 对应ENUM值的数量(上限65535)，1或者2字节 | |
LONGBLOB | L + 4字节(L < 2 ^ 32) | RAW、BLOB |
LONGTEXT | L + 4字节(L < 2 ^ 32) | RAW、CLOB |
MEDIUMBLOB | L + 3字节(L < 2^ 24) | RAW、BLOB |
MEDIUMTEXT | L + 3字节(L < 2^ 24) | RAW、CLOB |
SET (VALUE1, VALUE2, ...) | 对应成员的数量(上限64)，1，2，3，4或者8字节 | |
TEXT | L + 2字节(L<2^16) | VARCHAR2、CLOB |
TINYBLOB | L + 4字节(L <2 ^8) | RAW、BLOB |
TINYTEXT | L + 4字节(L <2 ^8) | VARCHAR2 |
VARCHAR(m) | MySQL 5.0.3之前L+1字节(L<=M或0<=M<=255)(MySQL 5.0.3之后0 <= M <= 65535最大有效字节65,532) | VARCHAR2、CLOB |
数据存储概念
数据存储是MySQL与几乎所有数据库（包括Oracle）不同的方面。在MySQL中，数据库对应于服务器数据目录中的目录。数据库中的表对应于数据库目录中的一个或多个文件，具体取决于表所使用的存储引擎。
数据库可以包含来自不同存储引擎的表。存储引擎负责存储和检索表数据。
MySQL提供了多种存储引擎（以前称为表类型），可以满足各种环境的不同要求。
MySQL支持的存储引擎
储存引擎 | 内容描述 |
|---|---|
MyISAM | 默认的非事务性存储引擎，提供全文索引并且可移植。 |
MERGE | 一种非事务性存储引擎，可以使用一组MyISAM表，这些表的列和索引信息相同。 |
MEMORY(HEAP) | 一种非事务性存储引擎，用于将数据存储在内存中。 |
BDB(Berkeley DB) | 第一个事务安全的存储引擎。 |
InnoDB | 一个事务安全的存储引擎，旨在在处理大量数据时最大化性能。提供行级锁定。MySQL5.5之后的默认存储引擎。 |
FEDERATED | 用于访问远程数据库而不是本地表中的数据。 |
ARCHIVE | 可以在很小的空间内存储大量未索引的数据。 |
CSV | 使用逗号分隔的值格式将数据存储在文本文件中。 |
BLACKHOLE | 一种类似于黑洞的存储引擎，它接受数据但不存储它。 |
MySQL存储引擎的职能包括保管数据、索引、使用内存 （数据缓存、缓冲）事务处理（ACID、XA、MVCC、隔离级别）和并发性（锁、排他控制）。
Oracle数据库由一个或多个表空间组成。表空间提供逻辑存储，并将数据库与保存数据的物理磁盘链接。表空间是从一个或多个数据文件创建的。数据文件是文件系统中的文件或原始设备指定的磁盘空间区域。可以通过添加数据文件来扩大表空间。
Oracle数据库由一个或多个存储Oracle表的SYSTEM表空间组成。也可以使用用户定义的表空间进行配置。表空间是数据库对象的逻辑存储位置。例如，用户可以指定在表空间中的哪个位置创建特定的表或索引。
希望以上内容，能够帮助熟悉Oracle数据库的人快速了解MySQL。