Data types
With Amazon DMS, you can migrate data between different database platforms, allowing you to consolidate databases, perform database modernization, or migrate databases to the cloud. Data types define the kind of data that can be stored in a database column or variable. The following sections will provide detailed information about different data types supported by Oracle and MySQL.
Feature compatibility | Amazon SCT / Amazon DMS automation level | Amazon SCT action code index | Key differences |
---|---|---|---|
|
|
Aurora MySQL doesn’t support |
Oracle usage
Oracle provides a set of primitive data types for defining table columns and PL/SQL code variables. The assigned data types for table columns or PL/SQL code (such as stored procedures and triggers) define the valid values each column or argument can store.
Oracle data types and MySQL data types
Character data types
Oracle data type | Oracle data type characteristic | MySQL identical compatibility | MySQL corresponding data type |
---|---|---|---|
|
Maximum size of 2000 bytes |
Yes |
|
|
Maximum size of 2000 bytes |
Yes |
|
|
Maximum size of 2000 bytes |
Yes |
|
|
Maximum size of 2000 bytes |
Yes |
|
|
Varying-length UTF-8 string, maximum size of 4000 bytes |
Yes |
|
|
Maximum size of 4000 bytes or 32 KB in PL/SQL |
No |
|
|
Maximum size of 32767 bytes |
No |
|
|
Maximum size of 4000 bytes |
No |
|
|
Maximum size of 2 GB |
Yes |
|
|
Maximum size of 2000 bytes |
No |
|
|
Maximum size of 2 GB |
No |
|
Numeric data types
Oracle data type | Oracle data type characteristic | MySQL identical compatibility | MySQL corresponding data type |
---|---|---|---|
|
Floating-point number |
No |
|
|
Floating-point number |
No |
|
|
Precision can range from 1 to 38, scale can range from -84 to 127 |
No |
|
|
Precision can range from 1 to 38 |
Yes |
|
|
Floating-point number |
Yes |
|
|
Fixed-point number |
Yes |
|
|
Fixed-point number |
Yes |
|
|
38 digits integer |
Yes |
|
|
38 digits integer |
Yes |
|
|
38 digits integer |
Yes |
|
|
Floating-point number |
Yes |
|
|
Floating-point number |
Yes |
|
Date and time data types
Oracle data type | Oracle data type characteristic | MySQL identical compatibility | MySQL corresponding data type |
---|---|---|---|
|
Stores date and time data (year, month, day, hour, minute and second) |
Yes |
|
|
Date and time with fraction |
Yes |
|
|
Date and time with fraction and time zone |
No |
|
|
Date interval |
No |
|
|
Day and time interval |
No |
|
LOB data types
Oracle data type | Oracle data type characteristic | MySQL identical compatibility | MySQL corresponding data type |
---|---|---|---|
|
Pointer to binary file, maximum file size of 4 GB |
No |
|
|
Binary large object, maximum file size of 4 GB |
Yes |
|
|
Character large object, maximum file size of 4 GB |
No |
|
|
Variable-length Unicode string, maximum file size of 4 GB |
No |
|
ROWID data types
Oracle data type | Oracle data type characteristic | MySQL identical compatibility | MySQL corresponding data type |
---|---|---|---|
|
Physical row address |
No |
|
|
Universal row id, logical row addresses |
No |
|
XML data type
Oracle data type | Oracle data type characteristic | MySQL identical compatibility | MySQL corresponding data type |
---|---|---|---|
|
XML data |
No |
|
Logical data type
Oracle data type | Oracle data type characteristic | MySQL identical compatibility | MySQL corresponding data type |
---|---|---|---|
|
Values |
Yes |
|
Spatial types
Oracle data type | Oracle data type characteristic | MySQL identical compatibility | MySQL corresponding data type |
---|---|---|---|
|
The geometric description of a spatial object |
No |
N/A |
|
Describes a topology geometry |
No |
N/A |
|
A raster grid or image object is stored in a single row |
No |
N/A |
Media types
Oracle data type | Oracle data type characteristic | MySQL identical compatibility | MySQL corresponding data type |
---|---|---|---|
|
Supports the storage and management of audio data |
No |
N/A |
|
Supports the storage and management of Digital Imaging and Communications in Medicine (DICOM). |
No |
N/A |
|
Supports storage and management of any type of media data |
No |
N/A |
|
Supports the storage and management of image data |
No |
N/A |
|
Supports the storage and management of video data |
No |
N/A |
Oracle character column semantics
Oracle supports BYTE
and CHAR
semantics for column size, which determines the amount of storage allocated for CHAR
and VARCHAR
columns.
-
If you define a field as
VARCHAR2(10 BYTE)
, Oracle can use up to 10 bytes for storage. However, based on your database codepage and NLS settings, you may not be able to store 10 characters in that field because the physical size of some non-English characters exceeds one byte. -
If you define a field as
VARCHAR2(10 CHAR)
, Oracle can store 10 characters no matter how many bytes are required to store each non-English character.
CREATE TABLE table1 (col1 VARCHAR2(10 CHAR), col2 VARCHAR2(10 BYTE));
By default, Oracle uses BYTE
semantics. When using a multi-byte character set such as UTF8, use one of the following options.
-
Use the
CHAR
modifier in theVARCHAR2
orCHAR
column definition. -
Modify the session or system parameter
NLS_LENGTH_SEMANTICS
to change the default fromBYTE
toCHAR
.
ALTER system SET nls_length_semantics=char scope=both; ALTER system SET nls_length_semantics=byte scope=both; ALTER session SET nls_length_semantics=char; ALTER session SET nls_length_semantics=byte;
For more information, see Data Types
MySQL usage
MySQL provides multiple data types equivalent to certain Oracle data types. The following table provides the full list of MySQL data types.
Character data types
MySQL data type | MySQL data type characteristic |
---|---|
|
Stores exactly (n) characters. |
|
Stores a variable number of characters, up to a maximum of n characters. |
|
Stores exactly (n) bytes. |
|
Stores a variable number of characters, up to a maximum of n bytes. |
|
Binary large object that can hold a variable amount of data. |
|
Specific variant of varchar, which does not require you to specify an upper limit on the number of characters. |
|
String object with a value chosen from a list of permitted values that are enumerated explicitly in the column specification at table creation time. |
|
String object that can have zero or more values, each of which must be chosen from a list of permitted values specified when the table is created. |
Numeric data types
MySQL data type | MySQL data type characteristic |
---|---|
|
Max value is 2147483647. |
|
Max value is 2147483647. |
|
Max value is 32767. |
|
Max value is 127. |
|
Max value is 8388607. |
|
Max value is 2^63-1. |
|
Stores any value with p digits and s decimals. |
|
Stores any value with p digits and s decimals. |
|
Values can be stored with up to M digits in total, of which D digits may be after the decimal point. |
|
Values can be stored with up to M digits in total, of which D digits may be after the decimal point. |
|
Stores M-bit values. M can range from 1 to 64. |
Date and time data types
MySQL data type | MySQL data type characteristic |
---|---|
|
Values with a date part but no time part. MySQL retrieves and displays |
|
Values that contain both date and time parts. MySQL retrieves and displays |
|
Values that contain both date and time parts. |
|
Values may range from |
|
|
Logical data type
MySQL data type | MySQL data type characteristic |
---|---|
|
Holds a truth value. Will accept values such as TRUE, 't','true', 'y', 'yes', and '1' as true. Uses 1 byte of storage, and can store NULL. |
Geometric data types
MySQL data type | MySQL data type characteristic |
---|---|
|
The column type to specify when you want to use the data models below. |
|
An (x,y) value. |
|
A line (pt1, pt2). |
|
A sequence of points, effectively a closed path. |
|
Collection of |
|
Collection of |
|
Collection of |
|
Collection of geometry data types. |
Other data types
MySQL data type | MySQL data type characteristic |
---|---|
|
Textual JSON data |
Migration of Oracle data types to MySQL data types
You can perform automatic migration and conversion of Oracle tables and data types using Amazon Schema Conversion Tool (Amazon SCT).
Examples
To demonstrate Amazon SCT capability for migrating Oracle tables to their MySQL equivalents, a table containing columns representing the majority of Oracle data types was created and converted using Amazon SCT.
Source Oracle compatible DDL for creating the DATATYPES
table.
CREATE TABLE "DATATYPES"( "BFILE" BFILE, "BINARY_FLOAT" BINARY_FLOAT, "BINARY_DOUBLE" BINARY_DOUBLE, "BLOB" BLOB, "CHAR" CHAR(10 BYTE), "CHARACTER" CHAR(10 BYTE), "CLOB" CLOB, "NCLOB" NCLOB, "DATE" DATE, "DECIMAL" NUMBER(3,2), "DEC" NUMBER(3,2), "DOUBLE_PRECISION" FLOAT(126), "FLOAT" FLOAT(3), "INTEGER" NUMBER(*,0), "INT" NUMBER(*,0), "INTERVAL_YEAR" INTERVAL YEAR(4) TO MONTH, "INTERVAL_DAY" INTERVAL DAY(4) TO SECOND(4), "LONG" LONG, "NCHAR" NCHAR(10), "NCHAR_VARYING" NVARCHAR2(10), "NUMBER" NUMBER(9,9), "NUMBER1" NUMBER(9,0), "NUMBER(*)" NUMBER, "NUMERIC" NUMBER(9,9), "NVARCHAR2" NVARCHAR2(10), "RAW" RAW(10), "REAL" FLOAT(63), "ROW_ID" ROWID, "SMALLINT" NUMBER(*,0), "TIMESTAMP" TIMESTAMP(5), "TIMESTAMP_WITH_TIME_ZONE" TIMESTAMP(5) WITH TIME ZONE, "UROWID" UROWID(10), "VARCHAR" VARCHAR2(10 BYTE), "VARCHAR2" VARCHAR2(10 BYTE), "XMLTYPE" XMLTYPE );
Target MySQL compatible DDL for creating the DATATYPES
table migrated from Oracle with Amazon SCT.
CREATE TABLE IF NOT EXISTS datatypes( bfile VARCHAR(1000) DEFAULT NULL, BINARY_FLOAT FLOAT(12) DEFAULT NULL, BINARY_DOUBLE DOUBLE DEFAULT NULL, `BLOB` LONGBLOB DEFAULT NULL, `CHAR` CHAR(10) DEFAULT NULL, `CHARACTER` CHAR(10) DEFAULT NULL, CLOB LONGTEXT DEFAULT NULL, NCLOB LONGTEXT DEFAULT NULL, `DATE` DATETIME DEFAULT NULL, `DECIMAL` DECIMAL(3,2) DEFAULT NULL, `DEC` DECIMAL(3,2) DEFAULT NULL, DOUBLE_PRECISION DOUBLE DEFAULT NULL, `FLOAT` DOUBLE DEFAULT NULL, `INTEGER` DECIMAL(38,0) DEFAULT NULL, `INT` DECIMAL(38,0) DEFAULT NULL, INTERVAL_YEAR VARCHAR(30) DEFAULT NULL, INTERVAL_DAY VARCHAR(30) DEFAULT NULL, `LONG` LONGTEXT DEFAULT NULL, NCHAR CHAR(10) DEFAULT NULL, NCHAR_VARYING VARCHAR(10) DEFAULT NULL, NUMBER DECIMAL(9,9) DEFAULT NULL, NUMBER1 DECIMAL(9,0) DEFAULT NULL, `NUMBER(*)` DOUBLE DEFAULT NULL, `NUMERIC` DECIMAL(9,9) DEFAULT NULL, NVARCHAR2 VARCHAR(10) DEFAULT NULL, RAW VARBINARY(10) DEFAULT NULL, `REAL` DOUBLE DEFAULT NULL, ROW_ID CHAR(10) DEFAULT NULL, `SMALLINT` DECIMAL(38,0) DEFAULT NULL, `TIMESTAMP` DATETIME(5) DEFAULT NULL, TIMESTAMP_WITH_TIME_ZONE DATETIME(5) DEFAULT NULL, UROWID VARCHAR(10) DEFAULT NULL, `VARCHAR` VARCHAR(10) DEFAULT NULL, VARCHAR2 VARCHAR(10) DEFAULT NULL, XMLTYPE LONGTEXT DEFAULT NULL);
Amazon SCT converted most of the data types. However, a few exceptions were raised for data types that Amazon SCT is unable to automatically convert and where Amazon SCT recommended manual actions.
MySQL doesn’t have a data type BFILE
BFILE
s are pointers to binary files.
Recommended actions: Either store a named file with the data and create a routine that gets that file from the file system, or store the data blob inside your database.
MySQL doesn’t have a data type ROWID
ROWID
s are physical row addresses inside Oracle storage subsystems. The ROWID
data type is primarily used for values returned by the ROWID
pseudocolumn.
Recommended actions: Although MySQL contains a ctid
column that is the physical location of the row version within its table, it doesn’t have a comparable data type. However, you can use CHAR
as a partial data type equivalent. If you use ROWID
data types in your code, modifications may be necessary.
MySQL doesn’t have a data type UROWID
Universal row identifier, or UROWID
, is a single Oracle data type that supports both logical and physical row identifiers of foreign table row identifiers such as non-Oracle tables accessed through a gateway.
Recommended actions: MySQL doesn’t have a comparable data type. You can use VARCHAR(n)
as a partial data type equivalent. However, if you are using UROWID
data types in your code, modifications may be necessary.
For more information, see Schema Conversion Tool Documentation