Microsoft’s SQL Server is a really solid product, but it can be overkill for a lot of applications. Oracle picked up MySQL a while back. For many applications, that is a really viable alternative. Not only is MySQL a fully mature product with support available, it’s an option on most web hosting sites as well as major service providers. We use Amazon Web Services here. They offer MySQL support with lots of extra benefits such as replication across availability zones.
Having coded my app in SQL Server and then getting the direction to move it to MySQL, I was a little unsure how to go about it. Here’s what I learned from the process.
Generate the scripts for tables, views and stored procedures separately.
Generating the scripts is a fairly straightforward process
Modify the table script to run in MySQL
Modify the view script to run in MySQL
MySQL does not allow for nested select statements. Instead, you have to create separate views.
Modify the stored procedure script to run in MySQL
Here is an example of a stored procedure from SQLServer:
CREATE PROCEDURE [dbo].[ItemDAL_DeleteItem] @ItemId uniqueidentifier AS BEGIN SET NOCOUNT ON; DELETE FROM Items WHERE ItemId=@ItemId END
To convert it to a procedure MySQL can understand, we have to do a few things.
- Add the statement DELIMITER $$ to the beginning of the script
- Remove the SET ANSI_NULLS ON and SET QUOTED_IDENTIFIER ON statments
- Remove the GO statements
- Remove the [dbo] references and all square brackets
- Remove the SET NOCOUNT ON statement
- The parameters may not have the ‘at’ sign (@). I did a quick find + replace of all at signs with ‘ivar’. As such, the parameter @ItemId becomes ivarItemId.
- The parameters are included in parentheses as part of the procedure signature
- Make sure your statements end with a semi-colon
- The final END needs the $$ delimiter.
The converted stored procedure looks like this…
CREATE PROCEDURE ItemDAL_DeleteItem( ivarItemId varchar(36)) BEGIN DELETE FROM Items WHERE ItemId=ivarItemId; END$$
Other things to note…
- IF statements take the form of IF <<condition>> THEN <<code>> END IF;
- When declaring local variables, you have to declare them each on their own line.
- MySQL doesn’t have a UNIQUEIDENTIFIER datatype. Either user UUID() to generate one or a VARCHAR(36) to hold one that’s generated elsewhere.
- The inline comment code of two dashes does not work in MySQL
- MySQL doesn’t understand the TOP keyword. You can’t SELECT TOP 20 Name FROM Table. Instead, you have to LIMIT the results. The syntax would be SELECT Name FROM Table LIMIT 0,20
- There is no GETDATE() function. Instead use NOW()
- There is no GETUTCDATE() function. insted use UTC_TIMESTAMP()
- The DATEDIFF function is different on MySQL. Whereas you can do DATEDIFF(hour,Date1,GetDate()) on SQLServer, on MySQL, the proper syntax would be TIMESTAMPDIFF(hour,Date1,NOW())
- There is no DATEADD() function in MySQL. Instead, use TIMESTAMPADD().
In your code
If you’ve gotten this far, hopefully the rest of the changes should be pretty straightforward. I consolidate all my code into logical parts. Specifically, I have all my data calls in a Data Access Layer. The first thing to do (in C# projects anyway) is to include the MySQL.Data.DLL assembly in your project.
Next, add the MySQL.Data references to your code.
using System.Data; using MySql.Data; using MySql.Data.MySqlClient;
Now, all of the original SQL objects need to be changed over to MySQL objects. For example, a SqlConnection object becomes a MySqlConnection. This can be done with a simple “Find + Replace” operation. In my code, I had to change the following objects:
- SqlConnection –> MySqlConnection
- SqlCommand –> MySqlCommand
- SqlDataAdapter –> MySqlDataAdapter
In my code, I was specifying the schema on all my stored procedure commands. This is no longer needed and could cause problems. The old statement
SqlCommand cmd = new SqlCommand("mySite.UserDAL_GetPassword", con);
MySqlCommand cmd = new MySqlCommand("UserDAL_GetPassword", con);
This is easily done with a “Find + Replace” command as well. Just replace “dbo.” with “”.
Finally, the stored procedure parameters have been renamed. Remember? They need to be adjusted in code. Simply use the “Find + Replace” to change the “@” to “ivar”. Therefore: