This article is the second part in a series examining some of the challenges in porting your databases between MS SQL Server and MySQL. In Part 1, we looked at some of the differences between data types utilized by each DBMS. Today, in moving on to functions, you’ll see how functions may require additional effort on your part to produce equivalent results.
Functions can be divided into three distinct categories:
- Equivalent Functions: Those that can be safely migrated from one database type to another without any modifications whatsoever.
- Emulated Functions: Functions that are available in one database, but not the other. Another problematic issue is that some MySQL functions have a variable parameter count. In either case, some conversion work is required.
- Non-supported Functions: Those which cannot be easily ported because of logical/physical organization and security model differences.
You’ll be happy to know that the following functions are usable in both MySQL and SQL Server queries without any modifications:
ASCII, LEFT, LOWER, LTRIM, REPLACE, REVERSE, RIGHT, RTRIM, SOUNDEX, SPACE, SUBSTRING, UPPER, ABS, ACOS, ASIN, ATAN, ATAN2, CEILING, COS, COT, DEGREES, EXP, FLOOR, LOG, LOG10, PI, POWER, RADIANS, RAND, ROUND, SIGN, SIN, SQRT, TAN, DAY, MONTH, COALESCE, NULLIF, CAST, CONVERT.
Functions that have no equivalent on the other platform are where the bulk of your efforts will go, as converting these can be like trying to fit a round peg into a square hole.
The Transact-SQL CASE function CASE WHEN @a > @b THEN @a ELSE @b - @a END
This can be converted to the MySQL IF(expr1, expr2, expr3) function. Here’s how it works:
If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL), IF() returns expr2; otherwise it returns expr3.
if(@a>@b, @a, @b-@a)
Converting Binary Data into a Varchar
IN SQL SERVER 2008 the convert function was extended to support binary data to hex string conversion. Hence, you might see something like this:
CONVERT(NVARCHAR(34), 0xc23eed6b65c93e44a41a2818e274194f, 1) AS BINARY_TO_STRING
The MySQL BIN(N) function, which returns a string representation of the binary value of N, can be utilized in its stead.
The Transact-SQL DATALENGTH Function
This one is easy to convert because both the SQL Server DATALENGTH and MySQL BIT_LENGTH functions will return the length of a string in bits.
SQL Server does not support the ANSI SQL CONCAT() function. Instead, it uses the plus operator (+) for string concatenation:
In MySQL, use the CONCAT(str1, str2, ….) or CONCAT_WS(separator, str1, str2, ...) functions, which return the concatenated arguments:
Converting Numbers between Different Number Bases
It is sometimes useful to convert a number to a non-base 10 string. In SQL Server, that requires using the CAST function or employing a user-defined function. In MySQL, you can forget about all that, as the ANSI SQL CONV(N, from_base, to_base) function will allow you to convert from one base to another with ease.
Finding the Position of the First Occurrence of a Substring within a String
The Transact-SQL CHARINDEX function maps exactly to the ANSI SQL LOCATE() function.
Inserting a String within Another
In SQL Server, the REPLACE function can be used to replace part of a string with another. For instance, the following example replaces the string def in abcdefghi with xyz.
MySQL’s INSERT(str, pos, len, newstr) function is a reasonable facsimile, as it returns the string str, with the substring that begins at position pos and is len characters long replaced by the string newstr.
Loading Data and Statements from a File
T-SQL bulk load statements and extended stored procedures that load data and executable statements from a text file can be replace with LOAD_FILE(file_name) in MySQL.
Getting the Current Date
Transact-SQL’s NOW function maps to GETDATE in ANSI SQL.
Generating a Repeating String
Transact-SQL’s REPLICATE function maps exactly to REPEAT in ANSI SQL.
Testing for NULL
Transact-SQL relies on the CASE and IS NULL clauses to check for NULL values. In MySQL, you can simply use the ISNULL(expr) function instead. If expr is NULL, ISNULL() returns 1; otherwise it returns 0.
Comparing Two Strings
Transact-SQL relies on comparison operators to compare strings, whereas ANSI SQL provides the STRCMP(expr1, expr2) function.
While Transact-SQL uses a combination of date, string, and convert functions to format dates as strings, ANSI SQL has the built-in DATE_FORMAT(date, format) function specifically for formatting dates.
Adding an Interval to a Given Date
The Transact-SQL DATEADD function does have equivalents in Oracle, DB2, and PostgreSQL. MySQL includes the same function, except that it’s called DATE_ADD:
SELECT DATE_ADD('2010-12-31 23:59:59', INTERVAL 1 DAY); returns '2011-01-01 23:59:59'
Converting between Seconds and a Time
In Transact-SQL, converting between seconds and a time such as 12:34:00 can be accomplished using a combination of the CONVERT and DATEADD functions. For instance, here is a statement that converts seconds to a time:
CONVERT(char(8), DATEADD(second, Diff, '0:00:00'), 108)
MySQL can convert between seconds and a time more easily using the SEC_TO_TIME(seconds) and TIME_TO_SEC(time) functions.
Retrieving the Last Inserted ID
The Transact-SQL @@IDENTITY and SCOPE_IDENTITY functions are used to retrieve the last inserted ID. MySQL possesses a similar function called LAST_INSERT_ID for this purpose.
Concatenating Column Values
To concatenate the contents of a column into a string requires a few steps in T-SQL:
declare @v varchar(max) set @v='' select @v=@v+','+isnull(field_a,'') from table_1 select substring(@v,2,len(@v))
It’s much easier in ANSI SQL, thanks to the GROUP_CONCAT function. It comes in two flavors to support
- GROUP_CONCAT( Language SEPARATOR ‘-’ ) will use the dash instead of the default comma separator.
- SELECT GROUP_CONCAT( Language ORDER BY Language DESC ) can be used to change the sorting order.
Note that GROUP_CONCAT ignores NULL values.
Any SQL Server-centric functions have to be either removed and/or rewritten using a combination of ANSI SQL statements. Once completed, the new code can be saved as a user-defined function for easy reuse.
There are purportedly some automated tools that can convert stored procedures between SQL Server and MySQL, such as SQLWays by Ispirer. According to their site and anecdotal reports, it converts stored procedures, functions, packages and triggers. All this automation doesn’t come cheap; at about a grand USD, it may be more cost effective to manually convert your procs.