Heterogeneous databases have different data types. When Data Transmission Service (DTS) migrates data between heterogeneous databases, DTS converts the data types of the source database to those of the destination database. This topic lists the data type mappings for you to evaluate the impact of data migration on your business.

Overview

You can view the data type mappings between heterogeneous databases based on the following migration scenarios:

Data migration from a PolarDB for MySQL cluster, an ApsaraDB RDS for MySQL instance, or a self-managed MySQL database

The following tables list the data type mappings between MySQL and AnalyticDB databases. The source instance can be a PolarDB for MySQL cluster, an ApsaraDB RDS for MySQL instance, or a self-managed MySQL database. The destination instance can be an AnalyticDB for MySQL cluster V2.0 or an AnalyticDB for PostgreSQL instance.
Note If the value range of the data to be migrated from the source instance exceeds the range supported by DTS, the accuracy of the data migrated to the destination instance will decrease.
Type Data type of the source instance Value range Data type of AnalyticDB for MySQL Data type of AnalyticDB for PostgreSQL Data type of a Message Queue for Apache Kafka instance or a self-managed Kafka cluster
Integer BIT[(M)] 1 ~ 64 VARCHAR BIT Consistent with the data types of MySQL or PolarDB for MySQL
TINYINT[(M)] -128 ~ 127 TINYINT SMALLINT
TINYINT[(M)] [UNSIGNED] 0 ~ 255 SMALLINT SMALLINT
SMALLINT[(M)] -32768 ~ 32767 SMALLINT SMALLINT
SMALLINT[(M)] [UNSIGNED] 0 ~ 65535 INT INTEGER
MEDIUMINT[(M)] -8388608 ~ 8388607 INT INTEGER
MEDIUMINT[(M)] [UNSIGNED] 0 ~ 16777215 INT INTEGER
INT[(M)] -2147483648 ~ 2147483647 INT INTEGER
INT[(M)] [UNSIGNED] 0 ~ 4294967295 BIGINT BIGINT
BIGINT[(M)] -9223372036854775808 ~ 9223372036854775807 BIGINT BIGINT
BIGINT[(M)] [UNSIGNED] 0 ~ 18446744073709551615. DECIMAL(20,0) NUMERIC(20)
Decimal DECIMAL[(M[,D])] M: 0 to 65
D: 0 to 30
 DECIMAL[(M[,D])] DECIMAL
FLOAT(p) 1.175494351E-38 ~ 3.402823466E+38 FLOAT REAL
DOUBLE[(M,D)] 2.2250738585072014E-308 ~ 1.7976931348623157E+308 DOUBLE DOUBLE PRECISION
Date and time DATE 1000-01-01~9999-12-31
Note The format is YYYY-MM-DD, in UTC.
DATE DATE
DATETIME[(fsp)] 1000-01-01 00:00:00.000000 ~ 9999-12-31 23:59:59.999999
Note The format is YYYY-MM-DD hh:mm:ss[.fraction], in UTC.
DATETIME TIMESTAMP
TIMESTAMP[(fsp)] 1970-01-01 00:00:01.000000 ~ 2038-01-19 03:14:07.999999
Note The format is YYYY-MM-DD hh:mm:ss[.fraction], in UTC.
TIMESTAMP TIMESTAMP WITH TIME ZONE
TIME[(fsp)] -838:59:59.000000 ~ 838:59:59.000000
Note The format is hh:mm:ss[.fraction], in UTC.
TIME TIME
YEAR[(4)] 1901 to 2155, or 0000 INT INTEGER
String CHAR[(M)] 0 to 255 characters VARCHAR CHAR
VARCHAR(M) 0 to 65,535 characters VARCHAR VARCHAR
BINARY[(M)] 0 to 255 bytes VARBINARY BYTEA
VARBINARY(M) 0 to 65,535 bytes VARBINARY BYTEA
TINYBLOB 255 (2^8 - 1) bytes VARBINARY BYTEA
TINYTEXT 255 (2^8 - 1) characters VARCHAR TEXT
BLOB 65,535 (2^16 - 1) bytes VARBINARY BYTEA
TEXT 65,535 (2^16 - 1) characters VARCHAR TEXT
MEDIUMBLOB 16,777,215 (2^24 - 1) bytes VARBINARY BYTEA
MEDIUMTEXT 16,777,215 (2^24 - 1) characters VARCHAR TEXT
LONGBLOB 4,294,967,295 or 4 GB (2^32 - 1) bytes VARBINARY BYTEA
LONGTEXT 4,294,967,295 or 4 GB (2^32 - 1) characters VARCHAR TEXT
ENUM('value1','value2',...) An ENUM column can have a maximum of 65,535 distinct elements. VARCHAR VARCHAR(128)
SET('value1','value2',...) A SET column can have a maximum of 64 distinct elements. VARCHAR VARCHAR(128)
Spatial GEOMETRY Geometry values of any type VARBINARY POLYGON
POINT N/A VARBINARY POINT
LINESTRING N/A VARBINARY PATH
POLYGON N/A VARBINARY POLYGON
MULTIPOINT N/A VARBINARY POLYGON
MULTILINESTRING N/A VARBINARY PATH
MULTIPOLYGON N/A VARBINARY POLYGON
GEOMETRYCOLLECTION A collection of geometry values of any type VARBINARY POLYGON
JSON JSON N/A JSON JSON

Data migration from an Oracle database

The following table lists the data type mappings between Oracle and heterogeneous databases. The destination instance can be a self-managed MySQL database, a PolarDB for MySQL cluster, an AnalyticDB for MySQL cluster, an AnalyticDB for PostgreSQL instance, or a PolarDB-O cluster.
Note If the value range of the data to be migrated from the source instance exceeds the range supported by DTS, the accuracy of the data migrated to the destination instance will decrease.
Type Data type of Oracle Value range Data type of MySQL, PolarDB for MySQL, or PolarDB-X Data type of ApsaraDB RDS for PPAS Data type of AnalyticDB for MySQL Data type of AnalyticDB for PostgreSQL Data type of PolarDB-O
Numeric NUMBER(p,s) 1 to 22 bytes
The argument p indicates the precision. Valid values: 1 to 38.
The argument s indicates the scale. Valid values: -84 to 127.
DECIMAL[(p[,s])] NUMBER[(p[,s])] DECIMAL | TINYINT | SMALLINT | INTEGER | BIGINT DECIMAL | TINYINT | SMALLINT | INTEGER | BIGINT NUMBER(p,s)
FLOAT(p) 1 to 22 bytes
The variable p indicates a pointer. Valid values: 1 to 126 bits.
DOUBLE DOUBLE PRECISION DOUBLE DOUBLE PRECISION DOUBLE PRECISION
BINARY_FLOAT A 32-bit floating-point number (4 bytes) DECIMAL(65,8) REAL DOUBLE DOUBLE PRECISION REAL
BINARY_DOUBLE A 64-bit floating-point number (8 bytes) DOUBLE DOUBLE PRECISION DOUBLE DOUBLE PRECISION DOUBLE PRECISION
Date and time DATE N/A DATETIME DATE DATETIME TIMESTAMP(0) DATE
TIMESTAMP [(fractional_seconds_precision)] N/A DATETIME[(fractional_seconds_precision)] TIMESTAMP [(fractional_seconds_precision)] DATETIME TIMESTAMP TIMESTAMP [(fractional_seconds_precision)]
TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE N/A DATETIME[(fractional_seconds_precision)] TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE TIMESTAMP TIMESTAMP WITH TIME ZONE TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE
TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONE N/A DATETIME[(fractional_seconds_precision)] TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE DATETIME TIMESTAMP WITH TIME ZONE TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE
INTERVAL YEAR [(year_precision)] TO MONTH N/A Not supported Not supported VARCHAR VARCHAR(32) INTERVAL
INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)] N/A Not supported Not supported VARCHAR VARCHAR(32) INTERVAL
String CHAR [(size [BYTE | CHAR])] 2,000 bytes CHAR[(n)] CHAR[(n)] VARCHAR CHAR CHAR [(size [BYTE | CHAR])]
NCHAR[(size)] 2,000 bytes NATIONAL CHAR[(n)] NCHAR[(n)] VARCHAR VARCHAR NCHAR[(size)]
VARCHAR2(size [BYTE | CHAR]) If MAX_STRING_SIZE is set to EXTENDED, the maximum size is 32,767 bytes.
If MAX_STRING_SIZE is set to STANDARD, the maximum size is 4,000 bytes.
VARCHAR(n) VARCHAR2[(n)] VARCHAR VARCHAR VARCHAR2(size [BYTE | CHAR])
NVARCHAR2(size) If MAX_STRING_SIZE is set to EXTENDED, the maximum size is 32,767 bytes.
If MAX_STRING_SIZE is set to STANDARD, the maximum size is 4,000 bytes.
NATIONALVARCHAR[(n)] VARCHAR2[(n)] VARCHAR VARCHAR NVARCHAR2(size)
LONG The maximum size is 2 GB (2^31 - 1). LONGTEXT LONG VARCHAR TEXT LONG
RAW(size) The maximum size is 32,767 bytes or 2,000 bytes. VARBINARY(2000) RAW(size) VARBINARY BYTEA RAW(size)
LONG RAW The maximum size is 2 GB. LONGBLOB LONG RAW VARBINARY BYTEA LONG RAW
CLOB The maximum size is (4 GB - 1) × DB_BLOCK_SIZE. LONGTEXT CLOB VARCHAR TEXT CLOB
NCLOB The maximum size is (4 GB - 1) × DB_BLOCK_SIZE. LONGTEXT NCLOB VARCHAR TEXT CLOB
BLOB The maximum size is (4 GB - 1) × DB_BLOCK_SIZE. LONGBLOB BLOB VARBINARY BYTEA BLOB
BFILE The maximum size is 4 GB. Not supported Not supported Not supported Not supported Not supported
JSON JSON The maximum size is 32 MB. Not supported Not supported JSON JSON JSON
ROWID ROWID 64 characters Not supported Not supported ROWID OID VARCHAR
Spatial Customization required Not supported
Note
  • Destination instance: MySQL, PolarDB for MySQL, or PolarDB-X
    • If a CHAR field in the source database is greater than 255 bytes in length, DTS converts this field to the VARCHAR(n) type in the destination database.
    • MySQL does not support the following Oracle data types: BFILE, INTERVAL YEAR TO MONTH, and INTERVAL DAY TO SECOND. Therefore, DTS does not convert these data types during schema migration.

      If a table to be migrated contains these data types, schema migration fails. You must make sure that the columns with these data types are excluded from the objects to be migrated.

    • The TIMESTAMP data type of MySQL databases does not contain the time zone information. However, the IMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE data types in Oracle databases provide the time zone information. Therefore, DTS converts the values of these data types to UTC time in the destination database.
  • Destination instance: ApsaraDB RDS for PPAS

    ApsaraDB RDS for PPAS does not support the TIMESTAMP[(fractional_seconds_precision)] WITH LOCAL TIME ZONE data type. DTS converts the data of this type to UTC time and then stores the data in the destination ApsaraDB RDS for PPAS instance by using the TIMESTAMP[(fractional_seconds_precision)] WITH TIME ZONE data type.

  • Destination instance: AnalyticDB for PostgreSQL

    If an Oracle data type is not supported by AnalyticDB for PostgreSQL, DTS converts the data type to BYTEA. If the conversion fails, DTS sets the field value to NULL.

Data migration from an SQL Server database

The following table lists the data type mappings between SQL Server and AnalyticDB databases. The source instance can be a self-managed SQL Server database or an ApsaraDB RDS for SQL Server instance. The destination instance can be an AnalyticDB for MySQL cluster or an AnalyticDB for PostgreSQL instance.
Note If the value range of the data to be migrated from the source instance exceeds the range supported by DTS, the accuracy of the data migrated to the destination instance will decrease.
Type Data type of SQL Server Value range Data type of AnalyticDB for MySQL Data type of AnalyticDB for PostgreSQL
Integer BIT An INTEGER data type that can take a value of 1, 0, or NULL BOOLEAN BIT(1)
TINYINT 0 to 255 TINYINT SMALLINT
SMALLINT -2^15 (-32,768) to 2^15 - 1 (32,767) SMALLINT SMALLINT
INT -2^31 (-2,147,483,648) to 2^31 - 1 (2,147,483,647) INTEGER INTEGER
BIGINT -2^63 (-9,223,372,036,854,775,808) to 2^63 - 1 (9,223,372,036,854,775,807) BIGINT BIGINT
Decimal NUMERIC[ (p[ ,s] )] -10^38 + 1 to 10^38 - 1 (1 <= p <= 38) DECIMAL DECIMAL
DECIMAL[ (p[ ,s] )] -10^38 + 1 to 10^38 - 1 (1 <= p <= 38) DECIMAL DECIMAL
FLOAT -1.79E + 308 to -2.23E - 308, 0, and 2.23E - 308 to 1.79E + 308 DOUBLE DOUBLE PRECISION
REAL -3.40E + 38 to -1.18E - 38, 0, and 1.18E - 38 to 3.40E + 38 FLOAT REAL
Monetary MONEY -922,337,203,685,477.5808 to 922,337,203,685,477.5807 DECIMAL(19, 4) DECIMAL(19, 4)
SMALLMONEY -214,748.3648 to 214,748.3647 DECIMAL(10, 4) DECIMAL(10, 4)
Date and time DATE 0001-01-01 to 9999-12-31 DATE DATE
DATETIME Date range: January 1, 1753 to December 31, 9999
Time range: 00:00:00 to 23:59:59.997
 DATETIME TIMESTAMP(3) WITHOUT TIME ZONE
DATETIME2[ (fractional seconds precision) ] Date range: January 1,1 CE to December 31, 9999 CE
Time range: 00:00:00 to 23:59:59.9999999
DATETIME TIMESTAMP(7) WITHOUT TIME ZONE
DATETIMEOFFSET [ (fractional seconds precision) ] Date range: January 1, 1 CE to December 31, 9999 CE
Time range: 00:00:00 to 23:59:59.9999999
Time zone offset range: -14:00 to +14:00
 TIMESTAMP TIMESTAMP(7) WITH TIME ZONE
SMALLDATETIME The time is based on a 24-hour day, with seconds always zero (:00) and without fractional seconds. DATETIME TIMESTAMP WITHOUT TIME ZONE
TIME [ (fractional second scale) ] 00:00:00.0000000 to 23:59:59.9999999 TIME TIME(7) WITH TIME ZONE
String BINARY [ ( n ) ] Valid values of n: 1 to 8,000. VARBINARY BYTEA
VARBINARY [ ( n | max) ] Valid values of n: 1 to 8,000. max indicates that the maximum storage size is 2^31 - 1 bytes. VARBINARY BYTEA
CHAR [ ( n ) ] Valid values of n: 1 to 8,000. The storage size is n bytes. VARCHAR CHARACTER
VARCHAR [ ( n | max ) ] Valid values of n: 1 to 8,000. max indicates that the maximum storage size is 2^31 - 1 bytes (2 GB). VARCHAR CHARACTER
NCHAR [ ( n ) ] n defines the string size in byte-pairs. Valid values of n: 1 to 4,000. The storage size is two times n bytes. VARCHAR CHARACTER VARYING
NVARCHAR [ ( n | max ) ] n defines the string size in byte-pairs. Valid values of n: 1 to 1 to 4,000. max indicates that the maximum storage size is 2^30 - 1 characters (2 GB). VARCHAR CHARACTER VARYING
NTEXT Variable-length Unicode data with a maximum string length of 2^30 - 1 (1,073,741,823) bytes. VARCHAR TEXT
TEXT The maximum string length is 2^31 - 1 (2,147,483,647) bytes. VARCHAR TEXT
IMAGE Variable-length binary data from 0 to 2^31 - 1 (2,147,483,647) bytes. VARBINARY BYTEA
Spatial (geography and geometry) GEOGRAPHY N/A VARCHAR Not supported
GEOMETRY N/A VARCHAR Not supported
XML XML ( [ CONTENT | DOCUMENT ] xml_schema_collection ) N/A VARCHAR XML
Other types UNIQUEIDENTIFIER N/A VARCHAR CHARACTER(36)
SQL_VARIANT N/A Not supported Not supported
HIERARCHYID N/A Not supported Not supported
SYSNAME N/A VARCHAR CHARACTER VARYING(128)

Data migration from a self-managed TiDB database

The following table lists the data type mappings between a self-managed TiDB database and a MySQL database.
Data type of TiDB Data type of MySQL
BIGINT BIGINT
BINARY BINARY
BIT BIT
BOOL\ BOOLEAN TINYINT
CHAR CHAR
DATE DATE
DATETIME DATETIME
DECIMAL DECIMAL
DOUBLE DOUBLE
ENUM ENUM
FLOAT FLOAT
INT INT
INTEGER INTEGER
JSON JSON

MEDIUMBLOB/LONGBLOB

TINYBLOB/BLOB/

MEDIUMBLOB/LONGBLOB

TINYBLOB/BLOB/
MEDIUMINT MEDIUMINT
SET SET
SMALLINT SMALLINT
TEXT/LONGTEXT TEXT/LONGTEXT
TIME TIME
TIMESTAMP TIMESTAMP
TINYINT TINYINT
VARBINARY VARBINARY
VARCHAR VARCHAR
YEAR YEAR

Data migration from a Db2 for LUW database

The following table lists the data type mappings between a Db2 for LUW database and a MySQL database.
Note If the value range of the data to be migrated from the source instance exceeds the range supported by DTS, the accuracy of the data migrated to the destination instance will decrease.
Type Data type of Db2 for LUW Value range Data type of MySQL
Integer SMALLINT -32,768~+32,767 SMALLINT
INTEGER -2,147,483,648~+2,147,483,647 INT
BIGINT -9,223,372,036,854,775,808~ +9,223,372,036,854,775,807 BIGINT
Decimal DECIMAL(precision-integer, scale-integer) p<=38 DECIMAL
FLOAT(integer) The value range is 1 to 53. If the integer is between 1 and 24 inclusive, the format is single precision floating-point. If the integer is between 25 and 53 inclusive, the format is double precision floating-point. FLOAT
DECFLOAT(precision-integer) N/A DECIMAL(65,10)
Date and time DATE 0001-01-01~9999-12-31 DATE
TIME 00:00:00~24:00:00 TIME
TIMESTAMP(integer) 0001-01-01-00.00.00.000000000000~9999-12-31-24.00.00.000000000000;0<=p<= 12 DATETIME
String CHARACTER(integer) 254 CHAR | VARCHAR
VARCHAR(integer) 32,672 VARCHAR
CHARACTER(integer) FOR BIT DATA 254 BLOB
CLOB 2,147,483,647 LONGTEXT
GRAPHIC(integer) 127 CHAR(length*4)
VARGRAPHIC(integer) 16,336 CHAR(length*4)
DBCLOB(integer) 1,073,741,823 VARCHAR | LONGTEXT
BLOB 2,147,483,647 LONGBLOB
Other types XML 2,147,483,647 VARCHAR | LONGTEXT

Data migration from a Db2 for i database

The following table lists the data type mappings between a Db2 for i database and a MySQL database.
Note If the value range of the data to be migrated from the source instance exceeds the range supported by DTS, the accuracy of the data migrated to the destination instance will decrease.
Type Data type of Db2 for i Value range Data type of MySQL
Integer SMALLINT -32,768~+32,767 SMALLINT
INTEGER -2,147,483,648~+2,147,483,647 INT
BIGINT -9,223,372,036,854,775,808~ +9,223,372,036,854,775,807 BIGINT
Decimal DECIMAL(precision-integer, scale-integer) p<=63 DECIMAL
NUMERIC N/A DECIMAL
FLOAT(integer) N/A FLOAT
DECFLOAT(precision-integer) N/A DECIMAL(65,10)
Date and time DATE 0001-01-0~9999-12-31 DATE
TIME 00:00:00~24:00:00 TIME
TIMESTAMP(integer) 0001-01-01-00.00.00.000000000000 to 9999-12-31-24.00.00.000000000000 (0 <= p <= 12) DATETIME
String CHAR(integer) 32,765 CHAR | VARCHAR
VARCHAR(integer) 32,739 VARCHAR
CHAR(integer) FOR BIT DATA N/A BLOB
CLOB 2,147,483,647 LONGTEXT
GRAPHIC(integer) 16,382 CHAR
VARGRAPHIC(integer) 16,369 VARCHAR
DBCLOB(integer) 1,073,741,823 LONGTEXT
BINARY 32,765 BINARY
VARBIN 32,739 VARBINARY
BLOB 2,147,483,647 LONGBLOB
Other types DATALINK N/A VARCHAR | LONGTEXT
ROWID 40 VARCHAR | LONGTEXT
XML 2,147,483,647 VARCHAR | LONGTEXT