You’re interested only in counting events, so there’s no point in creating a table row for each sequence value.
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
n
th 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]
Web Page Access Counting revisits the single-row sequence-generation mechanism, where it serves as the basis for implementing web page hit counters.
18.118.120.206