Hi Björn,
I tested what you did on one of my systems, running 4.0.20:
mysql create table parent (
- ID int unsigned not null auto_increment,
- value varchar(50),
- primary key (ID)
- ) engine=InnoDB;
create table child (
ID int unsigned not null auto_increment,
parent_ID int unsigned not null,
value varchar(50),
primary key (ID),
key (parent_ID),
foreign key (parent_ID) references parent (ID)
) engine=InnoDB;
insert into parent (value) values ('a');Query OK, 0 rows affected (0.05 sec)
mysql
mysql create table child (
- ID int unsigned not null auto_increment,
- parent_ID int unsigned not null,
- value varchar(50),
- primary key (ID),
- key (parent_ID),
- foreign key (parent_ID) references parent (ID)
- ) engine=InnoDB;
Query OK, 0 rows affected (0.05 sec)
mysql
mysql insert into parent (value) values ('a');
Query OK, 1 row affected (0.02 sec)
mysql select last_insert_ID();
+--+
| last_insert_id() |
+--+
|1 |
+--+
1 row in set (0.00 sec)
*this value is as
expected*
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.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql select last_insert_ID();
+--+
| last_insert_id() |
+--+
|1 |
+--+
1 row in set (0.00 sec)
*
this value is interesting -- we've inserted 3 items into the child
table, and the last_insert_ID() is still 1. So I ran the command
again:
*
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.04 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql select last_insert_ID();
+--+
| last_insert_id() |
+--+
|4 |
+--+
1 row in set (0.00 sec)
*
this value is interesting also -- given these 2 results, MySQL only
finds the value for last_insert_ID once per statement, instead of once
per actual new row. I have 6 rows in my table, and the last_insert_id
is 4.
*
mysql insert into child (parent_ID, value) values (last_insert_ID(),
'b1'), (last_insert_ID(), 'b2'), (last_insert_ID(), 'b3');
ERROR 1216: Cannot add or update a child row: a foreign key constraint fails
mysql select last_insert_ID();
+--+
| last_insert_id() |
+--+
|7 |
+--+
1 row in set (0.00 sec)
mysql select * from child;
++---+---+
| ID | parent_ID | value |
++---+---+
| 1 | 1 | b1|
| 2 | 1 | b2|
| 3 | 1 | b3|
| 4 | 1 | b1|
| 5 | 1 | b2|
| 6 | 1 | b3|
++---+---+
6 rows in set (0.00 sec)
*
This makes sense, because it's trying to insert a parent_ID of 4,
which is the current value of last_insert_id. Nothing was inserted,
and yet the last_insert_ID() changed to 7, even though it didn't even
insert anything with the value of 7 into the child table.
Granted, I tested this on MySQL 4.0.20, but it seems consistent with
what you get. As well, if you're inserting multiple parent rows, the
last_insert_ID is the first ID that is inserted:
mysql insert into parent (value) VALUES ('b'),('c'),('d');
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql select last_insert_ID();
+--+
| last_insert_id() |
+--+
|2 |
+--+
1 row in set (0.00 sec)
mysql select * from parent;
++---+
| ID | value |
++---+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
++---+
4 rows in set (0.00 sec)
We would expect the value of last_insert_ID() to be 4, not 2.
I had to dig into the documentation to find this, but it's in there:
http://dev.mysql.com/doc/refman/5.0/en/information-functions.html
LAST_INSERT_ID() LAST_INSERT_ID(expr)
Returns the first automatically generated value that was set for an
AUTO_INCREMENT column by the last INSERT or UPDATE query to affect
such a column.
-Sheeri
On 11/4/05, Björn Persson [EMAIL PROTECTED] wrote:
Hi everybody!
It seems like different instances of MySQL do things in different order, with
rather unfortunate effects on the last_insert_ID function.
Consider the following tables and insertions:
create table parent (
ID int unsigned not null auto_increment,
value varchar(50),
primary key
