Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at cf.Objective() 2011 (Minneapolis, MN) with:

Translate Microsoft SQL (MSSQL) To MySQL

By Ben Nadel on
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

  • <!--- Drop our testing tables if they exist. --->
  • IF EXISTS (
  • SELECT
  • 1
  • FROM
  • sys.objects
  • WHERE
  • object_id = OBJECT_ID( 't_actress' )
  • AND
  • type = ( 'U' )
  • )
  • BEGIN
  • DROP
  • TABLE t_actress
  • ;
  • END
  •  
  • <!--- Drop our testing tables if they exist. --->
  • IF EXISTS (
  • SELECT
  • 1
  • FROM
  • sys.objects
  • WHERE
  • object_id = OBJECT_ID( 't_movie' )
  • AND
  • type = ( 'U' )
  • )
  • BEGIN
  • DROP TABLE
  • t_movie
  • ;
  • END
  •  
  • <!--- Drop our testing tables if they exist. --->
  • 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

  • <!--- Create tables. --->
  • 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

  • <!--- Populate the temporary tables. --->
  • 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
  • )
  • );
  •  
  •  
  • <!--- Populate the temporary tables. --->
  • 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'
  • )
  • );
  •  
  •  
  • <!--- Populate the temporary tables. --->
  • INSERT INTO t_actress_movie_jn (
  • actress_id,
  • movie_id,
  • is_naked
  • )(
  • (
  • SELECT
  • 1, <!--- Christina Cox. --->
  • 1, <!--- The Chronicles of Riddick. --->
  • 0 <!--- NOT naked. --->
  • )
  •  
  • UNION ALL
  •  
  • (
  • SELECT
  • 1, <!--- Christina Cox. --->
  • 2, <!--- Better Than Chocolate. --->
  • 1 <!--- Woohoo, naked. --->
  • )
  •  
  • UNION ALL
  •  
  • (
  • SELECT
  • 2, <!--- Angela Bassett --->
  • 3, <!--- Strange Days. --->
  • 0 <!--- NOT naked. --->
  • )
  •  
  • UNION ALL
  •  
  • (
  • SELECT
  • 2, <!--- Angela Bassett --->
  • 4, <!--- How Stella Got Her Groove Back. --->
  • 0 <!--- NOT naked. --->
  • )
  •  
  • UNION ALL
  •  
  • (
  • SELECT
  • 3, <!--- Sharon Stone. --->
  • 5, <!--- The Muse. --->
  • 1 <!--- Woohoo, naked. --->
  • )
  •  
  • UNION ALL
  •  
  • (
  • SELECT
  • 3, <!--- Sharon Stone. --->
  • 6, <!--- Total Recall. --->
  • 0 <!--- NOT naked. --->
  • )
  • );

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

  • <!---
  • 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

  • <!--- Delete all the movies that don't have nudity. --->
  • 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

  • <!---
  • 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 )
  • );
  •  
  • <!--- Populate the hot girls table. --->
  • 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...




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. :)

Reply to this Comment

@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.

Reply to this Comment

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.

Reply to this Comment

@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).

Reply to this Comment

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. :)

Reply to this Comment

@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.

Reply to this Comment

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.

Reply to this Comment

@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.

Reply to this Comment

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

Reply to this Comment

@Randy,

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

@davidcl,

I could be wrong. I will test it shortly.

Reply to this Comment

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.

Reply to this Comment

@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.

Reply to this Comment

@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).

Reply to this Comment

For the "drop table if exists", you can use the INFORMATION_SCHEMA schema that is supported in both MSSQL and MySQL:

if exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 't_actress') ...

I actually came across a recommendation in an article somewhere in Microsoft's own documentation that advised using INFORMATION_SCHEMA instead of sys_objects for several reasons:

* it's portable (it's part of the SQL-92 standard, supported by MySQL, MSSQL, and PostGreSQL, but not Oracle -- but neither is sys_objects in Oracle, so there you go)
* it's more intuitive ("TABLES" and "TABLE_NAME" is much more explicit and self-documenting than "type='U'" and OBJECT_ID())
* the sys_* tables are internal to SQL Server and not guaranteed not to change with service packs/new versions (your code may break on a different version of SQL)

Of course, it's hard to argue all of this when MS's own tools generate code based on the sys_objects table. :/

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.