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.