TenTips on How ToConvertMS SQL QueriestoMySQL

Anytime youdecide onmovingdatabasesfromMS SQL toMySQLserver, it is idealto convertMS SQL queriesin accordance withMySQLsyntaxalso. Syntaxof SQL queriesinMS SQL andMySQLare the samebutdifferentin theiruniqueness. In this article, we willdiscussmore aboutthe 10most populardistinctionsbetweenMS SQL andMySQLsyntax.

It is assumedthat thetarget audiencethat will beusing thisguideought to havebasicdatabaseadministrationexperience and knowledgein writing SQL queries.

1) At times, theMS SQL tableorcolumnnamesaresurroundedinsquarebracketsinqueries (e.g. ifcontainsspacesor forsome otherreasons). MySQLdoesn’tpermitsquarebracketsaroundtableofcolumnnames, all of themhas to besubstitutedby ` symbolorcut off: [object] -> `object`.

2) MS SQL offersefficientsolution topreventlabelingobjectsconflictas well asmanageuserpermissionsondataaccess. This is schema, alogiccontainerthat is used togroupobjectswith similar semantics inside thesingledatabase.When use schemas,the fullnamereferencingdatabaseobjectinquerywilllook likeschema.object. However, there is nosuch semantic inMySQL, so all schema namesmust becut offfromqueries or treated as part of object name enclosed in MySQL quotes `schema.object`.

3) CONVERT() functionconvertsan expressionfrom onedatatypeto anotherinMS SQL. InMySQLCONVERT() functionconvertstextdatabetweendifferentcharactersets. However, there isequivalentfunctionCAST(), soeachentryof ‘convert(%type%, %expression%)’inMS SQL querymust bereplaced by ‘cast(%expression% AS %type%)’inMySQLquery.

4) LEN() functionreturnslength ofstringexpressioninMS SQL. MySQLequivalentfor this functionisLENGTH().

5) MS SQL functionDATEADD() addsintervalto thespecifiedpart of thedate. MySQLoperator ‘+’ can dothe same asfollows:

DATEADD(date_interval,  1, date_expression) ->date_expression + interval1 date_expression

6) GETDATE() functionreturnsthe currentsystemdate and timeinMS SQL. MySQLequivalentfor this functionis NOW().

7) MS SQL operator ‘+’ allowsto concatenate stringslike this: ‘string1’ + ‘string2’. InMySQLsuchexpressionsmust bereplaced byCONCAT(‘string1’, ‘string2’).

8) MS SQL functionCONTAINS(expression, template) searches formatchesoftemplateinsideexpression. MySQLhasoperatorLIKE thatimplementsthe same semantics: expressionLIKE %template%

9) pattern’TOP (100) PERCENT’ from MS SQL querymust becut offwhenconverting intoMySQLformat. If there isanotherpercentagevaluein thatpattern, it can bereplaceby thefollowingcodeinMySQL (worksinMySQL5.0.7and higher):

SET @row_count=(SELECTCOUNT(*) FROMtable_name) *percentage_amount/10;

PREPARE STMT FROM ‘original_queryFROMtable_nameLIMIT ?’;

EXECUTE STMT USING @row_count;

10) SyntaxofJOINconstructionsare verysimilarinMS SQL andMySQL, just replacekeyword ‘WHERE’ by ‘ON’ inthe resulting query. For example:

‘… table1CROSSJOIN table2WHERE condition’

must betranslatedinto

‘… table1CROSSJOIN table2ON condition’

11) MS SQL function charindex(exp1, exp2) must bereplacedLOCATE(exp1, exp2)

OtherarticlescoveringtopicsonMS SQL, MySQLas well as other databases are available at https://www.convert-in.com/articles.htm.

Leave a Reply

Your email address will not be published. Required fields are marked *