Generating and Using Sequences - MySQL - page 468

Discussion
AUTO_INCREMENT columns are useful for generating sequences across a set of individual
rows. But some applications require only a count of the number of times an event occurs,
and there's no benefit from 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. Such applications 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_IN
CREMENT values so that you can not only increment the count, but retrieve the updated
value easily.
To count a single type of event, use a trivial table with a single row and column. For
example, to record copies sold of a book, create a table like this:
CREATE TABLE booksales ( copies INT UNSIGNED );
However, if you're counting sales for multiple book titles, that method doesn't work well.
You certainly don't want to create a separate single-row counting table per book. Instead,
count them all within a single table by including a column that uniquely identifies each
book. The following table does this using a title column for the topic 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 the same statement works to initialize and update the sales
count.