MySQL Cookbook

11.17 Using Single-Row Sequence Generators

11.17.1 Problem

You're interested only in counting events, so there's no point in creating a record for each count.

11.17.2 Solution

Use a different sequence-generation mechanism that uses just one row.

11.17.3 Discussion

AUTO_INCREMENT columns are useful for generating sequences across a set of individual records. But for some applications, you're interested only in a count of the number of times an event occurs, and there's no value in creating a separate record for each event. Instances include web page or banner ad hit counters, a count of items sold, or the number of votes in a poll. For such applications, you need only a single record to hold the count as it changes over time. MySQL provides a mechanism for this that allows counts to be treated like AUTO_INCREMENT values so that you can not only increment the count, but retrieve the updated value easily.

To count a single type of event, you can use a trivial table with a single row and column. For example, if you're selling copies of a book named "Red Horse Hill," you can create and initialize a table to record sales for it like this:

CREATE TABLE red_horse_hill (copies INT UNSIGNED);
INSERT INTO red_horse_hill (copies) VALUES(0);

However, if you're selling multiple book titles, that method won't work so well. You certainly don't want to create a separate single-row table to count sales for each book. Instead, you can count them all within a single table if you include a column that provides a unique identifier for each book. The following table, booksales, does this using a title column for the book title in addition to a copies column that records the number of copies sold:

CREATE TABLE booksales
(
    title   VARCHAR(60) NOT NULL,   # book title
    copies  INT UNSIGNED NOT NULL,  # number of copies sold
    PRIMARY KEY (title)
);

Initialize the table by adding a row for each book:

mysql> INSERT INTO booksales (title) VALUES
    -> ('Red Horse Hill'),
    -> ('Sparkplug of the Hornets'),
    -> ('Bulldozer'),
    -> ('The Long Trains Roll'),
    -> ('Who Rides in the Dark?');
mysql> SELECT * FROM booksales;
+--------------------------+--------+
| title                    | copies |
+--------------------------+--------+
| The Long Trains Roll     |      0 |
| Bulldozer                |      0 |
| Sparkplug of the Hornets |      0 |
| Red Horse Hill           |      0 |
| Who Rides in the Dark?   |      0 |
+--------------------------+--------+

That sets up the table. Now, how do you use it? One way is to increment the copies column for a given book by issuing a simple UPDATE statement that names the book:

UPDATE booksales SET copies = copies+1 WHERE title = 'Bulldozer';

To retrieve the count (so that you can display a message to the customer such as "you just purchased copy n of this book," for example), issue a SELECT query for the same book title:

SELECT copies FROM booksales WHERE title = 'Bulldozer';

Unfortunately, this method doesn't really work properly. Suppose that during the time between the UPDATE and SELECT statements some other person buys a copy of the book (and thus increments the copies value). Then the SELECT statement won't actually produce the value you incremented the sales count to, but rather its most recent value. In other words, other clients can affect the value before you have time to retrieve it. This is similar to the problem discussed earlier that can occur if you try to retrieve the most recent AUTO_INCREMENT value from a column by invoking MAX(col_name) rather than LAST_INSERT_ID( ).

There are ways around this (such as by grouping the two statements as a transaction or by locking the table), but MySQL provides a different solution based on LAST_INSERT_ID( ). If you call LAST_INSERT_ID( ) with an expression argument, MySQL treats it like an AUTO_INCREMENT value.^[2] To use this feature for incrementing counters in the booksales table, modify the UPDATE statement slightly:

^[2] The LAST_INSERT_ID(expr) mechanism is available as of MySQL 3.22.9.

UPDATE booksales SET copies = LAST_INSERT_ID(copies+1)
WHERE title = 'Bulldozer';

Then you can invoke LAST_INSERT_ID( ) with no argument to retrieve the value:

SELECT LAST_INSERT_ID( );

By updating the copies column this way, you can always get back the value that you set it to, even if some other client has updated it in the meantime. If you're issuing the UPDATE statement from within an API that provides a mechanism for fetching the most recent AUTO_INCREMENT value directly, you need not even issue the SELECT query. For example, in Python, you can update a count and get the new value using the insert_id( ) method:

cursor = conn.cursor ( )
cursor.execute ("""
        UPDATE booksales SET copies = LAST_INSERT_ID(copies+1)
        WHERE title = 'Bulldozer'
    """)
count = cursor.insert_id ( )

In Java, the operation looks like this:

Statement s = conn.createStatement ( );
s.executeUpdate (
    "UPDATE booksales SET copies = LAST_INSERT_ID(copies+1)"
    + " WHERE title = 'Bulldozer'");
long count = ((com.mysql.jdbc.Statement) s).getLastInsertID ( );
s.close ( );

The use of LAST_INSERT_ID( ) for sequence generation has certain other properties that differ from true AUTO_INCREMENT sequences:

• AUTO_INCREMENT values increment by one each time, whereas counter values generated by LAST_INSERT_ID(expr) can be incremented by whatever value you want. For example, to produce the sequence 10, 20, 30, ..., increment the count by 10 each time. You need not even increment the counter by the same value each time. If you sell a dozen copies of a book rather than a single copy, update its sales count as follows:

  UPDATE booksales SET copies = LAST_INSERT_ID(copies+12)
  WHERE title = 'Bulldozer';

• You can start the sequence at any integer, including negative values. It's also possible to produce decreasing sequences by using a negative increment. (For a column that is used to generate a sequence that includes negative values, you would omit UNSIGNED from the column definition, of course.)

• To reset a counter, simply set it to the desired value. Suppose you want to report to book buyers the sales for the current month, rather than the total sales (for example, to display messages like "you're the nth buyer this month"). To clear the counters to zero at the beginning of each month, run this query:

  UPDATE booksales SET copies = 0;

• One property that's not so desirable is that the value generated by LAST_INSERT_ID(expr) is not available uniformly via client-side retrieval methods under all circumstances. You can get it after UPDATE or INSERT queries, but not for SET statements. If you generate a value as follows (in Perl), the client-side value returned by mysql_insertid will be 0, not 48:

  $dbh->do ("SET \@x = LAST_INSERT_ID(48)");
  $seq = $dbh->{mysql_insertid};

  To get the value in this case, ask the server for it:

  $seq = $dbh->selectrow_array ("SELECT LAST_INSERT_ID( )");

The single-row sequence-generation mechanism is revisited in Recipe 18.13, where it serves as the basis for implementing web page hit counters.