Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

10 tips on converting MS SQL queries to MySQL

By Oleg Fedorov,

When migrating databases from Microsoft SQL Server to MySQL, it is often necessary to translate SQL Server queries to the MySQL syntax. The syntax of SQL queries in both database systems are similar but not identical. This article discovers 10 most popular differences between the SQL Server and MySQL syntaxes. The target audience for this guide should have general database management knowledge and experience in composing SQL queries.

1) Sometime MS SQL table or column names are enclosed in square brackets in queries (e.g. if contains spaces or for some other reasons). MySQL does not allow square brackets around table of column names, they all must be replaced by ` symbol or cut off: [object] -> `object`.

2) Microsoft SQL provides effective solution to avoid naming objects conflict and to manage user permissions on data access. This is schema, a logic container used to group and categorize objects inside the single database. When using schema the full name of database object in query may look like %database%.%schema%.%object%. However, there is no such semantic in MySQL, so all schema names must be cut off from queries.

3) CONVERT() function is used to convert an expression of one data type to another in Microsoft SQL. In MySQL CONVERT() function converts text data between different character sets. However, there is equivalent function CAST(), so every occurrence of 'convert(%type%, %expression%)' in MS SQL query must be replaced by 'cast(%expression% AS %type%)' in MySQL query.

4) LEN() function returns length of string expression in MS SQL. MySQL equivalent for this function is LENGTH().

5) Microsoft SQL function DATEADD() adds interval to the specified part of the date. MySQL operator '+' can do the same as follows:

DATEADD(year,  1, %expression%) -> %expression% + interval 1 year
DATEADD(month, 1, %expression%) -> %expression% + interval 1 month
DATEADD(day,   1, %expression%) -> %expression% + interval 1 day

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

7) MS SQL operator '+' allows to concatenate strings like this: 'string1' + 'string2'. In MySQL such expressions must be replaced by CONCAT('string1', 'string2').

8) MS SQL function CONTAINS(expression, template) searches for matches of template inside expression. MySQL has operator LIKE that implements the same semantics: expression LIKE %template%

9) If MS SQL query contains 'TOP (100) PERCENT' pattern just cut it off when composing MySQL query. If there is another percentage amount in that pattern, it can be replace by the following code in MySQL (works in MySQL 5.0.7 and higher):

SET @amount =(SELECT COUNT(*) FROM %table name%) * %percentage% / 10;
PREPARE STMT FROM '%original query% FROM %table name% LIMIT ?';
EXECUTE STMT USING @amount;

10) Syntax of JOIN constructions are very similar in MS SQL and MySQL. The only difference is that MS SQL keyword 'WHERE' is replaced by 'ON' in MySQL. For example:

'... %table1% cross join %table2% where %condition%'

must be translated into

'... %table1% cross join %table2% on %condition%'

More articles about MS SQL, MySQL and other databases can be found at http://www.convert-in.com/articles.htm

Total article views: 5042 | Views in the last 30 days: 3
 
Related Articles
ARTICLE

Stairway to SQL Dialects Level 3: MySQL

As part of the LAMP stack, MySQL is incredibly important for providing a reliable and platform-agnos...

FORUM

Expression

Expression

FORUM

Linked server querying XML Column of MYSQL DB

Linked server querying XML Column of MYSQL DB

FORUM

mysql

writing procedures in mysql

FORUM

MySQL question

trouble restoring a mysql 5 database

Tags
mysql    
syntax    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones