MySQL Help

[Trav2003]

Got my webhost (GoDaddy) paid for and am setting up my site's database.

I've got experience with oracle and SQL Server (which I do have 2 allowable database). I'm pretty familiar with SQL Server and am contemplating just going with a SQL Server DB for this project, but I've been screwing around with MySQL and had a few questions:

1) What are some general differences/problems I may need to know?
2) What are the benefits/drawbacks of the different storage engines? I have the option for InnoDB, MyISAM, MEMORY, EXAMPLE, ARCHIVE, CSV, FEDERATED, MRG_ISAM

[eshbach]

Got my webhost (GoDaddy) paid for and am setting up my site's database.

I've got experience with oracle and SQL Server (which I do have 2 allowable database). I'm pretty familiar with SQL Server and am contemplating just going with a SQL Server DB for this project, but I've been screwing around with MySQL and had a few questions:

1) What are some general differences/problems I may need to know?
2) What are the benefits/drawbacks of the different storage engines? I have the option for InnoDB, MyISAM, MEMORY, EXAMPLE, ARCHIVE, CSV, FEDERATED, MRG_ISAM

Honestly, I know of no reason to use MySql over SQL Server on Windows except that it's free, and since you already have SQL Server I'd go with that.

There are some minor syntax differences (obviously MySQL doesn't support Microsoft's T-SQL langauge). Some common things I've run across are:

"SELECT TOP n" in SQL Server is replaced by "LIMIT N" at the end of a query in MySQL.

"SELECT -- INTO TABLE" in SQL Server is not supported in MySQL, instead one must do something like "DROP IF EXISTS table; CREATE TABLE table SELECT -- ;"

But these kinds of differences aren't really a big deal. Some things that have been a problem (such as lack of stored procedures!) have been added to MySql in the latest versions, but other things are still missing (like real transactions). MySQL has some problems like transactions losing scope if the lock table fills up that you won't run into with SQL Server.

Choice of language is also a factor to consider. If you're using .NET, there are some nice things you can do with SQL Server that aren't possible with MySQL, like SQLBulkCopy. Also, SQL Server tends to perform better with the .NET environment than any other database (where in other languages MySQL might have an advantage).

[Trav2003]

Honestly, I know of no reason to use MySql over SQL Server on Windows except that it's free, and since you already have SQL Server I'd go with that.

There are some minor syntax differences (obviously MySQL doesn't support Microsoft's T-SQL langauge). Some common things I've run across are:

"SELECT TOP n" in SQL Server is replaced by "LIMIT N" at the end of a query in MySQL.

"SELECT -- INTO TABLE" in SQL Server is not supported in MySQL, instead one must do something like "DROP IF EXISTS table; CREATE TABLE table SELECT -- ;"

But these kinds of differences aren't really a big deal. Some things that have been a problem (such as lack of stored procedures!) have been added to MySql in the latest versions, but other things are still missing (like real transactions). MySQL has some problems like transactions losing scope if the lock table fills up that you won't run into with SQL Server.

Choice of language is also a factor to consider. If you're using .NET, there are some nice things you can do with SQL Server that aren't possible with MySQL, like SQLBulkCopy. Also, SQL Server tends to perform better with the .NET environment than any other database (where in other languages MySQL might have an advantage).

Looks like I might as well stick with SQL Server then. I'm quite familiar with it (T-SQL, stored procedures, bulk copy, etc - from work).

Out of curiousity, if anyone knows, what are the management tools available over the web (ie, how good is Godaddys SQL Server management?)

I'm used to use SQL Management Studio and its quite impressive - and I know the web interface will be quite limited, but how limited is it?

[eshbach]

Looks like I might as well stick with SQL Server then. I'm quite familiar with it (T-SQL, stored procedures, bulk copy, etc - from work).

Out of curiousity, if anyone knows, what are the management tools available over the web (ie, how good is Godaddys SQL Server management?)

I'm used to use SQL Management Studio and its quite impressive - and I know the web interface will be quite limited, but how limited is it?

it's not terribly limited, but it could certainly be better. It's about on-par or a bit above PHP MyAdmin if you're familiar with that.

Also, today at work I had to add a user defined function to MySQL, and it was a pain. Even though I had the function defined as "RETURNS STRING" and the C code doing the work was returning a char*, MySQL was treating the results as longblob, meaning it needed cast to a char every time :confused: