ONLamp.com    
 Published on ONLamp.com (http://www.onlamp.com/)
 See this if you're having trouble printing code examples


O'Reilly Book Excerpts: MySQL Cookbook

Cooking with MySQL

Related Reading

MySQL Cookbook
By Paul DuBois

by Paul DuBois

Editor's note: Paul DuBois has selected sample recipes from the hundreds you'll find in his book, MySQL Cookbook. In this second in a three-part series showcasing these recipes, learn how to manage simultaneous AUTO_INCREMENT values, as well as how to use AUTO_INCREMENT values and related tables. (These excerpts are from Chapter 11, "Generating and Using Sequences.")

Managing Multiple Simultaneous AUTO_INCREMENT Values

Problem

You're working with two or more tables that contain AUTO_INCREMENT columns, and you're having a hard time keeping track of the sequence values generated for each table.

Solution

Save the values in SQL variables for later. If you're using queries from within a program, save the sequence values in program variables; or you may be able to issue the queries using separate connection or statement objects to keep them from getting mixed up.

Discussion

In This Series

Cooking with MySQL
Paul DuBois has selected sample recipes from the hundreds you'll find in his book, MySQL Cookbook. In this third and final series of excerpts showcasing these recipes, learn how to compute team standings, how to calculate the differences between successive rows, and how to find cumulative sums and running averages.

Cooking with MySQL
Paul DuBois has selected sample recipes from the hundreds you'll find in his book, MySQL Cookbook. In this first in a three-part series showcasing these sample recipes, find out how to interpret results from summaries and NULL values and how to identify duplicates in a table or record.

As described in "Retrieving Sequence Values," the LAST_INSERT_ID( ) server-side sequence value indicator function is set each time a query generates an AUTO_INCREMENT value, whereas client-side sequence indicators may be reset for every query. What if you issue a statement that generates an AUTO_INCREMENT value, but don't want to refer to that value until after issuing a second statement that also generates an AUTO_INCREMENT value? In this case, the original value no longer will be accessible, either through LAST_INSERT_ID( ) or as a client-side value. To retain access to it, you should save the value first before issuing the second statement. There are several ways to do this:

The third technique doesn't work with PHP, because there is no client-side object or structure that maintains AUTO_INCREMENT values on a query-specific basis. The client-side AUTO_INCREMENT value is returned by mysql_insert_id( ), which is tied to the connection, not to a statement. Yes, I know what you're thinking: a workaround would be to open a second connection to the server and issue the first and second queries over the different connections. You're right, that would work -- but it's not worth the effort. The overhead of opening another connection is much higher than simply saving the mysql_insert_id( ) value into a PHP variable before issuing another query. Furthermore, opening a second connection isn't as straightforward as it might seem. If you issue a second mysql_connect( ) or mysql_pconnect( ) call with the same connection parameters as the original call, PHP returns the same connection identifier as the one it returned originally! You'd have to connect to the server as a different user to get a truly independent connection identifier. (At the risk of muddying the waters, I should point out that as of PHP 4.2.0, mysql_connect( ) supports the option of explicitly forcing a new connection to be opened. You can use this feature to maintain separate client-side AUTO_INCREMENT values.)

Using AUTO_INCREMENT Values to Relate Tables

Problem

You're using sequence values from one table as keys in second table so that you can relate records in the two tables properly. But the associations aren't being set up properly.

Solution

You're probably not inserting records in the proper order, or you're losing track of the sequence values. Change the insertion order, or save the sequence values so that you can refer to them when you need them.

Discussion

Be careful with AUTO_INCREMENT values that are used to generate ID values in a master table if you also store those values in detail table records to link the detail records to the proper master table record. This kind of situation is quite common. Suppose you have an invoice table listing invoice information for customer orders, and an inv_item table listing the individual items associated with each invoice. Here, invoice is the master table and inv_item is the detail table. To uniquely identify each order, the invoice table could contain an AUTO_INCREMENT column inv_id. You'd also store the appropriate invoice number in each inv_item table record, so you can tell which invoice it goes with. The tables might look something like this:

CREATE TABLE invoice
(
    inv_id  INT UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (inv_id),
    date    DATE NOT NULL
    # ... other columns could go here
    # ... (customer ID, shipping address, etc.)
);
CREATE TABLE inv_item
(
    inv_id      INT UNSIGNED NOT NULL,  # invoice ID (from invoice table)
    INDEX (inv_id),
    qty         INT,                    # quantity
    description VARCHAR(40)             # description
);

For these kinds of table relationships, it's typical to insert a record into the master table first (to generate the AUTO_INCREMENT value that identifies the record), then insert the detail records and refer to LAST_INSERT_ID( ) to obtain the master record ID. For example, if a customer buys a hammer, three boxes of nails, and (in anticipation of finger-bashing with the hammer) a dozen bandages, the records pertaining to the order can be inserted into the two tables like so:

INSERT INTO invoice (inv_id,date)
    VALUES(NULL,CURDATE( ));
INSERT INTO inv_item (inv_id,qty,description)
    VALUES(LAST_INSERT_ID( ),1,'hammer');
INSERT INTO inv_item (inv_id,qty,description)
    VALUES(LAST_INSERT_ID( ),3,'nails, box');
INSERT INTO inv_item (inv_id,qty,description)
    VALUES(LAST_INSERT_ID( ),12,'bandage');

The first INSERT adds a record to the invoice master table and generates a new AUTO_INCREMENT value for its inv_id column. The following INSERT statements each add a record to the inv_item detail table, using LAST_INSERT_ID( ) to get the invoice number. This associates the detail records with the proper master record.

What if you need to process multiple invoices? There's a right way and a wrong way to enter the information. The right way is to insert all the information for the first invoice, then proceed to the next. The wrong way is to add all the master records into the invoice table, then add all the detail records to the inv_item table. If you do that, all the detail records in the inv_item table will contain the AUTO_INCREMENT value from the most recently entered invoice record. Thus, all will appear to be part of the same invoice, and records in the two tables won't have the proper associations.

If the detail table contains its own AUTO_INCREMENT column, you must be even more careful about how you add records to the tables. Suppose you want to number the rows in the inv_item table sequentially for each order. The way to do that is to create a multiple-column AUTO_INCREMENT index that generates a separate sequence for the items in each invoice. (See "Using an AUTO_INCREMENT Column to Create Multiple Sequences," which discusses this type of index.) Create the inv_item table as follows, using a PRIMARY KEY that combines the inv_id column with an AUTO_INCREMENT column, seq:

CREATE TABLE inv_item
(
    inv_id      INT UNSIGNED NOT NULL,  # invoice ID (from invoice table)
    seq         INT UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (inv_id, seq),
    qty         INT,                    # quantity
    description VARCHAR(40)             # description
);

The inv_id column allows each inv_item row to be associated with the proper invoice table record, just as with the original table structure. In addition, the index causes the seq values for the items in each invoice to be numbered sequentially, starting at 1. However, now that both tables contain an AUTO_INCREMENT column, you cannot enter information for an invoice the same way as before. To see why it doesn't work, try it:

INSERT INTO invoice (inv_id,date)
    VALUES(NULL,CURDATE( ));
INSERT INTO inv_item (inv_id,qty,description)
    VALUES(LAST_INSERT_ID( ),1,'hammer');
INSERT INTO inv_item (inv_id,qty,description)
    VALUES(LAST_INSERT_ID( ),3,'nails, box');
INSERT INTO inv_item (inv_id,qty,description)
    VALUES(LAST_INSERT_ID( ),12,'bandage');

These queries are the same as before, but now behave somewhat differently due to the change in the inv_item table structure. The INSERT into the invoice table works properly. So does the first INSERT into the inv_item table; LAST_INSERT_ID( ) returns the inv_id value from the master record in the invoice table. However, this INSERT also generates its own AUTO_INCREMENT value (for the seq column), which changes the value of LAST_INSERT_ID( ) and causes the master record inv_id value to be "lost." The result is that subsequent inserts into the inv_item store the preceding record's seq value into the inv_id column. This causes the second and following records to have incorrect inv_id values.

These are several ways to avoid this difficulty. One involves using a different INSERT syntax to add the detail records; others save the master record AUTO_INCREMENT value into a variable for later use:

Check back in two weeks for the next sampling from MySQL Cookbook. Recipes will cover how to compute team standings, how to calculate the differences between successive rows, and how to find cumulative sums and running averages (all excerpts from Chapter 12, "Using Multiple Tables").

Paul DuBois is one of the primary contributors to the MySQL Reference Manual. He is also the author of Using csh & tcsh and Software Portability with imake by O'Reilly, as well as MySQL and MySQL and Perl for the Web by New Riders.


Return to ONLamp.com.

Copyright © 2009 O'Reilly Media, Inc.