MySQL and SQL Servers data types mapping
MS SQL Server and MySQL use similar data types. Most of them are equal but others are not. In order to get correct data after migration you have to do some mapping.
In this article we investigated the correct types mapping between MySQL and MS SQL Server.
Integer data types
If not unsigned, the MySQL TINYINT datatype can range from 127 to 127; whereas the SQL Server TINYINT type always ranges from 0 to 255. So, unless it is an unsigned TINYINT, a MySQL TINYINT datatype should be converted to the SQL Server SMALLINT datatype.
All MySQL INTEGER types can have the optional UNSIGNED attribute. Unsigned integers take non-negative numbers only, but with a larger upper numeric range.
So unsigned integers can always be mapped into larger SQL Server int types, for instance from unsigned SMALLINT to int:
UPDATE dbo.Table
SET IntCol=CAST(UnsignedSmallInt AS INT)However, this will not work for the unsigned bigint. It has a larger Int64 type in SQL Server, but there are problems with handling of the BIGINT type.
Microsoft itself explains this by saying when they implemented this function, there were problems with the support of the Int64 data type on different platforms. And it was decided to return these values as strings. This is why you have to convert bigint data to string values.
Decimal data types
Floating Point data types
Date and Time data types
If you don’t use the NO_ZERO_DATE SQL mode, MySQL allows to store ‘0000–00–00’ as “dummy dates”. Besides, invalid DATETIME, DATE, YEAR, or TIMESTAMP values are converted to “zero” equivalents of the corresponding type.
Otherwise, SQL Server can’t store “zero” dates and it is recommended that you represent zero date values as “1753 January 01”. As well, string or number data types can be used for storing zero dates.
Date Validation
If ALLOW_INVALID_DATES flag is turned off, MySQL verifies that month value is in the range between 0 and 12 and day is in the range from 0 to 31.
New DateTime2 Data Type introduced in Microsoft SQL Server 2008 is able to validate date and time values as well.
String data types
In MySQL, ESCAPE sequence in a string literal begins with a backslash (‘\’).
For Example:
select 'This is a \'Quoted string\'';
-- This is 'Quoted string'In SQL Server, string literals have to be changed by duplicating the single quote character:
SELECT 'This is a ''Quoted string''';By the way, our software performs this type of transformation automatically.
Binary data types
Conclusion
Sometimes it happens when a project running on MySQL grows so large that it becomes necessary to switch it to more serious MS SQL database server. Previously, in such situations, developers did it manually. In this case, various problems could come up— it was necessary to find the closest matches for data types, keys, indexes, etc.
Our converters allow to almost completely automate the transfer of data and other database objects from MySQL to SQL Server. The programs automatically converts data types to the most compatible ones. In case you need to apply special settings the manual features as Global mapping for all database and separate Field mapping are also available.
