All Products
Search
Document Center

Data Transmission Service:Data type mappings between heterogeneous databases

最終更新日:Nov 01, 2024

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 describes 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 describes the data type mappings between a source MySQL database and a heterogeneous destination database. The source database can be a PolarDB for MySQL cluster, an ApsaraDB RDS for MySQL instance, or a self-managed MySQL database. The destination database 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 database exceeds the range supported by DTS, the accuracy of the data migrated to the destination database decreases.

Type

Data type in the source database

Value range

Data type in AnalyticDB for MySQL

Data type in AnalyticDB for PostgreSQL

Integer

BIT[(M)]

1 ~ 64

VARCHAR

BIT[(M)]

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[(M[,D])]

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.

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 Coordinated Universal Time (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 WITHOUT TIME ZONE

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

Destination database: a DataHub project, an ApsaraMQ for Kafka instance, or a self-managed Kafka cluster

Type

Data type in the source database

Value range

Data type in DataHub

Data type in an ApsaraMQ for Kafka instance or a self-managed Kafka cluster

Integer

BIT[(M)]

1 ~ 64

BOOLEAN | STRING

Consistent with the data types in MySQL or PolarDB for MySQL

TINYINT[(M)]

-128 ~ 127

BIGINT

TINYINT[(M)] [UNSIGNED]

0 ~ 255

BIGINT

SMALLINT[(M)]

-32768 ~ 32767

BIGINT

SMALLINT[(M)] [UNSIGNED]

0 ~ 65535

BIGINT

MEDIUMINT[(M)]

-8388608 ~ 8388607

BIGINT

MEDIUMINT[(M)] [UNSIGNED]

0 ~ 16777215

BIGINT

INT[(M)]

-2147483648 ~ 2147483647

BIGINT

INT[(M)] [UNSIGNED]

0 ~ 4294967295

BIGINT

BIGINT[(M)]

-9223372036854775808 ~ 9223372036854775807

BIGINT

BIGINT[(M)] [UNSIGNED]

0 ~ 18446744073709551615

BIGINT

Decimal

DECIMAL[(M[,D])]

M: 0 to 65

D: 0 to 30

DECIMAL

FLOAT(p)

1.175494351E-38 ~ 3.402823466E+38

DOUBLE

DOUBLE[(M,D)]

2.2250738585072014E-308 ~ 1.7976931348623157E+308

DOUBLE

Date and time

DATE

1000-01-01~9999-12-31

Note

The format is YYYY-MM-DD.

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 0000

STRING

String

CHAR[(M)]

0 to 255 characters

STRING

VARCHAR(M)

0 to 65,535 characters

STRING

BINARY[(M)]

0 to 255 bytes

STRING

VARBINARY(M)

0 to 65,535 bytes

STRING

TINYBLOB

255 (2^8 - 1) bytes

STRING

TINYTEXT

255 (2^8 - 1) characters

STRING

BLOB

65,535 (2^16 - 1) bytes

STRING

TEXT

65,535 (2^16 - 1) characters

STRING

MEDIUMBLOB

16,777,215 (2^24 - 1) bytes

STRING

MEDIUMTEXT

16,777,215 (2^24 - 1) characters

STRING

LONGBLOB

4,294,967,295 or 4 GB (2^32 - 1) bytes

STRING

LONGTEXT

4,294,967,295 or 4 GB (2^32 - 1) characters

STRING

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

Spatial

GEOMETRY

Geometry values of any type

STRING

POINT

N/A

STRING

LINESTRING

N/A

STRING

POLYGON

N/A

STRING

MULTIPOINT

N/A

STRING

MULTILINESTRING

N/A

STRING

MULTIPOLYGON

N/A

STRING

GEOMETRYCOLLECTION

A collection of geometry values of any type

STRING

JSON

JSON

N/A

STRING

Destination database: a MaxCompute project, an Elasticsearch cluster, or a ClickHouse cluster

Type

Data type in the source database

Value range

MaxCompute

Elasticsearch

ClickHouse

Integer

BIT[(M)]

1 ~ 64

BOOLEAN | STRING

BOOLEAN | LONG

Note

If the data is only one byte long, we recommend that you use the BOOLEAN data type in Elasticsearch.

UInt8

TINYINT[(M)]

-128 ~ 127

BIGINT

SHORT

Int8

TINYINT[(M)] [UNSIGNED]

0 ~ 255

BIGINT

INTEGER

UInt8

SMALLINT[(M)]

-32768 ~ 32767

BIGINT

SHORT

Int16

SMALLINT[(M)] [UNSIGNED]

0 ~ 65535

BIGINT

INTEGER

UInt16

MEDIUMINT[(M)]

-8388608 ~ 8388607

BIGINT

INTEGER

Int32

MEDIUMINT[(M)] [UNSIGNED]

0 ~ 16777215

BIGINT

INTEGER

Int32

INT[(M)]

-2147483648 ~ 2147483647

BIGINT

INTEGER

Int32

INT[(M)] [UNSIGNED]

0 ~ 4294967295

BIGINT

LONG

UInt32

BIGINT[(M)]

-9223372036854775808 ~ 9223372036854775807

BIGINT

LONG

Int64

BIGINT[(M)] [UNSIGNED]

0 ~ 18446744073709551615

BIGINT

LONG

UInt64

Decimal

DECIMAL[(M[,D])]

M: 0 to 65

D: 0 to 30

DOUBLE

DOUBLE

Note

If the DECIMAL value contains a decimal point, we recommend that you use the TEXT data type in Elasticsearch to ensure data consistency.

DECIMAL

FLOAT(p)

1.175494351E-38 ~ 3.402823466E+38

DOUBLE

FLOAT

Float32

DOUBLE[(M,D)]

2.2250738585072014E-308 ~ 1.7976931348623157E+308

DOUBLE

DOUBLE

Float64

Date and time

DATE

1000-01-01~9999-12-31

Note

The format is YYYY-MM-DD.

DATETIME

DATE

Note

The format is YYYY-MM-DD. For more information, see format.

DATE32

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

DATE

Note

The DATE data format is yyyy-MM-dd'T'HH:mm:ss, in UTC. If the DATE data is accurate to microseconds, the data format is yyyy-MM-dd'T'HH:mm:ss.S. For more information, see format.

DATETIME64

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.

DATETIME

DATE

Note

The DATE data format is yyyy-MM-dd'T'HH:mm:ss, in UTC. If the DATE data is accurate to microseconds, the data format is yyyy-MM-dd'T'HH:mm:ss.S. For more information, see format.

DATETIME

Note

The DATETIME data does not contain information about the time zone.

TIME[(fsp)]

-838:59:59.000000 ~ 838:59:59.000000

Note

The format is hh:mm:ss[.fraction], in UTC.

STRING

DATE

Note

The format is YYYY-MM-DD. For more information, see format.

STRING

YEAR[(4)]

1901 to 2155, or 0000

STRING

DATE

Note

The DATE format is yyyy, in UTC. For more information, see format.

Int16

String

CHAR[(M)]

0 to 255 characters

STRING

TEXT

STRING

VARCHAR(M)

0 to 65,535 characters

STRING

TEXT

STRING

BINARY[(M)]

0 to 255 bytes

STRING

BINARY

STRING

VARBINARY(M)

0 to 65,535 bytes

STRING

BINARY

STRING

TINYBLOB

255 (2^8 - 1) bytes

STRING

BINARY

STRING

TINYTEXT

255 (2^8 - 1) characters

STRING

TEXT

STRING

BLOB

65,535 (2^16 - 1) bytes

STRING

BINARY

STRING

TEXT

65,535 (2^16 - 1) characters

STRING

TEXT

STRING

MEDIUMBLOB

16,777,215 (2^24 - 1) bytes

STRING

BINARY

STRING

MEDIUMTEXT

16,777,215 (2^24 - 1) characters

STRING

TEXT

STRING

LONGBLOB

4,294,967,295 or 4 GB (2^32 - 1) bytes

STRING

BINARY

STRING

LONGTEXT

4,294,967,295 or 4 GB (2^32 - 1) characters

STRING

TEXT

STRING

ENUM('value1','value2',...)

An ENUM column can have a maximum of 65,535 distinct elements.

STRING

KEYWORD

ENUM

SET('value1','value2',...)

A SET column can have a maximum of 64 distinct elements.

STRING

KEYWORD

STRING

Spatial

GEOMETRY

Geometry values of any type

STRING

GEO_SHAPE

STRING

POINT

N/A

STRING

GEO_POINT

STRING

LINESTRING

N/A

STRING

GEO_SHAPE

STRING

POLYGON

N/A

STRING

GEO_SHAPE

STRING

MULTIPOINT

N/A

STRING

GEO_SHAPE

Note

If the data is only one byte long, we recommend that you use the BOOLEAN data type in Elasticsearch.

STRING

MULTILINESTRING

N/A

STRING

GEO_SHAPE

STRING

MULTIPOLYGON

N/A

STRING

GEO_SHAPE

STRING

GEOMETRYCOLLECTION

A collection of geometry values of any type

STRING

GEO_SHAPE

STRING

JSON

JSON

N/A

STRING

OBJECT

Note

If the data is only one byte long, we recommend that you use the BOOLEAN data type in Elasticsearch.

STRING

Destination database: a Tablestore instance

Data type in the source database

Data type in Tablestore

INTEGER

INTEGER

INT

INTEGER

SMALLINT

INTEGER

TINYINT

INTEGER

MEDIUMINT

INTEGER

BIGINT

INTEGER

DECIMAL

DOUBLE

NUMERIC

DOUBLE

FLOAT

DOUBLE

DOUBLE

DOUBLE

BIT

BOOLEAN

DATE

STRING or INTEGER

Note

Default value: STRING.

TIMESTAMP

DATETIME

TIME

YEAR

CHAR

STRING

VARCHAR

STRING

BINARY

BINARY

VARBINARY

BINARY

TINYBLOB/BLOB/MEDIUMBLOB/LONGBLOB

BINARY

TINYTEXT/TEXT/MEDIUMTEXT/LONGTEXT

STRING

ENUM

STRING

SET

STRING

GEOMETRY

STRING

POINT

STRING

LINESTRING

STRING

POLYGON

STRING

MULTIPOINT

STRING

MULTILINESTRING

STRING

MULTIPOLYGON

STRING

GEOMETRYCOLLECTION

STRING

JSON

STRING

Destination database: a Lindorm instance

Data type in the source database

Data type in Lindorm

BOOLEAN

BOOLEAN

BIT

BOOLEAN

TINYINT

TINYINT

SMALLINT

SMALLINT

INTEGER

INTEGER

BIGINT

BIGINT

FLOAT

FLOAT

DOUBLE

DOUBLE

DECIMAL

DECIMAL

CHAR/VARCHAR/TEXT/TINYTEXT/MEDIUMTEXT/LONGTEXT

CHAR/VARCHAR

BINARY

BINARY

BLOB

VARBINARY

VARBINAY

VARBINARY

TIMESTAMP

TIMESTAMP

YEAR

INTEGER

DATE

VARCHAR

DATETIME

VARCHAR

Important
  • We recommend that you map the DATETIME data in the source database to data of the VARCHAR type in the destination database.

  • If you want to map the DATETIME data to data of the TIMESTAMP type, data inconsistency may occur due to the difference in time zones. We recommend that you use the extract, transform, and load (ETL) feature when you configure the data synchronization task. This ensures data consistency.

TIME

VARCHAR

JSON

JSON

Migrate data from an Oracle database

The following table describes the data type mappings between a source self-managed Oracle database and a heterogeneous destination database. The destination database 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 database exceeds the range supported by DTS, the accuracy of the data migrated to the destination database decreases.

Type

Data type in Oracle

Value range

Data type in MySQL, PolarDB for MySQL, and PolarDB-X

Data type in ApsaraDB RDS for PPAS

Data type in AnalyticDB for MySQL

Data type in AnalyticDB for PostgreSQL

Data type in PolarDB for PostgreSQL (Compatible with Oracle)

Numeric

NUMBER(p,s)

1 to 22 bytes

The argument p indicates the precision. Valid values: 1 to 38.

The argument s indicates 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 the MAX_STRING_SIZE parameter is set to EXTENDED, the maximum size is 32,767 bytes.

If the MAX_STRING_SIZE parameter is set to STANDARD, the maximum size is 4,000 bytes.

VARCHAR(n)

VARCHAR2[(n)]

VARCHAR

VARCHAR

VARCHAR2(size [BYTE | CHAR])

NVARCHAR2(size)

If the MAX_STRING_SIZE parameter is set to EXTENDED, the maximum size is 32,767 bytes.

If the MAX_STRING_SIZE parameter 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 database: 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 TIMESTAMP 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 database: 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 database: 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 describes the data type mappings between a source SQL Server database and a heterogeneous destination database. The source database can be a self-managed SQL Server database or an ApsaraDB RDS for SQL Server instance. The destination database can be an AnalyticDB for MySQL cluster, an AnalyticDB for PostgreSQL instance, a PostgreSQL database, or a MySQL database.

Note

If the value range of the data to be migrated from the source database exceeds the range supported by DTS, the accuracy of the data migrated to the destination database decreases.

Type

Data type in SQL Server

Value range

Data type in AnalyticDB for MySQL

Data type in AnalyticDB for PostgreSQL

Data type in PostgreSQL

Data type in MySQL

Integer

BIT

1, 0, and NULL

BOOLEAN

BIT(1)

BIT

TINYINT

0 to 255

TINYINT

SMALLINT

TINYINT

SMALLINT

-32768 (-2^15) to 32767 (2^15 - 1)

SMALLINT

SMALLINT

SMALLINT

INT

-2147483648 (-2^31) to 2147483647 (2^31 - 1)

INTEGER

INTEGER

INT

BIGINT

-9223372036854775808 (-2^63) to 9223372036854775807 (2^63 - 1)

BIGINT

BIGINT

BIGINT

Decimal

NUMERIC[ (p[ ,s] )]

-10^38 + 1 to 10^38 - 1 (1 <= p <= 38)

DECIMAL

DECIMAL

DECIMAL[ (p[ ,s] )]

DECIMAL[ (p[ ,s] )]

-10^38 + 1 to 10^38 - 1 (1 <= p <= 38)

DECIMAL

DECIMAL

DECIMAL[ (p[ ,s] )]

FLOAT

-1.79E + 308 to -2.23E - 308, 0, and 2.23E - 308 to 1.79E + 308

DOUBLE

DOUBLE PRECISION

DOUBLE

REAL

-3.40E + 38 to -1.18E - 38, 0, and 1.18E - 38 to 3.40E + 38

FLOAT

REAL

DOUBLE

Monetary

MONEY

-922337203685477.5808 to 922337203685477.5807

DECIMAL(19, 4)

DECIMAL(19, 4)

DECIMAL

SMALLMONEY

-214748.3648 to 214748.3647

DECIMAL(10, 4)

DECIMAL(10, 4)

DECIMAL

Date and time

DATE

0001-01-01 to 9999-12-31

DATE

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

DATETIME

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

DATETIME

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

DATETIME

SMALLDATETIME

The time is based on a 24-hour day, with seconds always zero (:00) and without fractional seconds.

DATETIME

TIMESTAMP WITHOUT TIME ZONE

DATETIME

TIME [ (fractional second scale) ]

00:00:00.0000000 to 23:59:59.9999999

TIME

TIME(7) WITH TIME ZONE

TIME

String

BINARY [ ( n ) ]

Valid values of n: 1 to 8000.

VARBINARY

BYTEA

BINARY

VARBINARY [ ( n | max) ]

Valid values of n: 1 to 8000. max indicates that the maximum storage size is 2^31 - 1 bytes.

VARBINARY

BYTEA

VARBINARY

CHAR [ ( n ) ]

Valid values of n: 1 to 8000. The storage size is n bytes.

VARCHAR

CHARACTER

  • If the length is greater than 65535, the data type is TEXT.

  • If the length is greater than 255 and smaller than or equal to 65535, the data type is VARCHAR.

  • If the length is smaller than or equal to 255, the data type is CHAR.

VARCHAR [ ( n | max ) ]

Valid values of n: 1 to 8000. max indicates that the maximum storage size is 2^31 - 1 bytes (2 GB).

VARCHAR

CHARACTER

  • If the length is greater than 65535, the data type is TEXT.

  • If the length is smaller than or equal to 65535, the data type is VARCHAR.

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.

VARCHAR

CHARACTER VARYING

VARCHAR(200)

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).

VARCHAR

CHARACTER VARYING

  • If the length is greater than 65535, the data type is TEXT.

  • If the length is smaller than or equal to 65535, the data type is VARCHAR.

NTEXT

Variable-length Unicode data with a maximum string length of 1,073,741,823 (2^30 - 1) bytes.

VARCHAR

TEXT

TEXT

TEXT

The maximum string length is 2,147,483,647 (2^31 - 1) bytes.

VARCHAR

TEXT

TEXT

IMAGE

Variable-length binary data from 0 to 2,147,483,647 (2^31 - 1) bytes.

VARBINARY

BYTEA

BLOB

Spatial (geography and geometry)

GEOGRAPHY

N/A

VARCHAR

Not supported

BLOB

GEOMETRY

N/A

VARCHAR

Not supported

BLOB

XML

XML ( [ CONTENT | DOCUMENT ] xml_schema_collection )

N/A

VARCHAR

XML

TEXT

Other types

UNIQUEIDENTIFIER

N/A

VARCHAR

CHARACTER(36)

VARCHAR(200)

SQL_VARIANT

N/A

Not supported

Not supported

VARCHAR(200)

HIERARCHYID

N/A

Not supported

Not supported

VARCHAR(200)

SYSNAME

N/A

VARCHAR

CHARACTER VARYING(128)

VARCHAR(200)

Migrate data from a self-managed TiDB database

The following table describes the data type mappings between a source self-managed TiDB database and a heterogeneous destination database. The destination database can be a MySQL database or an AnalyticDB for MySQL V3.0 cluster.

Data type in TiDB

Data type in MySQL

BIGINT

BIGINT

BIGINT UNSIGNED

DECIMAL(20,0)

BINARY

BINARY

BIT

BIT

BOOL\ BOOLEAN

TINYINT

CHAR

CHAR

DATE

DATE

DATETIME

DATETIME

DECIMAL

DECIMAL

DOUBLE

DOUBLE

ENUM

ENUM

FLOAT

FLOAT

INT

INT

INT UNSIGNED

BIGINT

INTEGER

INTEGER

JSON

JSON

MEDIUMBLOB/LONGBLOB

TINYBLOB/BLOB/

MEDIUMBLOB/LONGBLOB

TINYBLOB/BLOB/

MEDIUMINT

MEDIUMINT

SET

SET

SMALLINT

SMALLINT

SMALLINT UNSIGNED

INT

TEXT/LONGTEXT

TEXT/LONGTEXT

TIME

TIME

TIMESTAMP

TIMESTAMP

TINYINT

TINYINT

TINYINT UNSIGNED

SMALLINT

VARBINARY

VARBINARY

VARCHAR

VARCHAR

YEAR

YEAR

Migrate data from a Db2 for LUW database

The following table describes the data type mappings between a source Db2 for LUW database and a destination MySQL database.

Note

If the value range of the data to be migrated from the source database exceeds the range supported by DTS, the accuracy of the data migrated to the destination database decreases.

Type

Data type in Db2 for LUW

Value range

Data type in 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 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)

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

Migrate data from a Db2 for i database

The following table describes the data type mappings between a source Db2 for i database and a destination MySQL database.

Note

If the value range of the data to be migrated from the source database exceeds the range supported by DTS, the accuracy of the data migrated to the destination database decreases.

Type

Data type in Db2 for i

Value range

Data type in 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

Migrate data from a Teradata database

The following table describes the data type mappings between a source Teradata database and a destination AnalyticDB for PostgreSQL instance.

Data type in Teradata

Data type in AnalyticDB for PostgreSQL

BYTEINT

SMALLINT

SMALLINT

SMALLINT

BIGINT

BIGINT

INTEGER

INTEGER

DATE

DATE

JSON

JSON

XML

XML

CLOB

text

Float

real

CHAR

CHAR

VARCHAR

VARCHAR

Timestamp

Timestamp

TIME

TIME

Timestamp With Time Zone

Timestamp With Time Zone

Time With Time Zone

Time With Time Zone

Decimal

Decimal

Number

numeric

BYTE

bytea

VARBYTE

bytea

BLOB

bytea

PERIOD

varchar(100)

INTERVAL

varchar(100)

Data type in Teradata

Data type in AnalyticDB for PostgreSQL

SMALLINT

SMALLINT

INTEGER

INT

BIGINT

BIGINT

DECIMAL(precision-integer, scale-integer)

DECIMAL

NUMERIC

DECIMAL

FLOAT(integer)

FLOAT

DECFLOAT(precision-integer)

DECIMAL(65,10)

DATE

DATE

TIME

TIME

TIMESTAMP(integer)

DATETIME

CHAR(integer)

CHAR | VARCHAR

VARCHAR(integer)

VARCHAR

CHAR(integer) FOR BIT DATA

BLOB

CLOB

LONGTEXT

GRAPHIC (integer)

CHAR

VARGRAPHIC (integer)

VARCHAR

DBCLOB (integer)

LONGTEXT

BINARY

BINARY

VARBIN

VARBINARY

BLOB

LONGBLOB

DATALINK

VARCHAR | LONGTEXT

ROWID

VARCHAR | LONGTEXT

XML

VARCHAR | LONGTEXT