Data Type Mapping Between Oracle and MySQL

By Hassan Shareef, Database Administrator — 16 years of experience

When converting data from Oracle to MySQL, we need to map Oracle column data types to their equivalent in MySQL. Most datatypes map directly since their behavior is the same in both databases, but for some — like NUMBER, BLOB, and CLOB — we need to choose the nearest equivalent.

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.

Planning to migrate data between an Oracle database and MySQL? Download and use Data Loader and achieve migration in just a few clicks.

Free Download Now

1. Numeric Data Types

Numeric data types 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's treated as an integer.
NUMBER (without p,s)	DOUBLE or DECIMAL	Oracle's flexible NUMBER has no direct equivalent — use DECIMAL for exact values, DOUBLE for floating points.
BINARY_FLOAT	FLOAT	Single-precision floating point.
BINARY_DOUBLE	DOUBLE	Double-precision floating point.
FLOAT(p)	FLOAT(p) or DOUBLE	Oracle FLOAT(p) is an alias for NUMBER(p,-127). MySQL uses FLOAT for precision ≤24, DOUBLE for >24.
INTEGER / INT	INT	Equivalent in both databases.
SMALLINT	SMALLINT	Equivalent in both databases.
BIGINT	BIGINT	Equivalent in both databases.

Considerations

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

MySQL doesn't differentiate between VARCHAR2 and VARCHAR — both are stored as VARCHAR.
TEXT in MySQL doesn't 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 doesn't natively support time zones in timestamps.
TIMESTAMP WITH LOCAL TIME ZONE	TIMESTAMP	Time zone is stored per session in Oracle but not in MySQL.

Considerations

Oracle's DATE stores both date and time; MySQL's DATE stores only the date.
MySQL's TIMESTAMP is more similar to Oracle's TIMESTAMP.
MySQL lacks built-in time-zone-aware timestamps, but you can manually adjust with 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

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

Oracle's ROWID is an internal identifier; MySQL has no 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.