Home > Documentation > SQL Server to MySQL
How to convert SQL Server queries into MySQL format |
[SQL Server to MySQL Converter] [About Migration] [Safe Types Mapping] [Stored Procedures] |
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.
Also, 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.
CHARINDEX
to search substring into
string. MySQL equivalent for this function is LOCATE
.
CONVERT()
function is used to convert an expression of one data type
to another in MS 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. See
special note on using CONVERT()
for DATETIME values.
IIF($boolean_expression, $true_value, $false_value)
that
returns $true_value or $false_value value depending on $boolean_expression, must be
replaced by IF
in MySQL.
LEN()
function returns length of string expression in MS SQL. MySQL
equivalent for this function is LENGTH()
.
DATEADD
adds interval to the specified part of the date.
MySQL operator '+'
can do the same as follows:
DATEADD(year, 1, $date$) -> $date$ + interval 1 year DATEADD(month, 1, $date$) -> $date$ + interval 1 month DATEADD(day, 1, $date$) -> $date$ + interval 1 day
where $date$ is an expression of DATE type. Function DATEDIFF($datepart,
$startdate, $enddate)
returns $datepart part of substructing $startdate
from $enddate. It can be converted into MySQL as follows:
DATEDIFF(hour, $startdate, $enddate) -> TIMESTAMPDIFF(hour, $enddate, $startdate) DATEDIFF(minute, $startdate, $enddate) -> TIMESTAMPDIFF(minute, $enddate, $startdate) DATEDIFF(month, $startdate, $enddate) -> TIMESTAMPDIFF(month, $enddate, $startdate)
In general, Microsoft SQL and MySQL have different sets of date processing functions, although most of them can be replicated as follows:
DATENAME(month, $date$) -> DATE_FORMAT($date$, '%M') or MONTHNAME(expression) DATENAME(weekday, $date$) -> DATE_FORMAT($date$, '%W') or DAYNAME(expression) DATEPART(year, $date$) -> DATE_FORMAT($date$, '%Y') DATEPART(month, $date$) -> DATE_FORMAT($date$, '%m') DATEPART(day, $date$) -> DATE_FORMAT($date$, '0') GETDATE() -> NOW() GETUTCDATE() -> UTC_TIMESTAMP()
where $date$ is an expression of DATE type.
'+'
allows to concatenate strings like this:
'string1' + 'string2'
. In MySQL such expressions must be replaced by
CONCAT('string1', 'string2')
.
CONTAINS(expression, template)
searches for matches
of template inside expression. MySQL has operator LIKE
that implements
the same semantics: expression LIKE %template%
'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;
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 conditionmust be translated into
... table1 CROSS JOIN table2 ON condition
Have any questions? Contact us