MySQL Cookbook, 4th Edition

Chapter 15. Generating and Using Sequences

15.0 Introduction

A sequence is a set of integers (1, 2, 3, …) generated in order on demand. Sequences see frequent use in databases because many applications require each row in a table to contain a unique value, and sequences provide an easy way to generate them. This chapter describes how to use sequences in MySQL in the following five ways:

Using AUTO_INCREMENT columns

The AUTO_INCREMENT column is MySQL’s mechanism for generating a sequence over a set of rows. Each time you create a row in a table that contains an AUTO_INCREMENT column, MySQL automatically generates the next value in the sequence as the column’s value. This value serves as a unique identifier, making sequences an easy way to create items such as customer ID numbers, shipping package waybill numbers, invoice or purchase order numbers, bug report IDs, ticket numbers, or product serial numbers.

Retrieving sequence values

For many applications, it’s not enough just to create sequence values. It’s also necessary to determine the sequence value for a just-inserted row. A web application may need to redisplay to a user the contents of a row created from the contents of a form just submitted by the user. The value may need to be retrieved so it can be stored in rows of a related table.

Resequencing techniques

It’s possible to renumber a sequence that has holes in it due to row deletions, reuse deleted values at the top of a sequence, or add a sequence column to a table that has none. ...