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. To use
this feature for incrementing counters in the
booksales table, modify the
UPDATE statement slightly:
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.
| 
No comments:
Post a Comment