MS SQL Server to MySQL
When switching from MS SQL to MySQL, apart from data migration, you must also transfer the application code, which is in the database.
Earlier we discussed how to move MS SQL to a MySQL database using the WorkSQL Workbench tool.
Within the migration, it will only convert tables and copy data, but it will not convert triggers, views, and stored procedures. You must manually convert them to a MySQL database.
To perform this conversion manually, you must understand the basic differences between MS SQL and MySQL queries.
During my conversion from Microsoft SQL Server to a MySQL database, I encountered the following operators and MS SQL queries that were not MySQL compatible, and I had to convert them as shown below.
Creating stored procedures. Syntax
The basic syntax for creating stored procedures is different.
MS SQL Stored, procedure creation syntax:
CREATE PROCEDURE [dbo]. [storedProcedureName]
@someString VarChar(150)
As
BEGIN
-- Sql queries goes here
END
for MySQL procedure creation syntax:
CREATE PROCEDURE storedProcedureName( IN someString VarChar(150) )
BEGIN
-- Sql queries goes here
END
Time table creation
In MS SQL code, I have created several temporary tables that are required to apply. The syntax for creating a temporary table is different, as shown below.
MS SQL syntax for creating a temporary table:
CREATE TABLE #tableName(
emp_id VARCHAR(10)COLLATE Database_Default PRIMARY KEY,
emp_Name VARCHAR(50) COLLATE Database_Default,
emp_Code VARCHAR(30) COLLATE Database_Default,
emp_Department VARCHAR(30) COLLATE Database_Default
)
MySQL syntax for creating a temporary table:
CREATE TEMPORARY TABLE tableName(
emp_id VARCHAR(10),
emp_Name VARCHAR(50),
emp_Code VARCHAR(30),
emp_Department VARCHAR(30)
);
IF syntax
I used many conditions in my stored procedures and triggers that didn’t work after conversion to MySQL because the syntax is different as shown below.
MS SQL condition IF Syntax:
if(@intSomeVal='')
BEGIN
SET @intSomeVal=10
END
MySQL condition IF Syntax:
IF @intSomeVal='' THEN
SET @intSomeVal=10;
END IF;
IF EXIST status
Another common use, if a condition, is to check whether the query returns any lines or not; and if it returns multiple lines, do something. To do this, I used IF EXISTS in MS SQL, which must be converted to MySQL by the IF command as described below.
MS SQL IF EXITS Example:
IF EXISTS(SELECT 1 FROM #tableName WITH(NOLOCK) WHERE ColName='empType' )
BEGIN
-- Sql queries goes here
END
MySQL equivalent is higher, using when the condition is met:
IF(SELECT count(*) FROM tableName WHERE ColName='empType') > 0 THEN
-- Sql queries goes here
END IF;
Date functions
Using data functions within a stored procedure is quite common. The following table shows the differences between MS SQL and MySQL data, related functions.
MS SQL Server | MySQL Server |
---|---|
GETDATE( ) | NOW( ) SYSDATE( ) CURRENT_TIMESTAMP( ) |
GETDATE( ) + 1 | NOW( ) + INTERVAL 1 DAY CURRENT_TIMESTAMP +INTERVAL 1 DAY |
DATEADD(dd, -1, GETDATE()) | ADDDATE(NOW(), INTERVAL -1 DAY) |
CONVERT(VARCHAR(19),GETDATE()) | DATE_FORMAT(NOW(),’%b %d %Y %h:%i %p’) |
CONVERT(VARCHAR(10),GETDATE(),110) | DATE_FORMAT(NOW(),’%m-%d-%Y’) |
CONVERT(VARCHAR(24),GETDATE(),113) | DATE_FORMAT(NOW(),’%d %b %Y %T:%f’) |
CONVERT(VARCHAR(11),GETDATE(),6) | DATE_FORMAT(NOW(),’%d %b %y’) |
Announcement of variables
In MS SQL stored procedures, you can declare variables somewhere between “Begin” and “end”.
However, in MySql, you will have to declare them only after you declare the stored “begin” procedure. A declaration of a variable at any point between is not allowed.
Select the first N records
In MS SQL, you will use SELECT, TOP if you want to select only the first few records. For example, to select the 1st 10 records, you will do the following:
SELECT TOP 10 * FROM TABLE;
In MySQL, you will have to use LIMIT instead of TOP as shown below.
SELECT * FROM TABLE LIMIT 10;
Converting an integer number to Char
In MS SQL you will perform the following steps (Convert functions) to convert an integer to a character.
CONVERT(VARCHAR(50), someIntVal)
In MySQL, you will use the CAST function to convert an integer to a character, as shown below.
CAST( someIntVal as CHAR)
Concatenation operator
If you manipulate a lot of data inside a stored procedure, you can use some string concatenation execution.
In MS SQL the concatenation operator + character. An example of such usage is shown below.
SET @someString = '%|' + @someStringVal + '|%'
In MySQL, if you use the AnSi mode, it is the same as in MS SQL. i.e. + character will work for concatenation.
But, in the default MySQL mode, we have to use the CONCAT function (“str1”, “str2”, “str3″… “strN”).
SET someString = CONCAT('%|', someStringVal, '|%');
Enteros
About Enteros
IT organizations routinely spend days and weeks troubleshooting production database performance issues across multitudes of critical business systems. Fast and reliable resolution of database performance problems by Enteros enables businesses to generate and save millions of direct revenue, minimize waste of employees’ productivity, reduce the number of licenses, servers, and cloud resources and maximize the productivity of the application, database, and IT operations teams.
The views expressed on this blog are those of the author and do not necessarily reflect the opinions of Enteros Inc. This blog may contain links to the content of third-party sites. By providing such links, Enteros Inc. does not adopt, guarantee, approve, or endorse the information, views, or products available on such sites.
Are you interested in writing for Enteros’ Blog? Please send us a pitch!
RELATED POSTS
Optimizing Allocation Strategy and Cost Estimation on AWS EC2 with Enteros: Maximizing Efficiency and Performance
- 2 October 2024
- Database Performance Management
In today’s fast-paced, cloud-driven environment, businesses across various industries are increasingly relying on cloud infrastructure to optimize the…
Maximizing Database Optimization in Real Estate with Enteros and Cloud Resources
The real estate industry is rapidly evolving, with digital transformation becoming a driving force behind how companies manage data and make informed …
Optimizing Anomaly Management and Database Performance in the Real Estate Sector with Enteros: Enhancing Balance Sheet Accuracy
- 1 October 2024
- Technology Business
The real estate sector is highly dependent on accurate financial reporting, precise data analytics, and smooth database performance to ensure profitab…
Optimizing Database Performance in the Manufacturing Sector: Leveraging Enteros, Cloud FinOps, and AIOps Platforms
In the rapidly evolving manufacturing industry, digital transformation has become a necessity. Companies are leveraging advanced technologies to strea…