|
Related Reading 
|
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.")
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.
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.
|
In This Series
Cooking with MySQL
Cooking with MySQL |
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:
At the SQL level, you can save the value in a SQL variable after issuing a query that generates an AUTO_INCREMENT value:
INSERT INTO tbl_name (id,...) VALUES(NULL,...);
SET @saved_id = LAST_INSERT_ID( );Then you can issue other statements without regard to their effect on LAST_INSERT_ID( ). To use the original AUTO_INCREMENT value in a subsequent query, refer to the @saved_id variable.
At the API level, you can save the AUTO_INCREMENT value in an API language variable. This can be done either by saving the value returned from LAST_INSERT_ID( ) or from any API-specific extension that might be available.
A third technique can be used from within APIs that allow you to maintain separate client-side AUTO_INCREMENT values. For example, in Python, when you use a cursor object to execute a query, the AUTO_INCREMENT value generated by the query is available by calling the cursor's insert_id( ) method. If you issue other queries using the same cursor, that value will be lost. However, if you use a different cursor object to execute additional queries, the original cursor's insert_id value will be unaffected:
cursor1 = conn.cursor ( )
cursor2 = conn.cursor ( )
gen_seq_val (cursor1) # issue query that generates a sequence number
gen_seq_val (cursor2) # issue another, using a different cursor
seq1 = cursor1.insert_id ( )
seq2 = cursor2.insert_id ( )
print "seq1:", seq1, "seq2:", seq2 # these values will be different
cursor1.close ( )
cursor2.close ( )In Perl, you can achieve the same effect by means of two statement handles; the mysql_insertid attribute for each is unaffected by query activity on the other. In Java, use separate Statement or PreparedStatement objects.
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.)
|
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.
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.
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:
One solution to the problem is to add detail records using MySQL's INSERT syntax that allows multiple rows to be inserted with a single statement. That way you can apply the LAST_INSERT_ID( ) value from the master record to all the detail records:
INSERT INTO invoice (inv_id,date)
VALUES(NULL,CURDATE( ));
INSERT INTO inv_item (inv_id,qty,description) VALUES
(LAST_INSERT_ID( ),1,'hammer'),
(LAST_INSERT_ID( ),3,'nails, box'),
(LAST_INSERT_ID( ),12,'bandage');Another method is to save the master record AUTO_INCREMENT value into a SQL variable for use when inserting the detail records:
INSERT INTO invoice (inv_id,date)
VALUES(NULL,CURDATE( ));
SET @inv_id = LAST_INSERT_ID( );
INSERT INTO inv_item (inv_id,qty,description)
VALUES(@inv_id,1,'hammer');
INSERT INTO inv_item (inv_id,qty,description)
VALUES(@inv_id,3,'nails, box');
INSERT INTO inv_item (inv_id,qty,description)
VALUES(@inv_id,12,'bandage');A third method is similar to the second, but applies only from within an API. Insert the master record, then save the AUTO_INCREMENT value into an API variable for use when inserting detail records. For example, in Perl, you can access the AUTO_INCREMENT using the mysql_insertid attribute, so the invoice-entry procedure looks something like this:
$dbh->do ("INSERT INTO invoice (inv_id,date) VALUES(NULL,CURDATE( ))");
$inv_id = $dbh->{mysql_insertid};
$sth = $dbh->prepare ("INSERT INTO inv_item (inv_id,qty,description)
VALUES(?,?,?)");
$sth->execute ($inv_id, 1, "hammer");
$sth->execute ($inv_id, 3, "nails, box");
$sth->execute ($inv_id, 12, "bandage");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.