Anytime you decide on moving databases from MS SQL to MySQL server, it is ideal to convert MS SQL queries in accordance with MySQL syntax also. Syntax of SQL queries in MS SQL and MySQL are almost the same but different in their uniqueness. In this article, we will discuss more about the 10 most popular distinctions between MS SQL and MySQL syntax.
It is assumed that the target audience that will be using this guide ought to have basic database administration experience and knowledge in writing SQL queries.
1) At times, the MS SQL table or column names are surrounded in square brackets in queries (e.g. if contains spaces or for some other reasons). MySQL doesn’t permit square brackets around table of column names, all of them has to be substituted by ` symbol or cut off: [object] -> `object`.
2) MS SQL offers efficient solution to prevent labeling objects conflict as well as manage user permissions on data access. This is schema, a logic container that is used to group objects with similar semantics inside the single database. When use schemas, the full name referencing database object in query will look like schema.object. However, there is no such semantic in MySQL, so all schema names must be cut off from queries or treated as part of object name enclosed in MySQL quotes `schema.object`.
3) CONVERT() function converts an expression from 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 each entry 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) MS SQL function DATEADD() adds interval to the specified part of the date. MySQL operator ‘+’ can do the same as follows:
DATEADD(date_interval, 1, date_expression) -> date_expression + interval 1 date_expression
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) pattern ‘TOP (100) PERCENT’ from MS SQL query must be cut off when converting into MySQL format. If there is another percentage value in that pattern, it can be replace by the following code in MySQL (works in MySQL 5.0.7 and higher):
SET @row_count =(SELECT COUNT(*) FROM table_name) * percentage_amount / 10;
PREPARE STMT FROM ‘original_query FROM table_name LIMIT ?’;
EXECUTE STMT USING @row_count;
10) MS SQL function charindex(exp1, exp2) must be replaced LOCATE(exp1, exp2)
Other articles covering topics on MS SQL, MySQL as well as other databases are available at https://www.convert-in.com/docs/mss2sql/contents.htm
