Skip to content
IntoSQL home
Convert a file

Data types

MySQL to Oracle data types

Every MySQL data type that Oracle stores differently, what the converter writes for it, when, and the report line.

View as Markdown

Each row is a type from MySQL that Oracle stores differently: what the Oracle script gets, why, and the line the report writes about it. The rows come from the converter's own rules, so this page matches what it does.

Convert in the browser: MySQL to Oracle opens the converter set up for this pair. The MySQL to Oracle migration guide covers dumping, loading and the rules for keys, indexes and names.

A report line is a fix when the converter changed something and lists it, or a problem when a value or object is worth a look.

Numbers

In MySQL In Oracle Why Report line
UNSIGNED integers NUMBER wide enough for the range, with a CHECK that it isn't negative Oracle's NUMBER holds any integer of the widths MySQL has; the check keeps negative values out as MySQL did. None
ZEROFILL and display widths dropped Zero padding is a way of showing a number, not part of it. The numbers are kept; format them when you read them if the padding matters. zerofill_dropped (fix)
FLOAT(M,D) BINARY_FLOAT or BINARY_DOUBLE, without the scale Oracle's binary floats have no fixed scale. The stored values are copied as they are. float_scale_dropped (fix)
DECIMAL(p,s) NUMBER(p,s) up to 38 digits; VARCHAR2 beyond Oracle's NUMBER holds 38 digits. Wider decimals are kept as text so every digit stays exact. decimal_as_text (fix)

Booleans and bits

In MySQL In Oracle Why Report line
TINYINT(1), BOOL NUMBER(1) with a CHECK of 0 and 1 when every value is 0 or 1, else NUMBER(3) MySQL's BOOL is a TINYINT(1) that can hold any number from -128 to 127. Oracle before 23ai has no boolean column, so a column of 0 and 1 is NUMBER(1) with a check; one that holds 2 or -1 keeps its numbers. bool_kept_numeric (fix)
BIT(1), BIT(n) NUMBER(1), NUMBER(19) up to 63 bits, NUMBER(20) for BIT(64) Bit fields are stored as the number their bits make. None

Text

In MySQL In Oracle Why Report line
empty strings and empty binary values NULL Oracle stores an empty string as NULL, so a value that was ' reads back as NULL. The report counts them per column. empty_string_null (problem)
CHAR(n), VARCHAR(n) VARCHAR2(n CHAR), counting characters Lengths count characters as MySQL's do, and MySQL returns CHAR values without trailing spaces, as VARCHAR2 keeps them. None
VARCHAR(n) and TEXT longer than 4000 bytes CLOB A VARCHAR2 holds 4000 bytes by default, so longer text is a CLOB; a column whose values all fit keeps VARCHAR2. text_as_lob (fix)
NUL bytes in text kept VARCHAR2 and CLOB can hold a NUL character, so the value is copied as it is. None
text that isn't valid in its character set the bad bytes as the replacement character Text has to be read as characters to be written as VARCHAR2. invalid_utf8_replaced (problem)

Enums and sets

In MySQL In Oracle Why Report line
ENUM VARCHAR2 with a CHECK of its values Text with a check keeps the same values. None
ENUM holding '' (MySQL's error value) NULL MySQL stores an empty string when an invalid value goes into an ENUM in a lenient SQL mode. Oracle stores it as NULL, which the check allows, and the report counts it with the other empty values. None
ENUM holding values outside its list no CHECK Values the list doesn't name would fail the check, so the check isn't created. enum_check_skipped (problem)
SET VARCHAR2 as MySQL writes it ('a,b') Oracle has no set type; the text is the one MySQL returns. None

Dates and times

In MySQL In Oracle Why Report line
zero, partial or impossible dates (0000-00-00, 2024-02-30) NULL Oracle only stores real calendar dates. MySQL keeps zero and impossible dates when its SQL mode allows them; they become NULL and the report counts them. invalid_date_null (problem)
DEFAULT '0000-00-00' no default A default Oracle can't store is left out rather than replaced with a made-up date. zero_date_default (fix)
TIME INTERVAL DAY TO SECOND, which holds durations Oracle has no time-of-day type, and MySQL's TIME is a duration from -838:59:59 to 838:59:59; an interval holds every value. None
YEAR NUMBER(4) with a CHECK of its range Oracle has no year type; the check keeps the same values MySQL allows. None
DATETIME TIMESTAMP A DATETIME is a wall-clock reading without a time zone, like TIMESTAMP. DATE becomes Oracle's DATE, which also holds a time of day that stays at midnight. None
TIMESTAMP TIMESTAMP WITH TIME ZONE in the zone the dump states, UTC when it states none A TIMESTAMP is a moment. Dumps state the zone they wrote it in (mysqldump writes UTC); without that, the values are read as UTC and the report says so. timestamp_zone_assumed (problem)

Binary

In MySQL In Oracle Why Report line
BINARY, VARBINARY, BLOB RAW up to 2000 bytes, else BLOB Bytes stay bytes. None

JSON, spatial and other types

In MySQL In Oracle Why Report line
JSON CLOB Oracle's JSON functions read JSON from text on every version, and a CLOB keeps values of any size. None
GEOMETRY, POINT, POLYGON... BLOB holding WKB WKB is the standard form every spatial library reads; SDO_GEOMETRY needs Oracle Spatial's metadata. geometry_as_blob (fix)
INET4, INET6, UUID (MariaDB) VARCHAR2(39), VARCHAR2(36) Oracle has neither type; the text is the one MariaDB returns. None
geometries in a dump without CREATE TABLE BLOB holding WKB MySQL's geometry format is recognised from the values and stored as standard WKB. geometry_inferred (fix)
types IntoSQL doesn't know CLOB Text holds any value; change the type after loading. unknown_type (problem)
values that don't fit their column NULL A damaged or hand-written dump can hold values its column can't; they become NULL so the rest loads. invalid_value_null (problem)
unreadable geometries NULL Bytes that aren't a geometry can't be stored as one. bad_geometry (problem)
VECTOR(n) (MariaDB 11.7+) CLOB with the floats as a JSON array Only Oracle 23ai has a vector type; a JSON array keeps every float on every version. type_replaced (fix)

Counters and identities

In MySQL In Oracle Why Report line
AUTO_INCREMENT GENERATED BY DEFAULT ON NULL AS IDENTITY, restarted past the copied ids The copied ids load as they are, and the counter continues after the highest one or the dump's AUTO_INCREMENT, whichever is higher. None
MyISAM AUTO_INCREMENT on the second column of a key one counter for the table MyISAM counts per group of the key's first column; Oracle has one counter per table. myisam_group_autoincrement (problem)

Other pairs