Heterogeneous databases have different data types. When Data Transmission Service (DTS) synchronizes 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 view and evaluate the impact of data synchronization on your business.
Overview
You can view the data type mappings between heterogeneous databases based on the following synchronization scenarios:Synchronize data from a MySQL database
The following table lists the data type mappings between MySQL and AnalyticDB databases. The source instance can be a self-managed MySQL database, an ApsaraDB RDS for MySQL instance, or a PolarDB for MySQL cluster. 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 synchronized from the source instance exceeds the range supported by DTS, the accuracy of the data written to the destination instance will decrease.
Category | Data type of the source instance | Value range | Data type of AnalyticDB for MySQL | Data type of AnalyticDB for PostgreSQL |
---|---|---|---|---|
Integer | BIT[(M)] | 1 ~ 64 | VARCHAR | BIT |
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 |
Category | Data type of the source instance | Value range | Data type of DataHub | Data type of a Message Queue for Apache Kafka instance or a self-managed Kafka cluster |
---|---|---|---|---|
Integer | BIT[(M)] | 1 ~ 64 | BOOLEAN | STRING | Consistent with the data types of 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, 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 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 |
Category | Data type of the source instance | Value range | MaxCompute | Elasticsearch |
---|---|---|---|---|
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. |
TINYINT[(M)] | -128 ~ 127 | BIGINT | SHORT | |
TINYINT[(M)] [UNSIGNED] | 0 ~ 255 | BIGINT | INTEGER | |
SMALLINT[(M)] | -32768 ~ 32767 | BIGINT | SHORT | |
SMALLINT[(M)] [UNSIGNED] | 0 ~ 65535 | BIGINT | INTEGER | |
MEDIUMINT[(M)] | -8388608 ~ 8388607 | BIGINT | INTEGER | |
MEDIUMINT[(M)] [UNSIGNED] | 0 ~ 16777215 | BIGINT | INTEGER | |
INT[(M)] | -2147483648 ~ 2147483647 | BIGINT | INTEGER | |
INT[(M)] [UNSIGNED] | 0 ~ 4294967295 | BIGINT | LONG | |
BIGINT[(M)] | -9223372036854775808 ~ 9223372036854775807 | BIGINT | LONG | |
BIGINT[(M)] [UNSIGNED] | 0 ~ 18446744073709551615 | BIGINT | LONG | |
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. |
FLOAT(p) | 1.175494351E-38 ~ 3.402823466E+38 | DOUBLE | FLOAT | |
DOUBLE[(M,D)] | 2.2250738585072014E-308 ~ 1.7976931348623157E+308 | DOUBLE | DOUBLE | |
Date and time | DATE | 1000-01-01~9999-12-31 Note The format is YYYY-MM-DD, in UTC. | DATETIME | DATE 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. | DATETIME | DATE 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. | DATETIME | DATE 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. | STRING | DATE Note The DATE format is YYYY-MM-DD, in UTC. For more information, see Date format mappings. | |
YEAR[(4)] | 1901 to 2155, or 0000 | STRING | DATE Note The DATE format is yyyy, in UTC. For more information, see Date format mappings. | |
String | CHAR[(M)] | 0 to 255 characters | STRING | TEXT |
VARCHAR(M) | 0 to 65,535 characters | STRING | TEXT | |
BINARY[(M)] | 0 to 255 bytes | STRING | BINARY | |
VARBINARY(M) | 0 to 65,535 bytes | STRING | BINARY | |
TINYBLOB | 255 (2^8 - 1) bytes | STRING | BINARY | |
TINYTEXT | 255 (2^8 - 1) characters | STRING | TEXT | |
BLOB | 65,535 (2^16 - 1) bytes | STRING | BINARY | |
TEXT | 65,535 (2^16 - 1) characters | STRING | TEXT | |
MEDIUMBLOB | 16,777,215 (2^24 - 1) bytes | STRING | BINARY | |
MEDIUMTEXT | 16,777,215 (2^24 - 1) characters | STRING | TEXT | |
LONGBLOB | 4,294,967,295 or 4 GB (2^32 - 1) bytes | STRING | BINARY | |
LONGTEXT | 4,294,967,295 or 4 GB (2^32 - 1) characters | STRING | TEXT | |
ENUM('value1','value2',...) | An ENUM column can have a maximum of 65,535 distinct elements. | STRING | KEYWORD | |
SET('value1','value2',...) | A SET column can have a maximum of 64 distinct elements. | STRING | KEYWORD | |
Spatial | GEOMETRY | Geometry values of any type | STRING | GEO_SHAPE |
POINT | N/A | STRING | GEO_POINT | |
LINESTRING | N/A | STRING | GEO_SHAPE | |
POLYGON | N/A | STRING | GEO_SHAPE | |
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. | |
MULTILINESTRING | N/A | STRING | GEO_SHAPE | |
MULTIPOLYGON | N/A | STRING | GEO_SHAPE | |
GEOMETRYCOLLECTION | A collection of geometry values of any type | STRING | GEO_SHAPE | |
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. |
Synchronize data from an Oracle database
The following table lists the data type mappings between a self-managed Oracle database and an AnalyticDB for PostgreSQL instance.Note If the value range of the data to be synchronized from the source instance exceeds the range supported by DTS, the accuracy of the data written to the destination instance will decrease.
Category | Data type of Oracle | Value range | Data type of AnalyticDB for PostgreSQL |
---|---|---|---|
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 | TINYINT | SMALLINT | INTEGER | BIGINT |
FLOAT(p) | 1 to 22 bytes The variable p indicates a pointer. Valid values: 1 to 126 bits. | DOUBLE PRECISION | |
BINARY_FLOAT | A 32-bit floating-point number (4 bytes) | DOUBLE PRECISION | |
BINARY_DOUBLE | A 64-bit floating-point number (8 bytes) | DOUBLE PRECISION | |
Date and time | DATE | N/A | TIMESTAMP(0) |
TIMESTAMP [(fractional_seconds_precision)] | N/A | TIMESTAMP | |
TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE | N/A | TIMESTAMP WITH TIME ZONE | |
TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONE | N/A | TIMESTAMP WITH TIME ZONE | |
INTERVAL YEAR [(year_precision)] TO MONTH | N/A | VARCHAR(32) | |
INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)] | N/A | VARCHAR(32) | |
String | CHAR [(size [BYTE | CHAR])] | 2,000 bytes | CHAR |
NCHAR[(size)] | 2,000 bytes | VARCHAR | |
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 | |
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. | VARCHAR | |
LONG | The maximum size is 2 GB (2^31 - 1). | TEXT | |
RAW(size) | The maximum size is 32,767 bytes or 2,000 bytes. | BYTEA | |
LONG RAW | The maximum size is 2 GB. | BYTEA | |
CLOB | The maximum size is (4 GB - 1) × DB_BLOCK_SIZE. | TEXT | |
NCLOB | The maximum size is (4 GB - 1) × DB_BLOCK_SIZE. | TEXT | |
BLOB | The maximum size is (4 GB - 1) × DB_BLOCK_SIZE. | BYTEA | |
BFILE | The maximum size is 4 GB. | Not supported | |
JSON | JSON | The maximum size is 32 MB. | JSON |
ROWID | ROWID | 64 characters | OID |
Spatial | Customization required | Not supported |
Note 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.
Synchronize data from a PostgreSQL database
The following table lists the data type mappings between PostgreSQL and AnalyticDB for PostgreSQL. The source PostgreSQL database can be a self-managed PostgreSQL database or an ApsaraDB RDS for PostgreSQL instance.Note If the value range of the data to be synchronized from the source instance exceeds the range supported by DTS, the accuracy of the data written to the destination instance will decrease.
Category | Data type of PostgreSQL | Value range | Data type of AnalyticDB for PostgreSQL |
---|---|---|---|
Integer | SMALLINT | -32768 to +32767 | SMALLINT |
INTEGER | -2147483648 to +2147483647 | INTEGER | |
BIGINT | -9223372036854775808 to +9223372036854775807 | BIGINT | |
Decimal | DECIMAL | Up to 131072 digits before the decimal point. Up to 16383 digits after the decimal point. | DECIMAL |
NUMERIC | Up to 131072 digits before the decimal point. Up to 16383 digits after the decimal point. | NUMERIC | |
REAL | 6 decimal digits of precision | REAL | |
DOUBLE PRECISION | 15 decimal digits of precision | DOUBLE PRECISION | |
Monetary | MONEY | -92233720368547758.08 to +92233720368547758.07 | MONEY |
String | CHARACTER VARYING(n) | N/A | CHARACTER VARYING(n) |
CHARACTER(n) | N/A | CHARACTER(n) | |
TEXT | N/A | TEXT | |
CHAR | The default length is 1 byte. | CHAR | |
NAME | The maximum length is 64 bytes. | NAME | |
Text search | TSQUERY | A text query | TEXT |
TSVECTOR | A document in a form optimized for text search | TEXT | |
Binary | BYTEA | 1 or 4 bytes plus the actual binary string | BYTEA |
Date and time | TIMESTAMP [ (p) ] [ WITHOUT TIME ZONE ] | Date and time without time zone. Storage size: 8 bytes. | TIMESTAMP [ (p) ] [ WITHOUT TIME ZONE ] |
TIMESTAMP [ (p) ] WITH TIME ZONE | Date and time with time zone. Storage size: 8 bytes. | TIMESTAMP [ (p) ] WITH TIME ZONE | |
DATE | A date. Storage size: 4 bytes. | DATE | |
TIME [ (p) ] [ WITHOUT TIME ZONE ] | A time without time zone. Storage size: 8 bytes. | TIME [ (p) ] [ WITHOUT TIME ZONE ] | |
TIME [ (p) ] WITH TIME ZONE | A time with time zone. Storage size: 12 bytes. | TIME [ (p) ] WITH TIME ZONE | |
interval [ fields ] [ (p) ] | A time interval. Storage size: 16 bytes. | interval [ fields ] [ (p) ] | |
Boolean | BOOLEAN | 1 byte | BOOLEAN |
Enumerated | Customization required | N/A | VARCHAR(128) |
Spatial | POINT | A point on a plane. Storage size: 16 bytes. | POINT |
LINE | An infinite line. Storage size: 32 bytes. | LINE | |
LSEG | A finite line segment. Storage size: 32 bytes. | LSEG | |
BOX | A rectangular box. Storage size: 32 bytes. | BOX | |
PATH | A path. Storage size: 16 + 16n bytes. | PATH | |
POLYGON | A polygon (similar to closed path). Storage size: 40 + 16n bytes. | POLYGON | |
CIRCLE | A circle. Storage size: 24 bytes. | CIRCLE | |
Network address | CIDR | IPv4 and IPv6 networks. Storage size: 7 or 19 bytes. | CIDR |
INET | IPv4 and IPv6 hosts and networks. Storage size: 7 or 19 bytes. | INET | |
MACADDR | MAC addresses. Storage size: 6 bytes. | MACADDR | |
MACADDR8 | MAC addresses in EUI-64 format. Storage size: 8 bytes. | MACADDR8 | |
Bit string | Bit (n) | N/A | Bit (n) |
BIT VARYING (n) | N/A | BIT VARYING (n) | |
UUID | UUID | N/A | VARCHAR(64) |
XML | XML | N/A | XML |
JSON | JSON | N/A | JSON |
JSONB | N/A | JSONB |
Synchronize 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 synchronized from the source instance exceeds the range supported by DTS, the accuracy of the data written to the destination instance will decrease.
Category | 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 | -32,768 (-2^15) to 32,767 (2^15 - 1) | SMALLINT | SMALLINT | |
INT | -2,147,483,648 (-2^31) to 2,147,483,647 (2^31 - 1) | INTEGER | INTEGER | |
BIGINT | -9,223,372,036,854,775,808 (-2^63) to 9,223,372,036,854,775,807 (2^63 - 1) | 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 1,073,741,823 (2^30 - 1) bytes. | VARCHAR | TEXT | |
TEXT | The maximum string length is 2,147,483,647 (2^31 - 1) bytes. | VARCHAR | TEXT | |
IMAGE | Variable-length binary data from 0 to 2,147,483,647 (2^31 - 1) 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 |
Others | 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) |
Synchronize 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 synchronized from the source instance exceeds the range supported by DTS, the accuracy of the data written to the destination instance will decrease.
Category | 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 | |
Others | XML | 2,147,483,647 | VARCHAR | LONGTEXT |
Synchronize 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 synchronized from the source instance exceeds the range supported by DTS, the accuracy of the data written to the destination instance will decrease.
Category | 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 | |
Others | DATALINK | N/A | VARCHAR | LONGTEXT |
ROWID | 40 | VARCHAR | LONGTEXT | |
XML | 2,147,483,647 | VARCHAR | LONGTEXT |