14

EDIT: This question title originally was: How does Doctrine know last inserted id in MySQL? and was related to Doctrine ORM mapper. After some digging I found out that this question is not related to Doctrine but to PDO_MySQL, MySQL C API and finally - to MySQL client-server communication. I have decided to change the title, so maybe someone will find answer to his/hers question.

For those who are not using Doctrine: I was curious, why bellow:

mysql_query("INSERT INTO category (name) VALUES('cat')");
echo mysql_insert_id();

or similar:

$pdo->exec("INSERT INTO category (name) VALUES('cat')");
echo $pdo->lastInsertId();

will lead to only one position (without separate SELECT LAST_INSERT_ID()) in log:

1701 Query    INSERT INTO category (name) VALUES ('cat')

Original question:

I have 2 tables:

category(id,name)
product(id, name, categoryId)

I created new category object and product object. I assigned category object to product object. I didn't set any ids:

$product = new Product();
$product->name = 'asdf';

$category = new Category();
$category->name = 'cat';

$product->Category = $category;

After that I flushed the connection and check MySQL logs:

1684 Query  START TRANSACTION
1684 Query  INSERT INTO category (name) VALUES ('cat')
1684 Query  INSERT INTO product (name, categoryid) VALUES ('asdf', '312')
1684 Query  COMMIT

How did Doctrine know, that the newly created category id is 312? There is nothing else in logs.

prostynick
  • 6,129
  • 4
  • 37
  • 61

3 Answers3

23

I did some research and browse some source code, so my answer could be a little bit wrong and not precise.

First of all, this is not really related to Doctrine. Doctrine uses PDO_MYSQL. But internally PDO_MYSQL uses the same thing as mysql_insert_id function - native MySQL C API function - mysql_insert_id.

The reason why there is no seperate SELECT LAST_INSERT_ID() lies in the fact, that after the statement is executed (in my example INSERT), server responds with data and some other things included in OK Packet), including insert_id. So when we fire mysql_insert_id() we are not connecting to the server, to receive insert_id - mysql library does not need to do that - it already has this value stored from last execution of query (at least I think so after analyzing the file libmysql.c)

OK Packet is described here: MySQL Generic Response Packets - OK Packet

jensgram
  • 31,109
  • 6
  • 81
  • 98
prostynick
  • 6,129
  • 4
  • 37
  • 61
1

Probably by calling http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html

Tobias
  • 7,238
  • 10
  • 46
  • 77
  • But that would be reflected in the log, wouldn't it? – Pekka Nov 24 '10 at 13:54
  • @Pekka An uneducated guess: Could the log be configured to *not* write `SELECT` s? – jensgram Nov 24 '10 at 13:55
  • @jensgram could be - or it could be configured to show the results of function calls (namely `LAST_INSERT_ID()`). I don't know how the query logs work – Pekka Nov 24 '10 at 13:55
  • I already did something similar to `INSERT INTO foo2 (id,text) VALUES(LAST_INSERT_ID(),'text');` and I have in logs string `LAST_INSERT_ID()...`, not the value of id itself. – prostynick Nov 24 '10 at 14:00
  • @prostynick Did you try a simple `SELECT`? Were you able to find this in the log, too? – jensgram Nov 24 '10 at 14:11
  • @jensgram Yes, I have all selects statements in log, also `SELECT last_insert_id()` or `SELECT MAX(id) FROM category` – prostynick Nov 24 '10 at 14:17
1

last_insert_id works only for AUTO_INCREMENT columns. It will not work if you insert a value manually into the AUTO_INCREMENT column.

The doctrine will work because it is just like assigning a NULL to it (although you never specifically write that). This triggers the auto increment and provide a value for last_insert_id().

I have included a jpg here so that you can see it. Hope it helps!

enter image description here

aksu
  • 5,221
  • 5
  • 24
  • 39
mike
  • 87
  • 4