Translate Microsoft SQL (MSSQL) To MySQL
Posted April 15, 2008 at 2:43 PM
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 »
- <!--- 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
Launch code in new window » Download code as text file »
- <!--- 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
Launch code in new window » Download code as text file »
- <!--- 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
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 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
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 )
- );
-
- <!--- 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...
Download Code Snippet ZIP File
Post Comment | Ask Ben | Permalink | Other Searches | Print Page
Newer Post
Update To MSSQL To MySQL Translation
Older Post
A Reminder About Interfaces And The User Experience
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. :)
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
@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.
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.
@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).
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. :)
One thing I wish translated well between the two:
MSSQL's TOP command and MySQL's LIMIT command.
@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.
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.
@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.
Select 3 as temp is correct for aliasing in MySQL
Also you can do select "randy" as firstname
-Randy
I tested SELECT (3) as temp and that worked fine, so I'm still wondering what the difference is.
@Randy,
I know aliasing works, but I think the parens were causing an error.
@davidcl,
I could be wrong. I will test it shortly.
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
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.
Thanks guys, I am going to be looking into this stuff at lunch, hopefully.
@ Tom, Dan
Using maxRows in the cfquery tag itself totally eliminates the need for TOP or LIMIT.
@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.
@david
It actually ain't that bad. :)
As blogged by Ben himself.
@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).
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. :/
