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:

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

Related

Категории