Ten Tips on How To Convert MS SQL Queries to MySQL

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

 

[Dhruv Bindhani]

The article provides a valuable overview of the common syntax differences between MS SQL Server and MySQL, which is essential for anyone undertaking a database migration between these two systems. It accurately highlights 10 key distinctions that developers and database administrators should be aware of.

Here's a review of the article's points:

The premise of the article is solid: while both MS SQL and MySQL use SQL, their specific implementations of functions, operators, and object referencing vary. The target audience, with basic database administration and SQL query knowledge, is well-suited for this guide.

The distinctions are well-chosen and cover common conversion pitfalls:

1. Identifier Quoting ([] vs. `): This is a very common and immediate syntax difference. The article correctly identifies the need to replace square brackets with backticks or remove them.
2. Schemas: This is a crucial conceptual difference. MS SQL schemas are logical grouping mechanisms within a database, whereas in MySQL, a <span>SCHEMA</span> is effectively synonymous with a <span>DATABASE</span>.1 The advice to cut off schema names or treat them as part of the object name using backticks is accurate for migration purposes.
   
   
   
   
3. CONVERT() Function: The distinction between data type conversion in MS SQL and character set conversion in MySQL, and the correct substitution with CAST() in MySQL, is vital for maintaining data integrity.
4. LEN() vs. LENGTH(): A straightforward function name difference. It's worth noting that <span>LEN()</span> in MS SQL ignores trailing spaces, while <span>LENGTH()</span> in MySQL counts bytes (not necessarily characters for multi-byte encodings).2 For character count, CHAR_LENGTH() is often more appropriate in MySQL.
   
   
   
   
5. DATEADD() vs. + INTERVAL: The article correctly illustrates the different syntax for date arithmetic. MySQL's <span>+ INTERVAL</span> operator or <span>DATE_ADD()</span> function are the standard ways to achieve this.3
   
   
   
   
6. GETDATE() vs. NOW(): A simple function name change for retrieving the current timestamp.
7. String Concatenation (+ vs. CONCAT()): This is a very common and critical difference. MS SQL uses +, which can conflict with arithmetic operations, while MySQL uses the dedicated CONCAT() function.
8. CONTAINS() vs. LIKE: The article states LIKE implements the same semantics as CONTAINS. This is partially true for basic pattern matching, but it's important to clarify that MS SQL's <span>CONTAINS</span> is a full-text search function, offering more advanced linguistic capabilities (e.g., stemming, thesaurus) that <span>LIKE</span> does not.4 For equivalent full-text search in MySQL, one would use <span>MATCH() AGAINST()</span> with a <span>FULLTEXT</span> index.5 The article's simplified equivalence might mislead users needing advanced full-text features.
   
   
   
   
   
9. TOP (N) PERCENT: This is a complex conversion. The article correctly points out that MySQL lacks a direct TOP PERCENT equivalent and provides a valid, albeit more involved, workaround using COUNT(*) and LIMIT with prepared statements. For simple <span>TOP N</span> (fixed number of rows), MySQL uses <span>LIMIT N</span>.6
   
   
   
   
10. CHARINDEX() vs. LOCATE(): Another direct function name replacement, accurately identified.

Overall, the article is a practical and useful resource. The listed distinctions cover many of the common hurdles in migrating queries. The only minor area for improvement would be to further elaborate on the CONTAINS to MATCH() AGAINST() conversion for true full-text search equivalence, beyond just LIKE. The inclusion of a link to additional resources is also beneficial.