Hi Bjorn,
It looks like the LAST_INSERT_ID() is returning the id of the last
auto_increment INSERT, this seems to be in line with the documentation.
<quote>
The ID that was generated is maintained in the server on a per-connection
basis. This means that the value which the function returns to a given client
is the first AUTO_INCREMENT value generated for most recent statement affecting
an AUTO_INCREMENT column by that client.
</quote>
To get what I think you need, you could do
use test;
drop table parent;
drop table child;
create table parent (
ID int unsigned not null auto_increment,
value varchar(50),
primary key (ID)
);
create table child (
ID int unsigned not null auto_increment,
parent_ID int unsigned not null,
value varchar(50),
primary key (ID)
);
insert into parent (value) values ('a');
set @lid=last_insert_id(); <-------------------------------------
insert into child (parent_ID, value) values
(@lid, 'b1'), (@lid, 'b2'),
(@lid, 'b3');
insert into parent (value) values ('c');
set @lid=last_insert_id(); <-------------------------------------
insert into child (parent_ID, value) values
(@lid, 'd1'), (@lid, 'd2'),
(@lid, 'd3');
select * from child;
With the results being
ID parent_ID value
1 1 b1
2 1 b2
3 1 b3
4 2 d1
5 2 d2
6 2 d3
Regards
David Logan
Database Administrator
HP Managed Services
148 Frome Street,
Adelaide 5000
Australia
+61 8 8408 4273 - Work
+61 417 268 665 - Mobile
+61 8 8408 4259 - Fax
-----Original Message-----
From: Björn Persson [mailto:[EMAIL PROTECTED]
Sent: Wednesday, 16 November 2005 10:06 AM
To: mysql@lists.mysql.com
Subject: Re: Database IDs
Gleb Paharenko:
> Could you provide a repeatable test case for you problem?
I don't know how repeatable this is. As I said I have one computer where it
seems to work sometimes, as if there's a race condition. Right now it seems
repeatable on the computer I'm using at the moment:
mysql> select version();
+-----------+
| version() |
+-----------+
| 4.1.14 |
+-----------+
1 row in set (0.00 sec)
mysql> create table parent (
-> ID int unsigned not null auto_increment,
-> value varchar(50),
-> primary key (ID)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> create table child (
-> ID int unsigned not null auto_increment,
-> parent_ID int unsigned not null,
-> value varchar(50),
-> primary key (ID)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> insert into parent (value) values ('a');
Query OK, 1 row affected (0.00 sec)
mysql> insert into child (parent_ID, value) values
-> (last_insert_ID(), 'b1'), (last_insert_ID(), 'b2'),
-> (last_insert_ID(), 'b3');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into parent (value) values ('c');
Query OK, 1 row affected (0.00 sec)
mysql> insert into child (parent_ID, value) values
-> (last_insert_ID(), 'd1'), (last_insert_ID(), 'd2'),
-> (last_insert_ID(), 'd3');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from child;
+----+-----------+-------+
| ID | parent_ID | value |
+----+-----------+-------+
| 1 | 1 | b1 |
| 2 | 1 | b2 |
| 3 | 2 | b3 |
| 4 | 2 | d1 |
| 5 | 4 | d2 |
| 6 | 5 | d3 |
+----+-----------+-------+
6 rows in set (0.00 sec)
Note how child rows 1 and 4 have the IDs of their respective parent row in the
parent_ID field, but child rows 3, 5 and 6 have the ID of the preceding child
row instead. (For row 2 we can't tell the difference.)
Here's the SQL code for easy copying:
use test;
create table parent (
ID int unsigned not null auto_increment,
value varchar(50),
primary key (ID)
);
create table child (
ID int unsigned not null auto_increment,
parent_ID int unsigned not null,
value varchar(50),
primary key (ID)
);
insert into parent (value) values ('a');
insert into child (parent_ID, value) values
(last_insert_ID(), 'b1'), (last_insert_ID(), 'b2'),
(last_insert_ID(), 'b3');
insert into parent (value) values ('c');
insert into child (parent_ID, value) values
(last_insert_ID(), 'd1'), (last_insert_ID(), 'd2'),
(last_insert_ID(), 'd3');
select * from child;
Björn Persson
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
