Data Type Mapping Between Oracle and MySQL

Author: Hassan Shareef,
Database Administrator
Since 16 years

When converting data from Oracle to MySQL, we need to map the Oracle table column data types to their equivalent data types in MySQL. While most the datatypes can be mapped directly because their behavior is same in both the databases, but for some datatypes we need to choose the nearest one as their direct equivalent datatype is not available in MySQL. Some examples are NUMBER, BLOB, CLOB etc.

When migrating data between Oracle and MySQL, it's essential to understand how Oracle data types correspond to MySQL data types to prevent data loss, truncation, or compatibility issues.

Oracle and MySQL use different sets of data types for storing numbers, strings, dates, and other values. Here is the comparison of these data types.

Planning to Migrate data between Oracle database to MySQL. Then download and use Data Loader and achieve migration in just few clicks. Free Download Now

1. Numeric Data Types

Numeric data types are used to store numerical values, including integers and floating-point numbers.

Oracle Data Type	MySQL Equivalent	Remarks
NUMBER(p,s)	DECIMAL(p,s) or NUMERIC(p,s)	Same precision and scale handling in both databases.
NUMBER(p)	DECIMAL(p,0) or BIGINT	If no scale is given, it is treated as an integer.
NUMBER (without p,s)	DOUBLE or DECIMAL	Oracle’s flexible NUMBER type has no direct equivalent in MySQL. Consider DECIMAL for exact values and DOUBLE for floating points.
BINARY_FLOAT	FLOAT	Single-precision floating point.
BINARY_DOUBLE	DOUBLE	Double-precision floating point.
FLOAT(p)	FLOAT(p) or DOUBLE	In Oracle, FLOAT(p) is an alias for NUMBER(p,-127). MySQL uses FLOAT for precision ≤ 24 and DOUBLE for precision > 24.
INTEGER / INT	INT	Equivalent in both databases.
SMALLINT	SMALLINT	Equivalent in both databases.
BIGINT	BIGINT	Equivalent in both databases.

Considerations for Numeric Data Types:

Oracle’s NUMBER type is more flexible than MySQL’s numeric types.
Use DECIMAL or NUMERIC in MySQL for fixed-point precision.
For floating-point values, FLOAT and DOUBLE are used in both databases.

2. String Data Types

String data types store character-based data.

Oracle Data Type	MySQL Equivalent	Remarks
CHAR(n)	CHAR(n)	Fixed-length character string.
VARCHAR2(n)	VARCHAR(n)	Variable-length character string.
NCHAR(n)	CHAR(n) CHARACTER SET utf8mb4	National character type for Unicode support.
NVARCHAR2(n)	VARCHAR(n) CHARACTER SET utf8mb4	National character type for Unicode support.
CLOB	TEXT or LONGTEXT	Oracle’s CLOB is for large text storage; MySQL uses TEXT types.
NCLOB	TEXT CHARACTER SET utf8mb4`	Unicode equivalent of CLOB.
LONG	TEXT or LONGTEXT	LONG is deprecated in Oracle; use TEXT in MySQL.
RAW(n)	VARBINARY(n)	Binary data storage.
LONG RAW	BLOB	Large binary object storage.

Considerations for String Data Types:

MySQL does not differentiate between VARCHAR2 and VARCHAR; both are stored as VARCHAR.
TEXT in MySQL does not support indexing directly on large sizes, while Oracle's CLOB does.
For Unicode characters, use utf8mb4 encoding in MySQL.

3. Date and Time Data Types

Oracle and MySQL store date and time data differently.

Oracle Data Type	MySQL Equivalent	Remarks
DATE	DATETIME	Oracle’s DATE includes time; MySQL’s DATE does not. Use DATETIME for full equivalence.
TIMESTAMP	TIMESTAMP	Stores date and time with fractional seconds.
TIMESTAMP WITH TIME ZONE	TIMESTAMP + CONVERT_TZ()	MySQL does not support time zones in timestamps natively. Use CONVERT_TZ().
TIMESTAMP WITH LOCAL TIME ZONE	TIMESTAMP	Time zone is stored per session in Oracle but not in MySQL.

Considerations for Date and Time:

Oracle’s DATE type stores both date and time, while MySQL’s DATE stores only the date.
MySQL’s TIMESTAMP is more similar to Oracle’s TIMESTAMP.
MySQL lacks built-in support for time zone-aware timestamps, but you can manually adjust using CONVERT_TZ().

4. LOB (Large Object) Data Types

LOB types are used for large data storage.

Oracle Data Type	MySQL Equivalent	Remarks
BLOB	BLOB	Equivalent.
CLOB	TEXT	Equivalent for text-based large storage.
NCLOB	TEXT CHARACTER SET utf8mb4	Unicode large text storage.

Considerations for LOB Types:

MySQL uses TEXT and BLOB, which have different behavior regarding indexing and storage.
TEXT columns in MySQL require additional handling for indexing.

5. Miscellaneous Data Types

Oracle Data Type	MySQL Equivalent	Remarks
ROWID	No direct equivalent	Unique row identifier in Oracle.
UROWID	No direct equivalent	Universal row identifier.
XMLTYPE	TEXT or JSON	Oracle’s XML storage can be handled using TEXT or JSON in MySQL.

Considerations for Miscellaneous Types:

Oracle’s ROWID is an internal identifier; MySQL does not have a direct counterpart.
MySQL has JSON for structured data storage, which can replace XMLTYPE in some use cases.

6. Handling Auto-Increment Columns

In Oracle, auto-increment behavior is handled using sequences, whereas MySQL provides AUTO_INCREMENT.

Oracle:

CREATE SEQUENCE my_seq START WITH 1 INCREMENT BY 1;
CREATE TABLE my_table (
  id NUMBER PRIMARY KEY DEFAULT my_seq.NEXTVAL
);

MySQL:

CREATE TABLE my_table (
  id INT AUTO_INCREMENT PRIMARY KEY
);

Migration Tip:

Replace Oracle SEQUENCE.NEXTVAL with AUTO_INCREMENT in MySQL.
If sequence-based IDs are needed, maintain a separate table in MySQL to mimic Oracle sequences.

7. Summary of Key Differences

Number Handling: Oracle’s NUMBER type is highly flexible, whereas MySQL requires explicit DECIMAL, INT, or DOUBLE.
String Storage: Oracle differentiates between VARCHAR2 and VARCHAR, while MySQL does not.
Date Handling: Oracle’s DATE includes time, but MySQL requires DATETIME or TIMESTAMP.
LOBs: Oracle’s CLOB maps to MySQL’s TEXT, but indexing and performance differ.
Auto-Increment: MySQL provides AUTO_INCREMENT, whereas Oracle requires sequences.

When migrating data between Oracle and MySQL, careful data type mapping is crucial to avoid truncation, performance degradation, or compatibility issues. Use tools like, Data Loader, Oracle SQL Developer, MySQL Workbench, or custom scripts to automate and validate conversions.