Marcus Young

Software. Microcontrollers. Beer.

Seriously MySQL?

| Comments

I recently discovered one of the most confusing things I’ve seen in MySQL.. Lets start with this table:

1
2
3
4
5
| Table | Create Table      
| blah  | CREATE TABLE 'blah' (
  'id' int(11) NOT NULL,
    'date' datetime NOT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

What this means is you have a required date. Let’s say this is to ENFORCE that date is there and valid (without a default value obviously).Now, what you can expect, is that you cannot force the column to take a default value…it must be specified, so this is what you’d expect to see:

1
2
mysql> insert into blah set id=1,date=NULL;
ERROR 1048 (23000): Column 'date' cannot be null

Cool…that’s right…so why does this work?

1
2
3
4
5
6
7
8
9
10
11
mysql> update blah set date=NULL where date is not null;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1
 
 mysql> select * from blah;
 +------+---------------------+
 | id   | date                |
 +------+---------------------+
 |    1 | 0000-00-00 00:00:00 |
 +------+---------------------+
 1 row in set (0.00 sec)

………Huh? It took on a default value, even specified as null?!If that doesn’t irritate you, what about this?

1
2
3
4
5
6
7
8
9
10
11
mysql> insert into blah set id=2;
Query OK, 1 row affected, 1 warning (0.00 sec)
 
 mysql> select * from blah;
 +------+---------------------+
 | id   | date                |
 +------+---------------------+
 |    1 | 0000-00-00 00:00:00 |
 |    2 | 0000-00-00 00:00:00 |
 +------+---------------------+
 2 rows in set (0.00 sec)

….So, I’ve specified that it cannot be NULL, but because datetime doesn’t actually have a NULL datatype, it accepts the NULL datetime of ’0000-00-00 00:00:00′. This is completely illogical. What’s worse, I specified NOT NULL, yet wrote an update to set date=NULL, and it passed. According to the MySQL 5 page, it’s because:

1
Illegal DATETIME, DATE, or TIMESTAMP values are converted to the “zero” value of the  appropriate type ('0000-00-00 00:00:00' or '0000-00-00').

That’s fine and dandy, but the MySQL is ignoring the fact that I set date=NULL where the schema shouldn’t allow it.

Comments