So I came across an interesting quirk in MySQL the other day. Let’s say you have a table schema and some values that look like this:
+-------------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+------------------+------+-----+---------+-------+
| page_id | varchar(30) | YES | | NULL | |
| clicks | int(10) unsigned | YES | | NULL | |
+-------------------+------------------+------+-----+---------+-------+
+---------+--------+
| page_id | clicks |
+---------+--------+
| 1 | NULL |
+---------+--------+And then let’s say you pass the following SQL statement to MySQL:
update page_click_count set clicks = clicks + 1 where page_id=1;If you come from a loosely-typed language such as PHP, you would probably expect clicks for page_id 1 to now be 1. But that’s not the case in MySQL. After the query is run, the table will still look like this:
+---------+--------+
| page_id | clicks |
+---------+--------+
| 1 | NULL |
+---------+--------+Not only does the query fail, but it fails with no warnings given. It appears that mathematical operations on null values silently fail.
There are a couple of ways around this. The first and most obvious is to set NOT NULL and a default value on the column. In the example above, this would work. The NULL value in that field becomes a 0 and you can to normal mathematical operations on it. But what happens if, for whatever reason, you can’t do that? We actually have this situation in a few places at dealnews, where NULL represents a distinct value of that field that is different from 0. In this case, you can use COALESCE() to fill in the appropriate value for the field.
update page_click_count set clicks = coalesce(clicks, 0) + 1 where page_id=1;Edit: Brian Moon informs me that this is actually part of the SQL specification. So hooray for specifications. Still, it’s kind of arcane; in working with MySQL (and PHP) for a decade now, this is the first time I’ve ever actually encountered this. Hopefully this helps someone who was as confused as I was.
