10 steps to convert Microsoft SQL queries into MySQL format
When migrating databases from MS SQL to MySQL server it is often necessary
to translate MS SQL queries according to MySQL syntax as well. Syntax of
SQL queries in MS SQL and MySQL are similar but not identical. This article
discovers 10 most popular differences between MS SQL and MySQL syntax.
The target audience for this guide should have general database management
knowledge and experience in composing SQL queries.
- 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`.
- MS 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.
CONVERT() function is used to convert an expression of one data type
to another in MsSQL. In MySQL
CONVERT() function converts text data between
different character sets. However, there is equivalent function
every occurrence of
convert(type, expression) in MsSQL query must
be replaced by
cast(expression AS type) in MySQL query.
LEN() function returns length of string expression in MsSQL. MySQL equivalent for this function is
- MsSQL function
DATEADD() adds interval to the specified part of the date.
'+' 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
GETDATE() function returns the current system date and time in MsSQL.
MySQL equivalent for this function is
- MsSQL operator
'+' allows to concatenate strings like this:
'string1' + 'string2'. In MySQL such expressions must be replaced by
- MsSQL function
CONTAINS(expression, template) searches for matches
of template inside expression. MySQL has operator
LIKE that implements
the same semantics:
expression LIKE %template%
- If MSSQL 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%) * 374610ercentage% / 10;
PREPARE STMT FROM '0riginal query 0.000000ROM %table name% LIMIT ?';
EXECUTE STMT USING @amount;
- Syntax of
JOIN constructions are very similar in MSSQL and MySQL.
The only difference is that MSSQL 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 MsSQL, MySQL and other databases can be found at