10 hints on conversion of MS SQL queries to MySQL

Database migration from SQL Server to MySQL often 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 is not identical. This article exposes 10 most important differences between query language dialects of SQL Server and MySQL.

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

  1. 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`

  1. MS SQL supports schema in 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.

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

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

  1. 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

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

  1. 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’)

  1. 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%

  1. MS SQL query may contain the pattern ‘TOP (N) PERCENT’. If N=100, it may be simply 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 table LIMIT ?’;

EXECUTE STMT USING @amount;

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

  1. JOIN constructions are very similar in SQL Server and MySQL. Just replace keyword ‘WHERE’ by ‘ON’ in MySQL query. For example:

… table1 cross join table2 where condition

must be converted into:

… table1 cross join table2 on condition