Recipe 11.14. Using Sequence Generators as Counters

Problem

You're interested only in counting events, so there's no point in creating a table row for each sequence value.

Solution

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

Discussion

AUTO_INCREMENT columns are useful for generating sequences across a set of individual rows. 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 row 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 row to hold the count as it changes over time. MySQL provides a mechanism for this that enables 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, you can create a table to record sales for it like this:

CREATE TABLE booksales (copies INT UNSIGNED);

However, if you're counting sales for multiple book titles, that method won't work so well. You certainly don't want to create a separate single-row counting table per 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) );

To record sales for a given book, different approaches are possible:

Initialize a row for the book with a copies value of 0:
```
INSERT INTO booksales (title,copies) VALUES('The Greater Trumps',0); 
```
Then increment the copies value for each sale:
```
UPDATE booksales SET copies = copies+1 WHERE title = 'The Greater Trumps'; 
```
This method requires that you remember to initialize a row for each book or the UPDATE will fail.
Use INSERT with ON DUPLICATE KEY UPDATE, which initializes the row with a count of 1 for the first sale and increments the count for subsequent sales:
```
INSERT INTO booksales (title,copies)   VALUES('The Greater Trumps',1)   ON DUPLICATE KEY UPDATE copies = copies+1; 
```
This is simpler because you can use the same statement for initializing and updating the sales count.

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

SELECT copies FROM booksales WHERE title = 'The Greater Trumps';

Unfortunately, this is not quite correct. Suppose that between the times when you update and retrieve the count, 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. To use this feature with the booksales table, modify the count-incrementing statement slightly:

INSERT INTO booksales (title,copies)   VALUES('The Greater Trumps',LAST_INSERT_ID(1))   ON DUPLICATE KEY UPDATE copies = LAST_INSERT_ID(copies+1);

The statement uses the LAST_INSERT_ID( expr ) construct both to initialize and to increment the count. With an expression argument to LAST_INSERT_ID⁠(⁠ ⁠ ⁠), MySQL treats the expression like an AUTO_INCREMENT value. Then you can invoke LAST_INSERT_ID⁠(⁠ ⁠ ⁠) with no argument to retrieve the value:

SELECT LAST_INSERT_ID();

By setting and retrieving 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 INSERT 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 ("""                 INSERT INTO booksales (title,copies)                 VALUES('The Greater Trumps',LAST_INSERT_ID(1))                 ON DUPLICATE KEY UPDATE copies = LAST_INSERT_ID(copies+1)               """) count = conn.insert_id ()

In Java, the operation looks like this:

Statement s = conn.createStatement (); s.executeUpdate (       "INSERT INTO booksales (title,copies)"     + " VALUES('The Greater Trumps',LAST_INSERT_ID(1))"     + " ON DUPLICATE KEY UPDATE copies = LAST_INSERT_ID(copies+1)"); long count = ((com.mysql.jdbc.Statement) s).getLastInsertID (); s.close ();

The use of LAST_INSERT_ID( expr ) 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:
```
INSERT INTO booksales (title,copies)   VALUES('The Greater Trumps',LAST_INSERT_ID(12))   ON DUPLICATE KEY UPDATE copies = LAST_INSERT_ID(copies+12); 
```
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 should omit UNSIGNED from the column definition.)
To reset a counter, simply set it to the desired value. Suppose that 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 statement:
```
UPDATE booksales SET copies = 0; 
```
One property that's not so desirable is that the value generated by LAST_INSERT_ID⁠( expr ) is not uniformly available via client-side retrieval methods under all circumstances. You can get it after UPDATE or INSERT statements, but not for SET statements. If you generate a value as follows (in Ruby), the client-side value returned by insert_id will be 0, not 48:
```
dbh.do("SET @x = LAST_INSERT_ID(48)") seq = dbh.func(:insert_id) 
```
To get the value in this case, ask the server for it:
```
seq = dbh.select_one("SELECT LAST_INSERT_ID()")[0] 
```

Recipe 11.14. Using Sequence Generators as Counters

Problem

Solution

Discussion

See Also