All Products
Search
Document Center

Data Transmission Service:Data type mappings between heterogeneous databases

Last Updated:Aug 08, 2023

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 during schema migration. 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:
Important If the data type of the time field in the source database is TIMESTAMP WITH TIME ZONE and the data type of the time field in the destination database is a heterogeneous data type, such as DATETIME, the time zone information in the time field will be lost.

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

The following table lists the data type mappings between MySQL and heterogeneous 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 V2.0 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 decreases.
TypeData type of the source instanceValue rangeData type of AnalyticDB for MySQLData type of AnalyticDB for PostgreSQL
IntegerBIT[(M)]1 ~ 64VARCHARBIT
TINYINT[(M)]-128 ~ 127TINYINTSMALLINT
TINYINT[(M)] [UNSIGNED]0 ~ 255SMALLINTSMALLINT
SMALLINT[(M)]-32768 ~ 32767SMALLINTSMALLINT
SMALLINT[(M)] [UNSIGNED]0 ~ 65535INTINTEGER
MEDIUMINT[(M)]-8388608 ~ 8388607INTINTEGER
MEDIUMINT[(M)] [UNSIGNED]0 ~ 16777215INTINTEGER
INT[(M)]-2147483648 ~ 2147483647INTINTEGER
INT[(M)] [UNSIGNED]0 ~ 4294967295BIGINTBIGINT
BIGINT[(M)]-9223372036854775808 ~ 9223372036854775807BIGINTBIGINT
BIGINT[(M)] [UNSIGNED]0 ~ 18446744073709551615.DECIMAL(20,0)NUMERIC(20)
DecimalDECIMAL[(M[,D])]M: 0 to 65
D: 0 to 30
DECIMAL[(M[,D])]DECIMAL
FLOAT(p)1.175494351E-38 ~ 3.402823466E+38FLOATREAL
DOUBLE[(M,D)]2.2250738585072014E-308 ~ 1.7976931348623157E+308DOUBLEDOUBLE PRECISION
Date and timeDATE1000-01-01~9999-12-31
Note The format is YYYY-MM-DD, in UTC.
DATEDATE
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.
DATETIMETIMESTAMP
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.
TIMESTAMPTIMESTAMP WITH TIME ZONE
TIME[(fsp)]-838:59:59.000000 ~ 838:59:59.000000
Note The format is hh:mm:ss[.fraction], in UTC.
TIMETIME
YEAR[(4)]1901 to 2155, or 0000INTINTEGER
StringCHAR[(M)]0 to 255 charactersVARCHARCHAR
VARCHAR(M)0 to 65,535 charactersVARCHARVARCHAR
BINARY[(M)]0 to 255 bytesVARBINARYBYTEA
VARBINARY(M)0 to 65,535 bytesVARBINARYBYTEA
TINYBLOB255 (2^8 - 1) bytesVARBINARYBYTEA
TINYTEXT255 (2^8 - 1) charactersVARCHARTEXT
BLOB65,535 (2^16 - 1) bytesVARBINARYBYTEA
TEXT65,535 (2^16 - 1) charactersVARCHARTEXT
MEDIUMBLOB16,777,215 (2^24 - 1) bytesVARBINARYBYTEA
MEDIUMTEXT16,777,215 (2^24 - 1) charactersVARCHARTEXT
LONGBLOB4,294,967,295 or 4 GB (2^32 - 1) bytesVARBINARYBYTEA
LONGTEXT4,294,967,295 or 4 GB (2^32 - 1) charactersVARCHARTEXT
ENUM('value1','value2',...)An ENUM column can have a maximum of 65,535 distinct elements.VARCHARVARCHAR(128)
SET('value1','value2',...)A SET column can have a maximum of 64 distinct elements.VARCHARVARCHAR(128)
SpatialGEOMETRYGeometry values of any typeVARBINARYPOLYGON
POINTNoneVARBINARYPOINT
LINESTRINGNoneVARBINARYPATH
POLYGONNoneVARBINARYPOLYGON
MULTIPOINTNoneVARBINARYPOLYGON
MULTILINESTRINGNoneVARBINARYPATH
MULTIPOLYGONNoneVARBINARYPOLYGON
GEOMETRYCOLLECTIONA collection of geometry values of any typeVARBINARYPOLYGON
JSONJSONNoneJSONJSON
Table 3. Destination instance: a DataHub project, a Message Queue for Apache Kafka instance, or a self-managed Kafka cluster
CategoryData type of the source instanceValue rangeData type of DataHubData type of a Message Queue for Apache Kafka instance or a self-managed Kafka cluster
IntegerBIT[(M)]1 ~ 64BOOLEAN | STRINGConsistent with the data types of MySQL or PolarDB for MySQL
TINYINT[(M)]-128 ~ 127BIGINT
TINYINT[(M)] [UNSIGNED]0 ~ 255BIGINT
SMALLINT[(M)]-32768 ~ 32767BIGINT
SMALLINT[(M)] [UNSIGNED]0 ~ 65535BIGINT
MEDIUMINT[(M)]-8388608 ~ 8388607BIGINT
MEDIUMINT[(M)] [UNSIGNED]0 ~ 16777215BIGINT
INT[(M)]-2147483648 ~ 2147483647BIGINT
INT[(M)] [UNSIGNED]0 ~ 4294967295BIGINT
BIGINT[(M)]-9223372036854775808 ~ 9223372036854775807BIGINT
BIGINT[(M)] [UNSIGNED]0 ~ 18446744073709551615BIGINT
DecimalDECIMAL[(M[,D])]M: 0 to 65
D: 0 to 30
DECIMAL
FLOAT(p)1.175494351E-38 ~ 3.402823466E+38DOUBLE
DOUBLE[(M,D)]2.2250738585072014E-308 ~ 1.7976931348623157E+308DOUBLE
Date and timeDATE1000-01-01~9999-12-31
Note The format is YYYY-MM-DD, in UTC.
TIMESTAMP
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.
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
TIME[(fsp)]-838:59:59.000000 ~ 838:59:59.000000
Note The format is hh:mm:ss[.fraction], in UTC.
STRING
YEAR[(4)]1901 to 2155, or 0000STRING
StringCHAR[(M)]0 to 255 charactersSTRING
VARCHAR(M)0 to 65,535 charactersSTRING
BINARY[(M)]0 to 255 bytesSTRING
VARBINARY(M)0 to 65,535 bytesSTRING
TINYBLOB255 (2^8 - 1) bytesSTRING
TINYTEXT255 (2^8 - 1) charactersSTRING
BLOB65,535 (2^16 - 1) bytesSTRING
TEXT65,535 (2^16 - 1) charactersSTRING
MEDIUMBLOB16,777,215 (2^24 - 1) bytesSTRING
MEDIUMTEXT16,777,215 (2^24 - 1) charactersSTRING
LONGBLOB4,294,967,295 or 4 GB (2^32 - 1) bytesSTRING
LONGTEXT4,294,967,295 or 4 GB (2^32 - 1) charactersSTRING
ENUM('value1','value2',...)An ENUM column can have a maximum of 65,535 distinct elements.STRING
SET('value1','value2',...)A SET column can have a maximum of 64 distinct elements.STRING
SpatialGEOMETRYGeometry values of any typeSTRING
POINTN/ASTRING
LINESTRINGN/ASTRING
POLYGONN/ASTRING
MULTIPOINTN/ASTRING
MULTILINESTRINGN/ASTRING
MULTIPOLYGONN/ASTRING
GEOMETRYCOLLECTIONA collection of geometry values of any typeSTRING
JSONJSONN/ASTRING
Table 6. Destination instance: a MaxCompute project or an Elasticsearch cluster
CategoryData type of the source instanceValue rangeMaxComputeElasticsearch
IntegerBIT[(M)]1 ~ 64BOOLEAN | STRINGBOOLEAN | LONG
Note If the data is only one byte long, we recommend that you use the BOOLEAN data type in Elasticsearch.
TINYINT[(M)]-128 ~ 127BIGINTSHORT
TINYINT[(M)] [UNSIGNED]0 ~ 255BIGINTINTEGER
SMALLINT[(M)]-32768 ~ 32767BIGINTSHORT
SMALLINT[(M)] [UNSIGNED]0 ~ 65535BIGINTINTEGER
MEDIUMINT[(M)]-8388608 ~ 8388607BIGINTINTEGER
MEDIUMINT[(M)] [UNSIGNED]0 ~ 16777215BIGINTINTEGER
INT[(M)]-2147483648 ~ 2147483647BIGINTINTEGER
INT[(M)] [UNSIGNED]0 ~ 4294967295BIGINTLONG
BIGINT[(M)]-9223372036854775808 ~ 9223372036854775807BIGINTLONG
BIGINT[(M)] [UNSIGNED]0 ~ 18446744073709551615BIGINTLONG
DecimalDECIMAL[(M[,D])]M: 0 to 65
D: 0 to 30
DOUBLEDOUBLE
Note If the DECIMAL value contains a decimal point, we recommend that you use the TEXT data type in Elasticsearch to ensure data consistency.
FLOAT(p)1.175494351E-38 ~ 3.402823466E+38DOUBLEFLOAT
DOUBLE[(M,D)]2.2250738585072014E-308 ~ 1.7976931348623157E+308DOUBLEDOUBLE
Date and timeDATE1000-01-01~9999-12-31
Note The format is YYYY-MM-DD, in UTC.
DATETIMEDATE
Note The format is YYYY-MM-DD, in UTC. For more information, see Date format mappings.
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.
DATETIMEDATE
Note The DATE format is yyyy-MM-dd'T'HH:mm:ss, in UTC. If DATE is accurate to microseconds, its format is yyyy-MM-dd'T'HH:mm:ss.S. For more information, see Date format mappings.
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.
DATETIMEDATE
Note The DATE format is yyyy-MM-dd'T'HH:mm:ss, in UTC. If DATE is accurate to microseconds, its format is yyyy-MM-dd'T'HH:mm:ss.S. For more information, see Date format mappings.
TIME[(fsp)]-838:59:59.000000 ~ 838:59:59.000000
Note The format is hh:mm:ss[.fraction], in UTC.
STRINGDATE
Note The DATE format is YYYY-MM-DD, in UTC. For more information, see Date format mappings.
YEAR[(4)]1901 to 2155, or 0000STRINGDATE
Note The DATE format is yyyy, in UTC. For more information, see Date format mappings.
StringCHAR[(M)]0 to 255 charactersSTRINGTEXT
VARCHAR(M)0 to 65,535 charactersSTRINGTEXT
BINARY[(M)]0 to 255 bytesSTRINGBINARY
VARBINARY(M)0 to 65,535 bytesSTRINGBINARY
TINYBLOB255 (2^8 - 1) bytesSTRINGBINARY
TINYTEXT255 (2^8 - 1) charactersSTRINGTEXT
BLOB65,535 (2^16 - 1) bytesSTRINGBINARY
TEXT65,535 (2^16 - 1) charactersSTRINGTEXT
MEDIUMBLOB16,777,215 (2^24 - 1) bytesSTRINGBINARY
MEDIUMTEXT16,777,215 (2^24 - 1) charactersSTRINGTEXT
LONGBLOB4,294,967,295 or 4 GB (2^32 - 1) bytesSTRINGBINARY
LONGTEXT4,294,967,295 or 4 GB (2^32 - 1) charactersSTRINGTEXT
ENUM('value1','value2',...)An ENUM column can have a maximum of 65,535 distinct elements.STRINGKEYWORD
SET('value1','value2',...)A SET column can have a maximum of 64 distinct elements.STRINGKEYWORD
SpatialGEOMETRYGeometry values of any typeSTRINGGEO_SHAPE
POINTN/ASTRINGGEO_POINT
LINESTRINGN/ASTRINGGEO_SHAPE
POLYGONN/ASTRINGGEO_SHAPE
MULTIPOINTN/ASTRINGGEO_SHAPE
Note If the data is only one byte long, we recommend that you use the BOOLEAN data type in Elasticsearch.
MULTILINESTRINGN/ASTRINGGEO_SHAPE
MULTIPOLYGONN/ASTRINGGEO_SHAPE
GEOMETRYCOLLECTIONA collection of geometry values of any typeSTRINGGEO_SHAPE
JSONJSONN/ASTRINGOBJECT
Note If the data is only one byte long, we recommend that you use the BOOLEAN data type in Elasticsearch.

Migrate data from an Oracle database

The following table describes the data type mappings between a self-managed Oracle database 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 for PostgreSQL (Compatible with Oracle) 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 decreases.
TypeData type of OracleValue rangeData type of MySQL, PolarDB for MySQL, or PolarDB-XData type of ApsaraDB RDS for PPASData type of AnalyticDB for MySQLData type of AnalyticDB for PostgreSQLData type of PolarDB for PostgreSQL (Compatible with Oracle)
NumericNUMBER(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 | BIGINTDECIMAL | TINYINT | SMALLINT | INTEGER | BIGINTNUMBER(p,s)
FLOAT(p)1 to 22 bytes
The variable p indicates a pointer. Valid values: 1 to 126 bits.
DOUBLEDOUBLE PRECISIONDOUBLEDOUBLE PRECISIONDOUBLE PRECISION
BINARY_FLOATA 32-bit floating-point number (4 bytes) DECIMAL(65,8)REALDOUBLEDOUBLE PRECISIONREAL
BINARY_DOUBLEA 64-bit floating-point number (8 bytes) DOUBLEDOUBLE PRECISIONDOUBLEDOUBLE PRECISIONDOUBLE PRECISION
Date and timeDATENoneDATETIMEDATEDATETIMETIMESTAMP(0)DATE
TIMESTAMP [(fractional_seconds_precision)]NoneDATETIME[(fractional_seconds_precision)]TIMESTAMP [(fractional_seconds_precision)]DATETIMETIMESTAMPTIMESTAMP [(fractional_seconds_precision)]
TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONENoneDATETIME[(fractional_seconds_precision)] TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONETIMESTAMPTIMESTAMP WITH TIME ZONETIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE
TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONENoneDATETIME[(fractional_seconds_precision)] TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONEDATETIMETIMESTAMP WITH TIME ZONETIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE
INTERVAL YEAR [(year_precision)] TO MONTHNoneNot supportedNot supportedVARCHARVARCHAR(32)INTERVAL
INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)]NoneNot supportedNot supportedVARCHARVARCHAR(32)INTERVAL
StringCHAR [(size [BYTE | CHAR])]2,000 bytes CHAR[(n)]CHAR[(n)]VARCHARCHARCHAR [(size [BYTE | CHAR])]
NCHAR[(size)]2,000 bytes NATIONAL CHAR[(n)]NCHAR[(n)]VARCHARVARCHARNCHAR[(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)]VARCHARVARCHARVARCHAR2(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)]VARCHARVARCHARNVARCHAR2(size)
LONGThe maximum size is 2 GB (2^31 - 1). LONGTEXTLONGVARCHARTEXTLONG
RAW(size)The maximum size is 32,767 bytes or 2,000 bytes. VARBINARY(2000)RAW(size)VARBINARYBYTEARAW(size)
LONG RAWThe maximum size is 2 GB. LONGBLOBLONG RAWVARBINARYBYTEALONG RAW
CLOBThe maximum size is (4 GB - 1) × DB_BLOCK_SIZE. LONGTEXTCLOBVARCHARTEXTCLOB
NCLOBThe maximum size is (4 GB - 1) × DB_BLOCK_SIZE. LONGTEXTNCLOBVARCHARTEXTCLOB
BLOBThe maximum size is (4 GB - 1) × DB_BLOCK_SIZE. LONGBLOBBLOBVARBINARYBYTEABLOB
BFILEThe maximum size is 4 GB. Not supportedNot supportedNot supportedNot supportedNot supported
JSONJSONThe maximum size is 32 MB. Not supportedNot supportedJSONJSONJSON
ROWIDROWID64 characters Not supportedNot supportedROWIDOIDVARCHAR
SpatialCustomization requiredNot 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 of 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.

Migrate data 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 decreases.
TypeData type of SQL ServerValue rangeData type of AnalyticDB for MySQLData type of AnalyticDB for PostgreSQL
IntegerBITAn INTEGER data type that can take a value of 1, 0, or NULLBOOLEANBIT(1)
TINYINT0 to 255TINYINTSMALLINT
SMALLINT-32768 (-2^15) to 32767 (2^15 - 1)SMALLINTSMALLINT
INT-2147483648 (-2^31) to 2147483647 (2^31 - 1)INTEGERINTEGER
BIGINT-9223372036854775808 (-2^63) to 9223372036854775807 (2^63 - 1)BIGINTBIGINT
DecimalNUMERIC[ (p[ ,s] )]-10^38 + 1 to 10^38 - 1 (1 <= p <= 38)DECIMALDECIMAL
DECIMAL[ (p[ ,s] )]-10^38 + 1 to 10^38 - 1 (1 <= p <= 38)DECIMALDECIMAL
FLOAT-1.79E + 308 to -2.23E - 308, 0, and 2.23E - 308 to 1.79E + 308DOUBLEDOUBLE PRECISION
REAL-3.40E + 38 to -1.18E - 38, 0, and 1.18E - 38 to 3.40E + 38FLOATREAL
MonetaryMONEY-922337203685477.5808 to 922337203685477.5807DECIMAL(19, 4)DECIMAL(19, 4)
SMALLMONEY-214748.3648 to 214748.3647DECIMAL(10, 4)DECIMAL(10, 4)
Date and timeDATE0001-01-01 to 9999-12-31DATEDATE
DATETIMEDate range: January 1, 1753 to December 31, 9999
Time range: 00:00:00 to 23:59:59.997
DATETIMETIMESTAMP(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
DATETIMETIMESTAMP(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
TIMESTAMPTIMESTAMP(7) WITH TIME ZONE
SMALLDATETIMEThe time is based on a 24-hour day, with seconds always zero (:00) and without fractional seconds.DATETIMETIMESTAMP WITHOUT TIME ZONE
TIME [ (fractional second scale) ]00:00:00.0000000 to 23:59:59.9999999TIMETIME(7) WITH TIME ZONE
StringBINARY [ ( n ) ]Valid values of n: 1 to 8000.VARBINARYBYTEA
VARBINARY [ ( n | max) ]Valid values of n: 1 to 8000. max indicates that the maximum storage size is 2^31 - 1 bytes.VARBINARYBYTEA
CHAR [ ( n ) ]Valid values of n: 1 to 8000. The storage size is n bytes.VARCHARCHARACTER
VARCHAR [ ( n | max ) ]Valid values of n: 1 to 8000. max indicates that the maximum storage size is 2^31 - 1 bytes (2 GB).VARCHARCHARACTER
NCHAR [ ( n ) ]n defines the string size in byte-pairs. Valid values of n: 1 to 4000. The storage size is two times n bytes.VARCHARCHARACTER VARYING
NVARCHAR [ ( n | max ) ]n defines the string size in byte-pairs. Valid values of n: 1 to 4000. max indicates that the maximum storage size is 2^30 - 1 characters (2 GB).VARCHARCHARACTER VARYING
NTEXTVariable-length Unicode data with a maximum string length of 1,073,741,823 (2^30 - 1) bytes.VARCHARTEXT
TEXTThe maximum string length is 2,147,483,647 (2^31 - 1) bytes.VARCHARTEXT
IMAGEVariable-length binary data from 0 to 2,147,483,647 (2^31 - 1) bytes. VARBINARYBYTEA
Spatial (geography and geometry)GEOGRAPHYNoneVARCHARNot supported
GEOMETRYNoneVARCHARNot supported
XMLXML ( [ CONTENT | DOCUMENT ] xml_schema_collection )NoneVARCHARXML
Other typesUNIQUEIDENTIFIERNoneVARCHARCHARACTER(36)
SQL_VARIANTNoneNot supportedNot supported
HIERARCHYIDNoneNot supportedNot supported
SYSNAMENoneVARCHARCHARACTER VARYING(128)

Migrate data 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 TiDBData type of MySQL
BIGINTBIGINT
BINARYBINARY
BITBIT
BOOL/BOOLEANTINYINT
CHARCHAR
DATEDATE
DATETIMEDATETIME
DECIMALDECIMAL
DOUBLEDOUBLE
ENUMENUM
FLOATFLOAT
INTINT
INTEGERINTEGER
JSONJSON

MEDIUMBLOB/LONGBLOB

TINYBLOB/BLOB/

MEDIUMBLOB/LONGBLOB

TINYBLOB/BLOB/

MEDIUMINTMEDIUMINT
SETSET
SMALLINTSMALLINT
TEXT/LONGTEXTTEXT/LONGTEXT
TIMETIME
TIMESTAMPTIMESTAMP
TINYINTTINYINT
VARBINARYVARBINARY
VARCHARVARCHAR
YEARYEAR

Migrate data 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 decreases.
TypeData type of Db2 for LUWValue rangeData type of MySQL
IntegerSMALLINT-32,768 to +32,767SMALLINT
INTEGER-2,147,483,648 to +2,147,483,647INT
BIGINT-9,223,372,036,854,775,808 to +9,223,372,036,854,775,807BIGINT
DecimalDECIMAL(precision-integer, scale-integer)p<=38DECIMAL
FLOAT(integer)The value range is 1 to 53. If the integer is in the range of 1 to 24, the format is single precision floating-point. If the integer is in the range of 25 to 53, the format is double precision floating-point.FLOAT
DECFLOAT(precision-integer)NoneDECIMAL(65,10)
Date and timeDATE0001-01-01 to 9999-12-31DATE
TIME00:00:00 to 24:00:00TIME
TIMESTAMP(integer)0001-01-01-00.00.00.000000000000 to 9999-12-31-24.00.00.000000000000 (0 <= p <= 12)DATETIME
StringCHARACTER(integer)254CHAR | VARCHAR
VARCHAR(integer)32,672VARCHAR
CHARACTER(integer) FOR BIT DATA254BLOB
CLOB2,147,483,647LONGTEXT
GRAPHIC (integer)127CHAR(length*4)
VARGRAPHIC (integer)16,336CHAR(length*4)
DBCLOB (integer)1,073,741,823VARCHAR | LONGTEXT
BLOB2,147,483,647LONGBLOB
Other typesXML2,147,483,647VARCHAR | LONGTEXT

Migrate data 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 decreases.
TypeData type of Db2 for iValue rangeData type of MySQL
IntegerSMALLINT-32,768 to +32,767SMALLINT
INTEGER-2,147,483,648 to +2,147,483,647INT
BIGINT-9,223,372,036,854,775,808 to +9,223,372,036,854,775,807BIGINT
DecimalDECIMAL(precision-integer, scale-integer)p<=63DECIMAL
NUMERICNoneDECIMAL
FLOAT(integer)NoneFLOAT
DECFLOAT(precision-integer)NoneDECIMAL(65,10)
Date and timeDATE0001-01-0 to 9999-12-31DATE
TIME00:00:00 to 24:00:00TIME
TIMESTAMP(integer)0001-01-01-00.00.00.000000000000 to 9999-12-31-24.00.00.000000000000 (0 <= p <= 12)DATETIME
StringCHAR(integer)32,765CHAR | VARCHAR
VARCHAR(integer)32,739VARCHAR
CHAR(integer) FOR BIT DATANoneBLOB
CLOB2,147,483,647LONGTEXT
GRAPHIC (integer)16,382CHAR
VARGRAPHIC (integer)16,369VARCHAR
DBCLOB (integer)1,073,741,823LONGTEXT
BINARY32,765BINARY
VARBIN32,739VARBINARY
BLOB2,147,483,647LONGBLOB
Other typesDATALINKNoneVARCHAR | LONGTEXT
ROWID40VARCHAR | LONGTEXT
XML2,147,483,647VARCHAR | LONGTEXT

Migrate data from a Teradata database

The following table lists the data type mappings between a Teradata database and an AnalyticDB for PostgreSQL instance.
Data type of TeradataData type of AnalyticDB for PostgreSQL
BYTEINTSMALLINT
SMALLINTSMALLINT
BIGINTBIGINT
INTEGERINTEGER
DATEDATE
JSONJSON
XMLXML
CLOBtext
Floatreal
CHARCHAR
VARCHARVARCHAR
TimestampTimestamp
TIMETIME
Timestamp With Time ZoneTimestamp With Time Zone
Time With Time ZoneTime With Time Zone
DecimalDecimal
Numbernumeric
BYTEbytea
VARBYTEbytea
BLOBbytea
PERIODvarchar(100)
INTERVALvarchar(100)
Data type of TeradataData type of AnalyticDB for PostgreSQL
SMALLINTSMALLINT
INTEGERINT
BIGINTBIGINT
DECIMAL(precision-integer, scale-integer)DECIMAL
NUMERICDECIMAL
FLOAT(integer)FLOAT
DECFLOAT(precision-integer)DECIMAL(65,10)
DATEDATE
TIMETIME
TIMESTAMP(integer)DATETIME
CHAR(integer)CHAR | VARCHAR
VARCHAR(integer)VARCHAR
CHAR(integer) FOR BIT DATABLOB
CLOBLONGTEXT
GRAPHIC (integer)CHAR
VARGRAPHIC (integer)VARCHAR
DBCLOB (integer)LONGTEXT
BINARYBINARY
VARBINVARBINARY
BLOBLONGBLOB
DATALINKVARCHAR | LONGTEXT
ROWIDVARCHAR | LONGTEXT
XMLVARCHAR | LONGTEXT