There are several things I love about MySQL compared to SQL Server and Oracle. Hopefully my memory is accurate on the SQL Server and Oracle support. I haven’t used Oracle in a few months and SQL Server in two years.
| Extended Inserts | |
|---|---|
| MySQL 5 | Supported |
| SQL Server 2005 | Not Supported |
| Oracle 10g | Not Supported |
| Linux/BSD support | |
| MySQL 5 | First class |
| SQL Server 2005 | None |
| Oracle 10g | First class |
Switch to another database in a single session (e.g. USE database) |
|
| MySQL 5 | As expected: use database per connection |
| SQL Server 2005 | As expected: use database per connection |
| Oracle 10g | Schema changes for all connections in the same threads so you have to call “ALTER SESSION SET CURRENT_SCHEMA = database” on before every query to be sure (or use a global singleton property to track which schema is current and alter session when needed). |
LIMIT max, offset
|
|
| MySQL 5 | Super useful |
| SQL Server 2005 | “SELECT TOP” with nested queries for offset |
| Oracle 10g | “SELECT * FROM (SELECT …) WHERE ROWNUM BETWEEN lowest AND highest
|
| Updated row count | |
| MySQL 5 | Number of rows actually changed |
| SQL Server 2005 | Number of rows matched in WHERE clause |
| Oracle 10g | Number of rows matched in WHERE clause |
| Unlimited size of results on limit clause | |
| MySQL 5 | SQL_CALC_FOUND_ROWS keyword |
| SQL Server 2005 | follow-up query needed: SELECT COUNT(*) FROM (SELECT …) |
| Oracle 10g | follow-up query needed: SELECT COUNT(*) FROM (SELECT …) |
| Bitwise Operators | |
| MySQL 5 | Default 64-bit support |
| SQL Server 2005 | No 64-bit support |
| Oracle 10g | ? |
| Command line client | |
| MySQL 5 | Very usable (e.g. /g switch is very helpful) |
| SQL Server 2005 | None. You’re stuck with Windows GUI. |
| Oracle 10g | SQL Plus. Clunky and minimal options |
| GUI tools | |
| MySQL 5 | Lots of great open source tools (phpMyAdmin, MySQL AB, etc. etc.) |
| SQL Server 2005 | Free SQL client. Clob data viewer is clunky |
| Oracle 10g | Free SQL Developer app. Third-party program needed to view Clob data (I hear support is coming) |
| Documentation and Support | |
| MySQL 5 | Comprehensive docs and comments online; online communities |
| SQL Server 2005 | Buy a book |
| Oracle 10g | Buy 2 books |
| GROUP CONCAT keyword | |
| MySQL 5 | Supported |
| SQL Server 2005 | Not Supported UPDATE: the “FOR XML PATH” construct can help: SELECT CustomerID AS ‘data()’ FROM Customer FOR XML PATH(”) |
| Oracle 10g | Not Supported |
| ON DUPLICATE KEY UPDATE | |
| MySQL 5 | Super useful |
| SQL Server 2005 | IF EXISTS (…) BEGIN UPDATE … SET … END / IF NOT EXISTS (…) BEGIN INSERT (…) VALUES (…) END |
| Oracle 10g | IF EXISTS (…) BEGIN UPDATE … SET … END / IF NOT EXISTS (…) BEGIN INSERT (…) VALUES (…) END |
| Cost | |
| MySQL 5 | Free License |
| SQL Server 2005 | Expensive |
| Oracle 10g | Painfully Expensive |
| Native Datetime Format | |
| MySQL 5 | Normal general-to-specific format (2005-07-15 11:30:52) |
| SQL Server 2005 | Normal general-to-specific format (2005-07-15 11:30:52) |
| Oracle 10g | Queries, updates, inserts default to chicago format “15-JUL-05 11.30.52.000000 AM” so you need to set NLS_DATE_FORMAT or TO_CHAR for other formats |
| Date Subtraction/Manipulation | |
| MySQL 5 | SELECT TIMESTAMPDIFF(SECOND,’2009-07-01′,’2009-07-04 20:05:00′) / (24 * 60 * 60) as ‘Days Between’ |
| SQL Server 2005 | ? |
| Oracle 10g | SELECT TO_DATE(‘2009-07-04 20:05:00′,’DD.MM.YYYY:HH24:MI:SS’) – TO_DATE(‘2009-07-01′,’DD.MM.YYYY:HH24:MI:SS’) as ‘Days Between’ |
| Programmatically reading schema (e.g. to alter schema) | |
| MySQL 5 | DESCRIBE table_name
|
| SQL Server 2005 | Complex managerie of queries to various system tables |
| Oracle 10g | Painfully complex managerie of queries to various system tables |
On the flip side, Oracle and SQL Server have much more robust backup and clustering support.