In the old country, before I came to US in 1988, I never saw a VCR. I vaguely heard of the concept, but never seen it nor knew anyone who had it. Given that I didn't know of a VCR, I didn't really have a desire to get one.
The reason I mention this, is because I spent most of my professional and hobby programming life in the Microsoft world and have been more or less oblivious to advances in parallel universes like MySQL or Linux or Mac or whatever.
As I've been porting Subtext to MySQL, it's been a learning experience. I've come to both marvel at MySQL's simplicity and tear my hair out at the maddening insanity of half-implemented features. I go back and forth between saying "SQL Server should have done this" and "SQL Server had this in 1997, why can't these guys". So first a look at the things MySQL has done right.
1. How many times have you written a script in MSSQL that goes something like this:
IF EXISTS (SELECT * FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'[subtext_FeedBack]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [subtext_FeedBack]
That's a lot of code to write just to drop a table. Here is the MySQL version:
DROP TABLE IF EXISTS `subtext_FeedBack`
Is that sweet of what? They have similar IF EXISTS constructs for sprocs, views, etc...
2. It is common to see code like before every stored procedure in the Microsoft world:
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
There is no need to do this in MySQL. Things like ANSI standards are actually standardized and there is one way to do things.
3. We give each table a unique name for a good reason: there are many of them. If we only had one, would we name it? Probably not. Table column can only have one DEFAULT value. So why does SQL Server insist on giving the DEFAULT constraint a name? Code like below:
IF NOT EXISTS
SELECT 1 FROM sysobjects
WHERE name = 'DF_subtext_Config_TimeZone' and type = 'D'
ALTER TABLE [subtext_Config] CONSTRAINT DF_subtext_Config_TimeZone
DEFAULT (0) FOR [TimeZone]
can be replaced with a one-liner in MySQL:
ALTER TABLE `subtext_Config` ALTER COLUMN `BlogGroup` SET DEFAULT 1;
4. In SQL Server there are a dozen paging techniques, but all of them are completely cockamamie compared to MySQL implementation. To return a page of data, just use the LIMIT keyword. Here is an example of how to return rows 6 through 15, you tell it how many rows to skip and how many rows to return:
SELECT * FROM tbl LIMIT 5,10
It's that simple.
5. Documentation. It's wiki style - e.g. users can add their own examples. This is huge for newbies, since they can see the real life examples. To be fair, MSSQL documentation page has also added wiki style semantics, but (a) they just started and there is barely anything there at this time and (b) msdn.microsoft.com pages take forever to load because they are so heavy.
1. The first thing that I found difficult to wrap my mind around is that you can't have variables or ANY procedural statements like IF, WHILE, etc... in scripts. I am going to let that sink in for a second...
But all of these language elements are available if you wrap your script with a stored procedure. What this means is that you can't simply whip up a quick, but more or less complicated, query and run it. You have to take the extra step and make it a sproc.
This is hugely annoying. Case and point, your upgrade scripts, as we know them in MS SQL, are simply not an option.
2. The query tools are pretty weak. The standard bearers of MySQL world are MySQL Query Browser and SQLYog. Both of these are good tools, but nowhere near as polished as the apps in the MSSQL world, like SQL Management Studio, SQL Edit or even 7 year old Query Analyzer.
MySQL Query Browser is particularly weird and powerful at the same time. It has 2 modes: Resultset and Script. In the Resultset mode you write one-liner queries that return results, while in the Script mode you do DDL style coding. Odd, that they couldn't provide the same canvas for both types of queries.
SQLYog is even more powerful, but it simply does not feel right. I couldn't put my finger on it, but I kept leaning towards the MySQL Query Browser.
3. MySQL has a ton of seemingly gratuitous limitations. Here is the list of things I've run into:
- The identity column must automatically be the primary key.
- Can't have more than one identity column per table.
- Can't have the identity column start at 0.
- Can't have more than one column in a table with CURRENT_TIMESTAMP (that's getdate() in the MSSQL world) default.
- Cannot have comments after the statement. For instance, following code won't compile:
SELECT * FROM MyTable; -- get all data from table
However, code below will compile
-- get all data from table
SELECT * FROM MyTable;
4. Inconsistent implementations. MySQL has a lot of them.
- For instance, pretty much all constructs can use the DROP [TABLE/VIEW/SPROC/ETC] [NAME] IF EXISTS. Except for INDEX. Why? I don't know. While they are fixing this bug, how about adding IF EXISTS to things ALTER TABLE MyTable DROP FOREIGN KEY IF EXISTS FK_MyTable.
- A couple of versions back, MySQL finally implemented subqueries. However, it is still not possible to update the table and select from the same table in the subquery. Example: UPDATE MyTable SET Name = 'a' WHERE ID in (SELECT ID FROM MyTable WHERE ID > 4);
- MySQL 5 has finally implemented support for stored procedures. However, there is no support for default parameters.
5. The LIMIT feature that I mentioned as one of the strength of MySQL has one nasty flaw. It does not take variables as parameters. So if your creating the query in a language like PHP or C#, it's all fine and dandy, but if you need it in the stored procedure, you are stuck doing dynamic SQL.
It sounds like MySQL could be a great RDBMS contender if they fixed a bunch of, what looks like, minor issues.