MySQL Cookbook

Using Single-Row Sequence Generators

Problem

You’re interested only in counting events, so there’s no point in creating a record for each count.

Solution

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

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 ...