Database Reference
In-Depth Information
the value of
LAST_INSERT_ID()
and causes the master row
inv_id
value to be “lost.” As
a result, each of the remaining inserts into the
inv_item
table stores the preceding row's
item_id
value into the
inv_id
column. This causes the second and following rows to
have incorrect
inv_id
values.
To avoid this difficulty, save the sequence value generated by the insert into the master
table and use the saved value for the inserts into the detail table. To save the value, use
a user-defined SQL variable or a variable maintained by your program.
Recipe 13.10
describes those techniques, which apply here as follows:
• Use a user-defined variable: Save the master row
AUTO_INCREMENT
value in a user-
defined variable for use when inserting the detail rows:
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'
);
• Use a variable maintained by your program: This method is similar to the previous
one, but applies only from within an API. Insert the master row, and then save the
AUTO_INCREMENT
value into an API variable for use when inserting detail rows. For
example, in Ruby, access the
AUTO_INCREMENT
value using the
insert_id
database-
handle attribute:
dbh
.
do
(
"INSERT INTO invoice (inv_id,date) VALUES(NULL,CURDATE())"
)
inv_id
=
dbh
.
func
(
:insert_id
)
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"
)
13.12. Using Sequence Generators as Counters
Problem
You're interested only in counting events, so there's no point in creating a table row for
each sequence value.
Solution
Use a sequence-generation mechanism that uses a single row per counter.
