For a number of years Ian Barwick has maintained an excellent page called MySQL Gotchas. These are MySQL features, which do not work as expected according to either the SQL standard, or the way other common relational databases would do things. I have been sent the link to this page countless times, usually by somebody trying to persuade me I have made a horrible mistake by using MySQL, or that I shouldn't be suggesting MySQL in certain instances.

The list itself is not anti-MySQL, although it has often been used in that context, not only by people sending me the link, but more frequently in the sort of ill-informed forum comment that plagues cyberspace along the lines of:
MySQL sucks - rather use a real database like X. See MySQL Gotchas.
This article introduces some of the oddities to new readers, and examines some of the points raised in the section on general SQL.

NULLS

NULL values in a relational database have always been contentious. Much has been written about it (you can start with Fabian Pascal's critique at DBAzine), but in brief, many argue that permitting NULL's in a database was a mistake, and that the SQL standard is flawed in this respect. Nonetheless NULL's are here to stay, and are supposed to represent the absence of a value. Let's look at what MySQL does differently in this regard. Try this example:

mysql> CREATE TABLE t1 (
 i1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY
 i2 INT NOT NULL, 
 f1 VARCHAR(10) NOT NULL,
 f2 VARCHAR(10) NOT NULL DEFAULT 'nada', 
 ts TIMESTAMP NOT NULL
);

mysql> INSERT INTO t1 (i1) VALUES(1);

mysql> INSERT INTO t1 (i1,f1) VALUES(NULL,'something');

What would you expect to happen with these examples? In the first example, only the field i1 is supplied. Since the others are defined as NOT NULL, and only f2 has a defined default, you may expect the query to fail, since there are no supplied values for i2, f1 and ts.

Similarly, in the second example, we are attempting to pass NULL into i1, defined as NOT NULL and with no default, as well as not supplying values for i2, f2 and ts. However, both queries run successfully. Here is what is in the table.

mysql> SELECT * FROM t1;
+----+----+-----------+------+---------------------+
| i1 | i2 | f1        | f2   | ts                  |
+----+----+-----------+------+---------------------+
|  1 |  0 |           | nada | 2005-07-06 21:58:15 |
|  2 |  0 | something | nada | 2005-07-06 21:58:40 |
+----+----+-----------+------+---------------------+

The results will be surprising if you are more familiar with another DBMS. MySQL creates default values in certain instances if none are supplied. The default values here are '' (empty string) for the VARCHAR field, 0 for INT field i2 and the current date and time for the TIMESTAMP field ts. The INT field i1 is even more surprising. Although it was specifically assigned the value NULL in the second query, it still contains a value, 2. This is how the MySQL AUTO_INCREMENT sequence works. If the fields were defined as accepting NULLs, MySQL would have inserted a NULL. Instead, since they were defined as NOT NULL, MySQL uses alternative defaults.

There is an even more alarming case, to my mind, where the actual results of query are not consistent, even though ostensibly no data has changed. If you have not run any other queries since the two INSERT statements above, run these two queries below, one after another. Even though they are identical, the result changes.

mysql> SELECT * FROM t1 WHERE i1 IS NULL;
+----+----+-----------+------+---------------------+
| i1 | i2 | f1        | f2   | ts                  |
+----+----+-----------+------+---------------------+
|  2 |  0 | something | nada | 2005-07-06 21:58:40 |
+----+----+-----------+------+---------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t1 WHERE i1 IS NULL;
Empty set (0.00 sec) 

There is certainly no NULL value in i1, but MySQL justifies this exception as being of benefit to Access and Delphi (for determining the last AUTO_INCREMENT value) and possibly other ODBC applications. The full list of defaults MySQL uses vary according to the field type, as follows:

• For numeric fields except for those defined with AUTO_INCREMENT, the default is '0'.
• For numeric fields defined with AUTO_INCREMENT column, the default is the next value in the sequence.
• For string types except for ENUM, the default is an empty string.
• For the ENUM type, the default is the first enumeration value.
• For DATETIMEs, the default is '0000-00-00 00:00:00'
• For DATEs, the default is '0000-00-00'
• For TIMESTAMPs, the default is the current date and time.
• For TIMEs, the default is '00:00:00'
• For YEARs, the default is '0000'

Note that as of MySQL 5.0.2, this default behavior can be changed, and MySQL made to behave in the same way as standard SQL by setting one of the strict modes. I plan an article on this topic in future, but for now you can read more in the MySQL manual section about the Server SQL mode. You can also stop MySQL from accepting zero dates with the NO_ZERO_DATE SQL mode. Zero dates are not standard, and are automatically converted to NULL when using newer versions of MyODBC.