Going from MS SQL to MySQL - How difficult?

[Robinho]

I'm solid at SQL development but i've not used MySQL before. There's a role i'm considering going to an interview for which is after MySQL.

I'm a ASP.net developer so i would probably doing things like general queries, SPs, creating tables perhaps, maybe even indexing. As well as possibly some ADO commands.

How long do you think it would take to pick these things up? I know the syntaxes should be similar but what about the interface of MySQL, is it quite similar? In addition how does each one behave under the bonnet in regards to the query planner etc?

Thanks

[stek]

Download it and try it.

[PAH]

Does MySQL have proper stored procs etc now?

Last looked at it a few years ago and the limitations over MSSQL put me off so I used the free version of MSSQL instead.

I expect one of the biggest differences would be the SQL standards used between them. Not sure if MySQL is closer to ANSI standard than MS's Transact SQL, it's been a long time since I used 'pure' SQL so expect MS to have done the usual 'extend, improve, and bastardise' they do to all standards they pretend to be interested in adhering to.

I'd do some web searches on the differences to identify which areas of MySQL you may need to spend most time learning.

[TheFaQQer]

Each has their own way of doing certain things.

IIRC, MS SQL has things like being able to read uncommitted transactions and also to create a temporary table dynamically in code, which is something that MySQL doesn't do for good reason.

Download it, or install it on a website and have a play around and see what you think. I only use MySQL on the internet so use PHP MyAdmin as the interface to it - I don't know about any other management / CLI to access it.

[TheFaQQer]

Does MySQL have proper stored procs etc now?

Stored procedures / routines came in MySQL 5.0

[doodab]

Each has their own way of doing certain things.

IIRC, MS SQL has things like being able to read uncommitted transactions and also to create a temporary table dynamically in code, which is something that MySQL doesn't do for good reason.

Download it, or install it on a website and have a play around and see what you think. I only use MySQL on the internet so use PHP MyAdmin as the interface to it - I don't know about any other management / CLI to access it.

It has a command line tool as well. It doesn't have anything like the SQL Server GUI tools, at least not that I am aware of. You can use things like the eclipse database tools although they aren't the greatest.

[Durbs]

How long do you think it would take to pick these things up? I know the syntaxes should be similar but what about the interface of MySQL, is it quite similar?

If you are used to SQL Server then MySQL should be straightforward, use it myself for various sites and blogs and never found I had to 'learn' anything really, it was all pretty self explanatory and is well documented should you become stuck. As said, just download and play, there are loads of management GUI's to choose from but i've mainly stuck to phpMyAdmin or MySQL's own thick client management tools (MySQL Administrator or MySQL Workbench).

I was always (and still am) a SQL Server chap but for the past 5-6 years have worked mainly with Oracle and found that was more difficult to transition to as a lot of it is very different to SQL but didn't find that with MySQL.

[Robinho]

Cheers guys.

[Spacecadet]

Each has their own way of doing certain things.

IIRC, MS SQL has things like being able to read uncommitted transactions and also to create a temporary table dynamically in code, which is something that MySQL doesn't do for good reason.

what good reason is that?

[TheFaQQer]

what good reason is that?

Reading uncommitted data is a bad idea. The only time you can rely on uncommitted data is within the same session before you either commit it or roll back. If there are other sessions which are relying on your uncommitted data, which is then rolled back, you need to be careful about what users are presented with - are they going to see data that never gets committed? If that uncommitted data is then used to make decisions (either programmatically or manually) then that decision is based on duff data. MySQL will read the uncommitted data from your own session, but cross-session uncommitted reads are a bad idea.

I don't agree with defining tables on the fly - define the schema, document the schema, code. Define a temporary table in the schema and use that, rather than creating and dropping objects in code. MySQL doesn't allow it for this very reason.