[MySQL-ORACLE] Oracle and MySQL Compared (types and so on)

By | Y2014Y2014-7M-D

cite from: http://docs.oracle.com/cd/E12151_01/doc.150/e12155/oracle_mysql_compared.htm

Here’s the table for comparing types of ORACLE & MySQL below:

2.2.4 Migrating Multiple Databases

SQL Developer supports the migration of multiple MySQL databases if they are on the same MySQL database server.

2.2.5 Schema Migration Considerations for MySQL

Schema migration considerations for MySQL apply in the following areas”

2.2.5.1 Databases

When migrating MySQL databases to Oracle, SQL Developer maps each MySQL database to a tablespace in Oracle. Database objects, such as tables, indexes and views are stored in the respective tablespaces and are referenced from the Oracle schema for the user that owns them.

2.2.5.2 Mapping MySQL Global and Database-Level Privileges to Oracle System Privileges

SQL Developer does not process all the administrative privileges on MySQL, except the SUPER privilege. Table 2-2 shows the mappings for MySQL per-object privileges granted at the different levels as well as the SUPER privilege granted at the global level.

Table 2-2 MySQL Privileges and Oracle System Privileges

Level Privilege System Privilege(s) on Oracle
Global ALTER ALTER ANY TABLE, ALTER ANY SEQUENCE, ALTER ANY CUSTER, COMMENT ANY TABLE
Global ALTER ROUTINE ALTER ANY PROCEDURE, DROP ANY PROCEDURE
Global CREATE CREATE ANY TABLE, CREATE ANY SEQUENCE, CREATE ANY CLUSTER, CREATE DATABASE LINK, COMMENT ANY TABLE
Global CREATE ROUTINE CREATE ANY PROCEDURE
Global CREATE USER CREATE USER, GRANT ANY PRIVILEGE
Global CREATE VIEW CREATE ANY VIEW
Global DELETE ALTER ANY TABLE, DROP USER, DELETE ANY TABLE
Global DROP DROP ANT TABLE, DROP ANY SEQUENCE, DROP ANY CLUSTER, DROP ANY VIEW
Global EXECUTE EXECUTE ANY PROCEDURE
Global INDEX CREATE ANY INDEX, ALTER ANY INDEX, DROP ANY INDEX
Global INSERT INSERT ANY TABLE
Global LOCK TABLES LOCK ANY TABLE
Global SELECT SELECT ANY TABLE
Global SUPER CREATE ANY TRIGGER, DROP ANY TRIGGER
Global UPDATE UPDATE ANY TABLE
Global USAGE CREATE SESSION, ALTER SESSION, UNLIMITED TABLESPACE
Database CREATE CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE TABLE
Database CREATE ROUTINE CREATE PROCEDURE
Database CREATE VIEW CREATE VIEW
Table CREATE CREATE TABLE
Table CREATE VIEW CREATE VIEW

2.2.5.3 Temporary Tables

SQL Developer does not support the migration of temporary tables.

In MySQL, temporary tables are database objects that are visible only to the current user session and are automatically dropped when the user session ends.

The definition of temporary tables in Oracle differs slightly from MySQL, in that the temporary tables, once created, exist until they are explicitly dropped and they are visible to all sessions with appropriate privileges. However, the data in the temporary tables is visible only to the user session that inserts the data into the table, and the data may persist for the duration of a transaction or a user session.

2.2.5.4 Owner of Schema Objects

SQL Developer creates an Oracle schema for the MySQL root user that owns, for all databases to be migrated, all database objects except stored procedures. For stored procedures, the MySQL users that created them remain the owner. SQL Developer creates an Oracle schema for each MySQL user that is migrated.

2.3 Data Types

This section describes the data types used within Oracle. It shows the MySQL data types and the Oracle equivalent. It includes information about the following:

2.3.1 Supported Oracle Data Types

Table 2-3 describes the Oracle data types supported by Oracle SQL Developer.

Table 2-3 Supported Oracle Data Types

Data Type Description
BLOB A binary large object. Maximum size is 4 gigabytes.
CHAR (SIZE) Fixed-length character data of length size bytes. Maximum size is 2000 bytes. Default and minimum size is 1 byte.
CLOB A character large object containing single-byte characters. Both fixed-width and variable-width character sets are supported, both using the CHAR database character set. Maximum size is 4 gigabytes.
DATE The DATE data type stores date and time information. Although date and time information can be represented in both CHAR and NUMBER data types, the DATE data type has special associated properties. For each DATE value, Oracle stores the following information: century, year, month, day, hour, minute, and second.
FLOAT Specifies a floating-point number with decimal precision 38, or binary precision 126.
LONG (SIZE) Character data of variable length up to 2 gigabytes, or 2^31 -1 bytes.
LONG RAW Raw binary data of variable length up to 2 gigabytes.
NCHAR (SIZE) Fixed-length character data of length size characters or bytes, depending on the choice of national character set. Maximum size is determined by the number of bytes required to store each character, with an upper limit of 2000 bytes. Default and minimum size is 1 character or 1 byte, depending on the character set.
NCLOB A character large object containing multibyte characters. Both fixed-width and variable-width character sets are supported, both using the NCHAR database character set. Maximum size is 4 gigabytes. Stores national character set data.
NUMBER Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127.
NVARCHAR2 (SIZE) Variable-length character string having maximum length size characters or bytes, depending on the choice of national character set. Maximum size is determined by the number of bytes required to store each character, with an upper limit of 4000 bytes. You must specify size for NVARCHAR2.
RAW (SIZE) Raw binary data of length size bytes. Maximum size is 2000 bytes. You must specify size for a RAW value.
VARCHAR (SIZE) The VARCHAR data type is currently synonymous with the VARCHAR2 data type. Oracle recommends that you use VARCHAR2 rather than VARCHAR. In the future, VARCHAR might be defined as a separate data type used for variable-length character strings compared with different comparison semantics. The maximum size is 4000 and the minimum of 1 is the default.
BINARY_DOUBLE A 64-bit, double-precision floating-point number data type.
BINARY_FLOAT A 32-bit, single-precision floating-point number data type.

For more information about Oracle data types, see Oracle Database SQL Language Reference.

2.3.2 Default Data Type Mappings

Table 2-4 shows the default settings used by SQL Developer to convert data types from MySQL to Oracle. SQL Developer enables you to change the default setting for certain data types by specifying an alternative type. For information about changing the default data type mappings, see the SQL Developer online help.

Table 2-4 Default Data Type Mappings Used by Oracle SQL Developer

MySQL Data Type Oracle Data Type
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

Note:

The ENUM and SET data types have no direct mapping in Oracle. SQL Developer maps ENUM columns in MySQL to VARCHAR2 columns in Oracle. It then adds a constraint and a trigger to those columns to ensure that only values that were allowed by the ENUM data type are allowed in the column it was mapped to in Oracle.

2.3.3 Comparing Data Types

This section lists the difference between MySQL and Oracle data types. For some MySQL data types there is more than one alternative Oracle data type. The tables include information about the following:

2.3.3.1 Numeric Types

When mapping MySQL data types to numeric data types in Oracle, the following conditions apply:

  • If there is no precision or scale defined for the destination Oracle data type, precision and scale are taken from the MySQL source data type.
  • If there is a precision or scale defined for the destination data type, these values are compared to the equivalent values of the source data type and the maximum value is selected.

The following table compares the numeric types of MySQL to Oracle:

MySQL Size Oracle
BIGINT 8 Bytes NUMBER (19,0)
BIT approximately (M+7)/8 Bytes RAW
DECIMAL(M,D) M+2 bytes if D > 0, M+1 bytes if D = 0 (D+2, if M < D) FLOAT(24), BINARY_FLOAT
DOUBLE 8 Bytes FLOAT(24), BINARY_FLOAT, BINARY_DOUBLE
DOUBLE PRECION 8 Bytes FLOAT(24), BINARY_DOUBLE
FLOAT(25<=X <=53) 8 Bytes FLOAT(24), BINARY_FLOAT
FLOAT(X<=24) 4 Bytes FLOAT, BINARY_FLOAT
INT 4 Bytes NUMBER (10,0)
INTEGER 4 Bytes NUMBER (10,0)
MEDIUMINT 3 Bytes NUMBER (7,0)
NUMERIC M+2 bytes if D > 0, M+1 bytes if D = 0 (D+2, if M < D) NUMBER
REAL 8 Bytes FLOAT(24), BINARY_FLOAT
SMALLINT 2 Bytes NUMBER(5,0)
TINYINT 1 Byte NUMBER(3,0)

2.3.3.2 Date and Time Types

The following table compares the date and time types of MySQL to Oracle:

MySQL Size Oracle
DATE 3 Bytes DATE
DATETIME 8 Bytes DATE
TIMESTAMP 4 Bytes DATE
TIME 3 Bytes DATE
YEAR 1 Byte NUMBER

2.3.3.3 String Types

When mapping MySQL data types to character data types in Oracle, the following conditions apply:

  • If there is no length defined for the destination data type, the length is taken from the source data type.
  • If there is a length defined for the destination data type, the maximum value of the two lengths is taken.

The following table compares the string types of MySQL to Oracle:

Note:

Reference to M indicates the maximum display size. The maximum legal display size is 255. A reference to L applies to a floating point types and indicates the number of digits following the decimal point.

MySQL Size Oracle
BLOB L + 2 Bytes whereas L<2^16 RAW, BLOB
CHAR(m) M Bytes, 0<=M<=255 CHAR
ENUM (VALUE1, VALUE2, …) 1 or 2 Bytes depending on the number of enum. values (65535 values max)
LONGBLOB L + 4 Bytes whereas L < 2 ^ 32 RAW, BLOB
LONGTEXT L + 4 Bytes whereas L < 2 ^ 32 RAW, CLOB
MEDIUMBLOB L + 3 Bytes whereas L < 2^ 24 RAW, BLOB
MEDIUMTEXT L + 3 Bytes whereas L < 2^ 24 RAW, CLOB
SET (VALUE1, VALUE2, …) 1, 2, 3, 4 or 8 Bytes depending on the number of set members (64 members maximum)
TEXT L + 2 Bytes whereas L<2^16 VARCHAR2, CLOB
TINYBLOB L + 1 Bytes whereas L<2 ^8 RAW, BLOB
TINYTEXT L + 1 Bytes whereas L<2 ^8 VARCHAR2
VARCHAR(m) L+1 Bytes whereas L<=M and0<=M<=255 before MySQL 5.0.3 (0 <= M <= 65535 in MySQL 5.0.3 and later; effective maximum length is 65,532 bytes) VARCHAR2, CLOB

2.4 Data Storage Concepts

This section provide a description of the conceptual differences and similarities in data storage for MySQL and Oracle databases.

Data storage is an aspect of MySQL that sets it apart for nearly every database, including Oracle. In MySQL, databases correspond to directories within the data directory of the server. Tables within a database correspond to one or more files within the database directory, depending on the storage engine used for the tables.

A database can contain a mix of tables of different storage engines. A storage engine is responsible for the storage and retrieval of the data for a table.

MySQL offers a variety of storage engines (formerly called table types) to meet the different requirements of the user’s environment. Table 2-5 shows the storage engines supported by MySQL.

Table 2-5 Storage Engines Supported by MySQL

Storage Engine Description
MyISAM The default non-transactional storage engine that provides full-text indexing and is highly portable
MERGE A non-transactional storage engine that allows a collection of MyISAM tables with identical column and index information to be used as one
MEMORY (HEAP) A non-transactional storage engine that stores data in memory
BDB (Berkeley DB) The first transactional-safe storage engine
InnoDB A transactional-safe storage engine designed for maximum performance when processing large volume of data and that provides row-level locking
FEDERATED A storage engine that accesses data in tables of remote databases rather than in local tables
ARCHIVE A storage engine that can store large amount of data without indexes in very small footprint
CSV A storage engine that stores data in text file using comma-separated-values format
BLACKHOLE A storage engine that acts as a “black hole” that accepts data but throws it away and does not store it
EXAMPLE A “stub” engine that does nothing. Its purpose is to serve as an example that illustrates how to begin writing new engines.
ISAM The original MySQL storage engine that has been deprecated in favor of the MyISAM storage engine as of version 5.0

Each storage engine has its benefits and drawbacks. Some of features that differentiate the storage engines are transaction, locking, concurrency and portability. The following table summarizes the features for four of the commonly used storage engines.

Table 2-6 Feature Comparison for Common Storage Engines

Feature MyISAM Heap BDB InnoDB
Transactional No No Yes Yes
Lock granularity Table Table Page Row
Storage A data file (.MYD) and an index file (.MYI) for each table In-memory A single data and index file (.db) for each table A set of data files for all the tables
Portable Yes N/A No Yes

1,835 total views, 1 views today

댓글 남기기