Generating and Using Sequences - MySQL - page 471

See Also
Recipe 20.12 uses this single-row sequence-generation mechanism as the basis for im‐
plementing web page hit counters.
13.13. Generating Repeating Sequences
Problem
You require a sequence that contains cycles.
Solution
Generate a sequence and use it to produce cyclic elements with division and modulo
operations.
Discussion
Some sequence-generation problems require values that go through cycles. Suppose
that you manufacture items such as pharmaceutical products or automobile parts, and
you must be able to track them by lot number if manufacturing problems are discovered
later that require items sold within a particular lot to be recalled. Suppose also that you
pack and distribute items 12 units to a box and 6 boxes to a case. In this situation, item
identifiers are three-part values: the unit number (with a value from 1 to 12), the box
number (with a value from 1 to 6), and a lot number (with a value from 1 to the highest
current case number).
This item-tracking problem appears to require that you maintain three counters, so you
might generate the next identifier value using an algorithm like this:
retrieve most recently used case, box, and unit numbers
unit = unit + 1 # increment unit number
if (unit > 12) # need to start a new box?
{
unit = 1 # go to first unit of next box
box = box + 1
}
if (box > 6) # need to start a new case?
{
box = 1 # go to first box of next case
case = case + 1
}
store new case, box, and unit numbers
Alternatively, it's possible simply to assign each item a sequence number identifier and
derive the corresponding case, box, and unit numbers from it. The identifier can come
from an AUTO_INCREMENT column or a single-row sequence generator. The formulas for