Translate Microsoft SQL (MSSQL) To MySQL : Kinky Solutions : A Student's Perspective by Ben Nadel

Translate Microsoft SQL (MSSQL) To MySQL

Posted April 15, 2008 at 2:43 PM

Tags: SQL

This is going to be a post that I update from time to time and I just wanted to kick if off right now. For years, I have been working on Microsoft SQL Server and I have reached what I think is a descent level of competence in it. As of late, however, I have been doing a lot of work in MySQL. While MySQL is a good database server (from what I have heard), I am having trouble translating all of my MSSQL Server knowledge into MySQL scripts (ex. Declaring temporary tables). Below, I will compile my findings and translations as I come across them.

Some things that pop into my mind that I know how to easily do in MS SQL but am not sure of how to accomplish in MySQL 5:

Declaring temporary variables / tables
Updating / Deleting queries using JOINs
Running multiple statements in one query
IF / ELSE / END logic
Getting the previous created Identity value
Inline view / temp table creation
Running transactions
Creating column aliases

More to come soon...

Drop Table If It Exists

As we kick off our examples, we want to drop our test tables if and only if they exist (I am running these tests over and over during development, so it is important that we only DROP if they table exists).

MS SQL Server 2005

Launch code in new window » Download code as text file »

IF EXISTS (
SELECT
1
FROM
sys.objects
WHERE
object_id = OBJECT_ID( 't_actress' )
AND
type = ( 'U' )
)
BEGIN
DROP
TABLE t_actress
;
END
IF EXISTS (
SELECT
1
FROM
sys.objects
WHERE
object_id = OBJECT_ID( 't_movie' )
AND
type = ( 'U' )
)
BEGIN
DROP TABLE
t_movie
;
END
IF EXISTS (
SELECT
1
FROM
sys.objects
WHERE
object_id = OBJECT_ID( 't_actress_movie_jn' )
AND
type = ( 'U' )
)
BEGIN
DROP TABLE
t_actress_movie_jn
;
END

MySQL 5

Coming soon...

Create Tables

Now that we have dropped our test tables, it's time to re-create them for our testing.

MS SQL Server 2005

Launch code in new window » Download code as text file »

CREATE TABLE t_actress (
id INT,
name VARCHAR( 30 ),
hair VARCHAR( 10 ),
birthday DATETIME,
is_hot TINYINT,
is_mega_hot TINYINT
);
CREATE TABLE t_movie (
id INT,
name VARCHAR( 30 )
);
CREATE TABLE t_actress_movie_jn (
actress_id INT,
movie_id INT,
is_naked TINYINT
);

MySQL 5

Coming soon...

Populate Our Tables

Once the tables have been created, we have to populate them with test data.

MS SQL Server 2005

Launch code in new window » Download code as text file »

INSERT INTO t_actress (
id,
name,
hair,
birthday,
is_hot,
is_mega_hot
)(
(
SELECT
1,
'Christina Cox',
'Brunette',
'1971-07-31',
1,
1
)
UNION ALL
(
SELECT
2,
'Angela Bassett',
'Black',
'1958-08-16',
1,
0
)
UNION ALL
(
SELECT
3,
'Sharon Stone',
'Blonde',
'1958-03-10',
1,
0
)
);
INSERT INTO t_movie (
id,
name
)(
(
SELECT
1,
'The Chronicles of Riddick'
)
UNION ALL
(
SELECT
2,
'Better Than Chocolate'
)
UNION ALL
(
SELECT
3,
'Strange Days'
)
UNION ALL
(
SELECT
4,
'How Stella Got Her Groove Back'
)
UNION ALL
(
SELECT
5,
'The Muse'
)
UNION ALL
(
SELECT
6,
'Total Recall'
)
);
INSERT INTO t_actress_movie_jn (
actress_id,
movie_id,
is_naked
)(
(
SELECT
1,
1,
0
)
UNION ALL
(
SELECT
1,
2,
1
)
UNION ALL
(
SELECT
2,
3,
0
)
UNION ALL
(
SELECT
2,
4,
0
)
UNION ALL
(
SELECT
3,
5,
1
)
UNION ALL
(
SELECT
3,
6,
0
)
);

MySQL 5

Coming soon...

Update A Table Using JOIN Clauses And Table Aliases

Most of the time, our update statements are very simple; but, sometimes, we want to update tables based on their relationship to other tables. This can be easily done with the use of JOIN clauses and table aliasing.

MS SQL Server 2005

Launch code in new window » Download code as text file »

<!---
Update the "is_mega_hot" rating to include actresses
who were already hot AND have gotten nudie in at least
one movie.
--->
UPDATE
a
SET
a.is_mega_hot = 1
FROM
t_actress a
INNER JOIN
t_actress_movie_jn amjn
ON
(
a.is_hot = 1
AND
a.id = amjn.actress_id
AND
amjn.is_naked = 1
)

MySQL 5

Coming soon...

Delete A Table Using JOIN Clauses And Table Aliases

Most of the time, our delete statements are very simple; but, sometimes, we want to delete a record or records from a table based on their relationship to other tables. This can easily be done with the use of JOIN clauases and table aliasing.

MS SQL Server 2005

Launch code in new window » Download code as text file »

DELETE
m
FROM
t_movie m
INNER JOIN
t_actress_movie_jn amjn
ON
(
m.id = amjn.movie_id
AND
amjn.is_naked = 0
)

MySQL 5

Coming soon...

Declaring And Populating Temporary Table Variables

Sometimes, I like to take a long, complicated SQL algorithm and break it down into smaller, more management steps. To do this, I can create a temporary table variable to hold intermediary result sets which I can then query and JOIN to in later steps of my algorithm.

MS SQL Server 2005

Launch code in new window » Download code as text file »

<!---
Create a temporary table to hold hot girls and
the movies in which they got naked.
--->
DECLARE @hot_girls TABLE (
girl_name VARCHAR( 30 ),
movie_name VARCHAR( 30 )
);
INSERT INTO @hot_girls (
girl_name,
movie_name
)(
SELECT
( a.name ) AS girl_name,
( m.name ) AS movie_name
FROM
t_actress a
INNER JOIN
t_actress_movie_jn amjn
ON
(
a.is_hot = 1
AND
a.id = amjn.actress_id
AND
amjn.is_naked = 1
)
INNER JOIN
t_movie m
ON
amjn.movie_id = m.id
);

MySQL 5

Coming soon...

Download Code Snippet ZIP File

Related Blog Entries

Update To MSSQL To MySQL Translation

A Reminder About Interfaces And The User Experience

Keep your Web site content fresh and your overhead costs low with Savvy Content Manager

Reader Comments

Funny but today i started to look into ways to export tables/data/stored procedures/etc from MS SQL to MySQL as well in my try to make 1ssBlog work with MySQL as well. :)

Posted by Ed on Apr 15, 2008 at 3:03 PM

This is good. It'll be my own reference, too. Like a public google.com/notebook.

Temporary Tables in MySQL5: http://dev.mysql.com/doc/refman/5.0/en/create-table.html

Posted by David Buhler on Apr 15, 2008 at 3:07 PM

@David,

I am not sure that that create table page is what I am talking about. It looks like that creates an actual table (with create permissions required). I am talking about just a table-variable (like an INT, or a FLOAT, but a table). I think, but could be dead wrong, that these are two different concepts.

Posted by Ben Nadel on Apr 15, 2008 at 3:31 PM

Ben, it's a fantastic idea to gather a "translation" list of different terms between MS-SQL and mySQL. I've always wondered whether you could ever put something like that into code so that you could create dynamic queries which would be translated into either format... but, my guess would be that the respective syntaxes are so different as to make that implausible.

Posted by Tom Mollerus on Apr 15, 2008 at 3:49 PM

@Tom,

Yeah, this is not so much going to be a programmatic translation as it is just collection of parallel examples in both syntaxes (if that can be done).

Posted by Ben Nadel on Apr 15, 2008 at 3:58 PM

IIRC, O'Reilly's "SQL in a Nutshell" covers a lot of the syntax differences between databases.

http://www.oreilly.com/catalog/sqlnut2/?CMP=AFC-ak_book&ATT=SQL+in+a+Nutshell

re: Running multiple statements in one query

How to Combine Multiple SQL Statements in One CFQUERY
http://www.sumoc.com/blog/index.cfm/2005/12/30/MySQL-How-to-Combine-Multiple-SQL-Statements-in-One-CFQUERY

re: Getting the previous created Identity value

SELECT LAST_INSERT_ID()

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

re: Creating column aliases

Same as anywhere else

SELECT someColumn AS myColumn

FWIW, I know one of the guys on the MySQL documentation team. I can track down virtually anything. :)

Posted by Adrian J. Moreno on Apr 15, 2008 at 4:07 PM

One thing I wish translated well between the two:

MSSQL's TOP command and MySQL's LIMIT command.

Posted by Dan Sorensen on Apr 15, 2008 at 4:44 PM

@Dan,

Yeah, TOP and LIMIT are placed at opposite ends of the query. I wish that MS-SQL had LIMIT's ability to specify a starting place in the resultset, since it makes pagination so easy.

Posted by Tom Mollerus on Apr 15, 2008 at 5:07 PM

I don't think MySQL has a direct equivalent for MSSQL's table-valued variables. (These were new in SQL Server 2000, and they aren't a widely-implemented feature, as far as I know).

Update/Delete queries using joins -

MSSQL:

UPDATE a
SET foo = 'bar'
FROM a JOIN b ON a.baz = b.baz

MySQL:

UPDATE a JOIN b ON a.baz = b.baz
SET foo = 'bar'

Running multiple statements in one query batch: In addition to making the driver change that is mentioned in the post linked by @Adrian, in MySQL, they must be seperated by semicolons.

IF / ELSE / END logic - Do you mean in a stored procedure? The syntax is slightly different, but I don't know of any huge differences. I haven't used stored procedures in MySQL too much yet.

Transactions - biggest issue is you must be using a transaction table type. Then you use START TRANSACTION instead of BEGIN TRANSACTION. COMMIT and ROLLBACK work as in SQL Server.

Creating column aliases - I'm curious how you've found this to be different form MSSQL-- to my eye they are similar.

Posted by davidcl on Apr 15, 2008 at 5:17 PM

@Adrian,

I think you just became my new best friend :)

@davidcl,

"Creating column aliases - I'm curious how you've found this to be different form MSSQL-- to my eye they are similar."

I don't recall exactly, but I am pretty sure I ran into problems in MySQL that it didn't like this:

SELECT
( 3 ) AS temp

The parens were messing it up or something. I think I had to dumb it down to:

SELECT
3 AS temp

But, that could be wrong.

Posted by Ben Nadel on Apr 15, 2008 at 5:42 PM

Select 3 as temp is correct for aliasing in MySQL

Also you can do select "randy" as firstname

-Randy

Posted by randy on Apr 16, 2008 at 4:14 PM

I tested SELECT (3) as temp and that worked fine, so I'm still wondering what the difference is.

Posted by davidcl on Apr 16, 2008 at 4:17 PM

@Randy,

I know aliasing works, but I think the parens were causing an error.

@davidcl,

I could be wrong. I will test it shortly.

Posted by Ben Nadel on Apr 16, 2008 at 5:01 PM

Here is a nice summary of MS SQL temporary tables and table variable types. They are different.

http://www.sqlteam.com/article/temporary-tables

#tblname are local temp tables

##tblname are global temp tables

@tblname are variable tables - exist only in memory and do not need to be dropped

Posted by randy b on Apr 17, 2008 at 12:30 PM

Yep. I did some more searching on this and it doesn't look like there's an equivalent to @table variables in MySQL. The best recommendations I've found are:

Create temporary tables using the "memory" storage engine, to provide the speed advantages of memory storage.

Use the "create temporary table" privilege to separate low-privilege users from those who can create permanent tables.

Posted by davidcl on Apr 17, 2008 at 12:45 PM

Thanks guys, I am going to be looking into this stuff at lunch, hopefully.

Posted by Ben Nadel on Apr 17, 2008 at 12:47 PM

@ Tom, Dan

Using maxRows in the cfquery tag itself totally eliminates the need for TOP or LIMIT.

Posted by Will on Apr 18, 2008 at 11:32 PM

@Will, I strongly disagree. TOP and LIMIT place the limit on the sets returned by the database server. MAXROWS simply limits how much of the result Cold Fusion processes. If the unlimited query returns lots of rows, this can be a HUGE performance difference.

Posted by davidcl on Apr 19, 2008 at 12:20 PM

@david

It actually ain't that bad. :)

As blogged by Ben himself.

http://www.bennadel.com/blog/464-ColdFusion-CFQuery-MaxRows-Not-A-Bad-Compromise-When-TOP-Not-Available.htm

Posted by Will on Apr 19, 2008 at 4:16 PM

@Will, even a factor of 4 would be enough for me to recommend against MAXROWS. But I've rewritten code where the change from MAXROWS to TOP resulted in an improvement of a factor of 100.

One factor is how many rows are in the query.

Another factor is how the query optimizer handles any joins in the query. In some cases the database engine may be able to apply the TOP earlier in the join process, reducing the number of rows to be joined, which can have a great impact on performance.

In general-- the only place I would use MaxRows is in a query of queries. Use the database's limiting mechanism instead. (As an aside, it would be great if Coldfusion would implement MAXROWS by translating it to the appropriate language in the underlying SQL implementation).

Posted by davidcl on Apr 21, 2008 at 9:58 AM

Post Comment | Ask Ben

Translate Microsoft SQL (MSSQL) To MySQL : Kinky Solutions : A Student's Perspective by Ben Nadel