MySQL Oddities

Introduction

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.

Ian Gilfillan
Ian Gilfillan
Ian Gilfillan lives in Cape Town, South Africa. He is the author of the book 'Mastering MySQL 4', published by Sybex, and has been working with MySQL since 1997. These days he develops mainly in PHP and MySQL, although confesses to starting out with BASIC and COBOL, way back when, and still has a soft spot for Perl. He developed South Africa's first online grocery store, and has developed and taught internet development and other technical courses for various institutions. He has majors in Programming and Information Systems, as well as English and Philosophy. For 5 years he was Lead Developer and IT Manager for Independent Online, South Africa's premier news portal. However, he has now 'retired' from fulltime work, and is hoping that his next book will be more in the style of William Blake and Allen Ginsberg.

Latest Articles