Data Type Mapping
FlyData Sync Data Type Mapping: MySQL -> Redshift (ver. 0.3.17)
There are data type differences between MySQL and Amazon Redshift. Some formats are supported, while others are not.
In order to provide a smooth replication of data from MySQL to Redshift, we automatically convert MySQL data types to match the closest equivalent data types supported in Amazon Redshift.
Below is a mapping of this data type conversion.
MySQL Data Type |
Redshift Data Type |
Agent version |
Notes |
| BIGINT | INT8 | ||
| BIGINT UNSIGNED | NUMERIC(20, 0) | *1 | |
| BINARY | VARCHAR | *2, *3 | |
| BIT | INT8 | *11 | |
| BLOB | VARCHAR(65535) | ||
| BOOL or BOOLEAN | INT2 | ||
| CHAR | VARCHAR | *3, *4 | |
| DATE | DATE | *5 | |
| DATETIME | TIMESTAMP | *5 | |
| DEC or DECIMAL | NUMERIC | *6 | |
| DEC or DECIMAL UNSIGNED | NUMERIC | *6 | |
| DOUBLE [PRECISION] | FLOAT8 | ||
| DOUBLE [PRECISION] UNSIGNED | FLOAT8 | *1 | |
| ENUM | VARCHAR | *7 | |
| FIXED | NUMERIC | *6 | |
| FIXED UNSIGNED | NUMERIC | *6 | |
| FLOAT | FLOAT4 | ||
| INT or INTEGER | INT4 | ||
| INT or INTEGER UNSIGNED | INT8 | *1 | |
| LONGBLOB | VARCHAR | *2, *3 | |
| LONGTEXT | VARCHAR(MAX) | *3, *4 | |
| MEDIUMBLOB | VARCHAR | *2, *3 | |
| MEDIUMINT | INT4 | ||
| MEDIUMINT UNSIGNED | INT4 | *1 | |
| MEDIUMTEXT | VARCHAR(MAX) | *3, *4 | |
| NUMERIC | NUMERIC | ||
| SET | VARCHAR | *7 | |
| SMALLINT | INT2 | ||
| SMALLINT UNSIGNED | INT4 | *1 | |
| TEXT | VARCHAR(MAX) | *3, *4 | |
| TIME | TIMESTAMP | *8, *9, *10 | |
| TIMESTAMP | TIMESTAMP | *5 | |
| TINYBLOB | VARCHAR | *2 | |
| TINYINT | INT2 | ||
| TINYINT UNSIGNED | INT2 | ||
| TINYTEXT | VARCHAR(MAX) | *4 | |
| VARBINARY | VARCHAR(MAX) | *2, *3 | |
| VARCHAR | VARCHAR | *3, *4 | |
| YEAR | DATE | 0.3.10 | *12 |
| GEOMETRY, POINT, LINESTRING, POLYGON | (unsupported) | ||
| MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION | (unsupported) |
Notes
- *1) Redshift does not support unsigned types
- *2) Binary bytes get translated into a string of “0xFFFF…” format. e.g.) Binary 40bc8f => “0x40bc8f”
- *3) Truncated at 65,535 which is the max length of Redshift VARCHAR type
- *4) String gets converted to UTF-8
- *5) ‘0000-00-00’ becomes ‘0001-01-01’
- *6) maximum (precision, scale) is (38, 37)
- *7) label text is stored as varchar string
- *8) ‘00:00:00’ gets converted to ‘0001-01-01 00:00:00’
- *9) Negative value gets converted as an offset from ‘0001-01-01 00:00:00’. For example, ‘-01:00:00’ becomes ‘0001-12-31 23:00:00 BC’
- *10) Time value whose hour part is more than 23 is represented using the day part. For example, ‘25:00:00’ becomes ‘0001-01-02 01:00:00’
- *11) ex. b’101’ becomes 5 in Redshift
- *12) DATE(2) and DATE(4) are supported