MySQL data mapping

Mapping to MySQL consolidated data types

The following table identifies how SQL Anywhere and UltraLite remote data types are mapped to MySQL consolidated data types. For example, a column of type TEXT on the remote database should be type LONGTEXT on the consolidated database.

SQL Anywhere or UltraLite data type	MySQL data type	Notes
BIGINT	BIGINT
BINARY(n<=255)	BINARY(n)
BINARY(n>255)	BLOB
BIT	BIT
CHAR(n<=255)	CHAR(n)
CHAR(n>255)	TEXT(n)
DATE	DATE	The year must range from 1000 to 9999.
DATETIME	DATETIME	The MySQL DATETIME data type does not support fractional seconds. The year must range from 1000 to 9999.
DECIMAL(p<=65,s<=30)	DECIMAL(p,s)
DECIMAL(p>65,s>30)		There is no corresponding data type in MySQL if the precision is greater than 65 or if the scale is greater than 30.
DOUBLE	DOUBLE
FLOAT	FLOAT
IMAGE	LONGBLOB
INTEGER	INTEGER
LONG BINARY	LONGBLOB
LONG NVARCHAR	LONGTEXT CHARACTER SET UTF8
LONG VARBIT	LONGTEXT
LONG VARCHAR	LONGTEXT
MONEY	NUMERIC(19,4)
NCHAR(n<=255)	CHAR(n) CHARACTER SET UTF8
NCHAR(n>255)	TEXT CHARACTER SET UTF8
NTEXT	LONGTEXT CHARACTER SET UTF8
NUMERIC(p<=65,s<=30)	DECIMAL(p,s)
NUMERIC(p>65,s>30)		There is no corresponding data type in MySQL.
NVARCHAR(n)	VARCHAR(n) CHARACTER SET UTF8
REAL	REAL
SMALLDATETIME	DATETIME	The MySQL DATETIME data type does not support fractional seconds. The year must range from 1000 to 9999.
SMALLINT	SMALLINT
SMALLMONEY	NUMERIC(10,4)
ST_GEOMETRY	GEOMETRY
TEXT	LONGTEXT
TIME	TIME	The MySQL TIME data type does not support fractional seconds.
TIMESTAMP	DATETIME	The MySQL DATETIME data type does not support fractional seconds. The year must range from 1000 to 9999.
TIMESTAMP WITH TIME ZONE	VARCHAR(34)	There is no equivalent data type in MySQL. Therefore, a TIMESTAMP WITH TIME ZONE column should be mapped to a VARCHAR(34) column. In upload, the MobiLink server first converts the data to a string using the format YYYY-MM-DD HH:NN:SS.SSSSSS [+\|-]HH:NN and then applies it to the consolidated database. In download, it converts the data from string to TIMESTAMP WITH TIME ZONE. Ensure the data in the consolidated database follows this format or the download will fail.
TINYINT	TINYINT UNSIGNED	TINYINT is always unsigned in SQL Anywhere and UltraLite.
UNIQUEIDENTIFIER	CHAR(36)
UNIQUEIDENTIFIERSTR	CHAR(36)
VARBINARY(n)	VARCHAR(n)
VARBIT(n<=8000)	VARCHAR(n)
VARBIT(n>8000)	TEXT
VARCHAR(n)	VARCHAR(n)
XML	LONGTEXT

Mapping to SQL Anywhere or UltraLite remote data types

The following table identifies how MySQL consolidated data types are mapped to SQL Anywhere and UltraLite remote data types. For example, a column of type BOOL on the consolidated database should be type BIT on the remote database.

MySQL data type	SQL Anywhere or UltraLite data type	Notes
BIGINT	BIGINT
BINARY(n)	BINARY(n)
BIT(1)	BIT
BIT(n>1)	UNSIGNED BIGINT
BLOB(n<=32767)	VARBINARY(n)
BLOB(n>32767)	IMAGE
BOOL	BIT
CHAR(n)	CHAR(n)
DATE	DATE	The year must range from 1000 to 9999.
DATETIME	DATETIME	The MySQL DATETIME data type does not support fractional seconds. The year must range from 1000 to 9999.
DOUBLE	DOUBLE
DECIMAL	DECIMAL
ENUM		There is no corresponding data type in SQL Anywhere or UltraLite.
GEOMETRY	ST_GEOMETRY
INTEGER	INTEGER
LINESTRING		There is no corresponding data type in SQL Anywhere or UltraLite.
LONGBLOB	IMAGE
LONGTEXT	TEXT
MEDIUMBLOB	IMAGE
MEDIUMINT	INTEGER
MEDIUMTEXT	TEXT
MULTILINESTRING		There is no corresponding data type in SQL Anywhere or UltraLite.
MULTIPOINT		There is no corresponding data type in SQL Anywhere or UltraLite.
MULTIPOLYGON		There is no corresponding data type in SQL Anywhere or UltraLite.
NCHAR	NCHAR	Not available in UltraLite.
NUMERIC	NUMERIC
NVARCHAR	NVARCHAR	Not available in UltraLite.
POINT		There is no corresponding data type in SQL Anywhere or UltraLite.
POLYGON		There is no corresponding data type in SQL Anywhere or UltraLite.
REAL	REAL
SET		There is no corresponding data type in SQL Anywhere or UltraLite.
SMALLINT	SMALLINT
TEXT(n<=32767)	VARCHAR(n)
TEXT(n>32767)	TEXT
TIME	TIME	The MySQL TIME data type does not support fractional seconds. The range of TIME in MySQL is '-838:59:59' to '838:59:59'. The range of TIME in SQL Anywhere or UltraLite is '00:00:00.000000' to '23:59:59:999999'.
TIMESTAMP	TIMESTAMP	The MySQL DATETIME data type does not support fractional seconds. The year must range from 1000 to 9999. Although MySQL offers automatic initialization and updating on TIMESTAMP columns, SQL Anywhere and UltraLite only offers automatic initialization.
TINYBLOB	VARBINARY
TINYINT	SMALLINT	TINYINT is always unsigned in SQL Anywhere and UltraLite. Must be a positive value.
TINYINT UNSIGNED	TINYINT	TINYINT is always unsigned in SQL Anywhere and UltraLite.
TINYTEXT	VARCHAR
VARBINARY(n<=32767)	VARBINARY(n)
VARBINARY(n>32767)	IMAGE
VARCHAR(n<=32767)	VARCHAR(n)
VARCHAR(n>32767)	TEXT
YEAR[(2\|4)]	INTEGER	SQL Anywhere and UltraLite do not support the YEAR data type. YEAR needs to be mapped to INTEGER in a remote database. The INTEGER value must range from 1000 to 9999.

Discuss this page in DocCommentXchange.