How to use sequence generators as counters in MySQL

If you’re interested only in counting events, use a sequence-generation mechanism that uses just one row per counter.

AUTO_INCREMENTcolumns 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:

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 exp​ression 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 exp​ression argument to LAST_INSERT_ID(), MySQL treats the exp​ression 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: