Convert queries from SQL Server to MySQL

One essential step of database migration from SQL Server to MySQL is conversion of queries used for views, stored procedures and triggers. While data migration can be automated using the special software, this task may require some human efforts especially for complicated queries.

SQL Server and MySQL have similar syntax of queries, however it is not equal mostly due to different sets of embedded functions. This article covers conversion of the most popular patterns of query language from SQL Server to MySQL.

  1. MS SQL required composed object names (containing two and more words) are enclosed in in square brackets. MySQL does not accept such syntax, all square brackets must be replaced by ` symbol:

[the object] -> `the object`

  1. SQL Server supports schemas in order to avoid naming conflicts and to manage user permissions on database. When specifying schema for object name it may look like this:

database.schema.object

MySQL does not support schema entity, all schema specifications should become a part of object name (database.schema_object) or must be cut off (database.object).

  1. Function CONVERT() is used to convert an expression from one type to another in SQL Server. MySQL has the same function but with another meaning: it converts text data between different character sets. That’s why each occurrence of CONVERT must be replaced by equivalent CAST in MySQL query:

convert(type, $expression) -> cast($expression AS type)

  1. Each occurrence of DATEADD() function that adds interval to the specified part of the date in SQL Server must be replaced by the operator ‘+’:

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. Function GETDATE() returns the current date and time in MS SQL. MySQL has direct match for this function that is NOW().
  2. SQL Server uses the operator ‘+’ to concatenates strings: ‘string1’ + ‘string2’ + ‘string3’. In MySQL there is function CONCAT() for the same purpose

CONCAT(‘string1’, CONCAT(‘string2’, ‘string3’))

  1. Function CONTAINS(expression, template) is provided by MS SQL to search matches of template inside expression. In MySQL operator LIKE is used for the same purposes:

expression LIKE %template%

  1. In SQL Server query may contain the pattern ‘TOP (N) PERCENT’ to extract partial rows from the top of resulting collection. If N is equal to 100, this pattern should be just omitted in MySQL query. Otherwise, the query should be rewritten in MySQL as follows (version 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 this example ‘table’, ‘percentage’ and ‘original query’ must be replaced by actual values.

The steps listed above mean that converting queries from SQL Server to MySQL is not an easy task. The person responsible for database migration should consider using special tools to automate the conversion, especially for large databases having a lot of views.

One of such tool is MS SQL to MySQL converter provided by Intelligent Converters company. The program converts more than 80% of all possible syntax constructions used in CREATE VIEW queries. It also handles migration of table definitions, indexes, constraints and data.

Advertisement

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s