Mysql Update: Null + 1 is Null! Thu, Feb 12. 2009
You can't add to Null
Here's something about mysql create table definitions that can easily catch you if you aren't careful. Consider this table definition:
- CREATE TABLE screenshots (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, votes INT);
mysql> CREATE TABLE screenshots (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, votes INT);
Query OK, 0 rows affected (0.09 sec)
What the user wanted was a simple table to keep track of user submitted screen shots. Of course the real table had a submission date, and name column but I've omitted those in order to focus on what can happen when you allow a numeric column to have NULL values.
In the application in question, when a user votes for the screen shot they like, the system should "count" the vote, by increasing the values in the "votes" column by one. Initially the developer working on this application was trying to read the value from the database, and in a PHP web script, they would increment this value and take the result and set "votes" to be equal to it in an UPDATE statement. I explained that this could cause lost votes, because if two or more users were voting at nearly the same time, each would overwrite the value of the "vote" column. In fact there are scenarios far worse than that --- a user with a cached page could vote and set the vote count back days or weeks. I didn't bother to mention the possibility that someone might recognize what was going on in the web form, and start tampering with it, since it was plainly evident that the form was passing the current number of votes.
One of the many benefits of using a relational database is built in concurrency. In an UPDATE statement, you can add to the value of the column without having to know what its original value is, just as computer languages allow assignment to a variable that references the variable's current value (ie. $a = $a + 1, $a++).
- UPDATE screenshots SET votes = votes + 1 WHERE id =
All that's needed is to have the serverside language provide a value for a particular "id" and the votes will be tallied and updated correctly. Even more importantly, mysql will serialize the updates, insuring that no votes are lost.
However, given the original Mysql CREATE TABLE statement , what will happen if our code embeds the UPDATE statement provided?
« previous page
(Page 1 of 1, totaling 1 entries)
next page »
