10hints on conversion of MS SQL queries to MySQL

Database migration from SQL Server to MySQLoften involves conversion of views. While table definitions and data can be migrated automatically using the special software, this part of migration process may require some manual efforts.

Both MS SQL and MySQL have similar syntax of SQL queries, however it isnot identical. This article exposes 10 most important differences between query languagedialects of SQL Server and MySQL.

This guide requiresthe target audience to have basic knowledge in database programming.

In MS SQL queries database object names may be enclosed in square brackets. MySQL does not allow square brackets around names, they all must be replaced by ` symbol or cut off:

[object] -> `object`

MS SQL supports schemain order to avoid naming conflicts and to manage user permissions on database. When using schemas, database object name may look in a query like this:

database.schema.object

Since MySQL does not support schema, all schema names may become a part of object name or must be cut off.

SQL Server provides function CONVERT() that used to convert an expression from one type to another. In MySQLthis function has another meaning: it converts text data between different character sets. That’s why each entry of ‘convert(type, expression)’ must be convertedinto ‘cast(expression AS type)’ in MySQL query.

MS SQL function LEN() that returns length of string expression must be converted into MySQL equivalent LENGTH().

DATEADD()function adds interval to the specified part of the date in SQL Server. Although MySQL does not have similar function, the operator ‘+’ can do the same:

DATEADD(year, 1, expression) -> expression + interval 1 year

DATEADD(month, 1, expression) -> expression + interval 1 month

DATEADD(day, 1, expression) ->expression + interval 1 day

MS SQL function GETDATE()returns the current system date and time. MySQL has equivalent for this function that is NOW().

In SQL Server the operator ‘+’ concatenates strings: ‘string1’ + ‘string2’. MySQL provides the function CONCAT for the same purpose. So, the expression above must be converted into:

CONCAT(‘string1’, ‘string2’)

MS SQL function CONTAINS(expression, template) is used to search matches of template inside expression. MySQL operator LIKE provides the same capabilities:

expression LIKE %template%

MS SQL query may contain the pattern ‘TOP (N) PERCENT’.If N=100, it may besimply cut of in MySQL query. Otherwise, the query can be replace by the following code in MySQL (versoin 5.0.7 or higher is required):

SET @amount = SELECT COUNT(*) FROM table * percentage / 10;

PREPARE STMT FROM ‘original_query FROM tableLIMIT ?’;

EXECUTE STMT USING @amount;

In above example patterns ‘table’, ‘percentage’ and ‘original query’ must be replaced by actual values.