Mysql_insert_id () behavior

Question

I use functions from the php library which uses from time to time mysql_insert_id(). Now I have changed several functions that insert data into tables, so that the value of the primary key is explicitly specified, for example. I insert a row with ID = 300, followed by a row with ID = 200, when the table originally had 10 rows. I am wondering if mysql_insert_id will behave as expected, i.e. return the value explicitly specified for the AUTO_INCREMENT PRIMARY KEY column.

Answer 1

Table:

CREATE TABLE `foo` (
  `id` int(11) NOT NULL auto_increment,
  `value` varchar(32) NOT NULL,
  PRIMARY KEY  (`id`)
) ;

the code:

print phpversion()."\n";
mysql_connect('localhost','user','pass');
mysql_select_db('database');

mysql_query('INSERT INTO `foo` (`id`,`value`) VALUES(100,"bar")');
print var_export(mysql_insert_id())."\n";

mysql_query('INSERT INTO `foo` (`value`) VALUES("zoid")');
print var_export(mysql_insert_id())."\n";

mysql_query('INSERT INTO `foo` (`id`,`value`) VALUES(99,"yargh")');
print var_export(mysql_insert_id())."\n";

$q = mysql_query('SHOW TABLE STATUS LIKE "foo"');
$status = mysql_fetch_assoc($q);
mysql_free_result($q);
print $status['Auto_increment']."\n";

mysql_query('INSERT INTO `foo` (`value`) VALUES("whatever")');
print var_export(mysql_insert_id())."\n";

Conclusion:

5.2.XX-0.dotdeb.1
100
101
99
102
102

TL / DR version: it returns the last id, automatically incrementing or explicitly specifying.

UPDATE: Added INSERTusing id < 100as suggested by Mchl.

Answer 2

: ,

. , , .

,

mysql_insert_id() auto_increment feild, .

0, , .

,

- EDIT..

<?php
$link = mysql_connect('localhost', 'root', 'techping');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
mysql_select_db('test');

mysql_query("INSERT INTO test (id, name) values (100,'kossu')");
printf("Last inserted record has id %d<br />", mysql_insert_id());
mysql_query("INSERT INTO test (id, name) values (125,'kossu')");
printf("Last inserted record has id %d<br />", mysql_insert_id());
mysql_query("INSERT INTO test (id, name) values (121,'kossu')");
printf("Last inserted record has id %d<br />", mysql_insert_id());
mysql_query("INSERT INTO test (id, name) values ('','kossu')");
printf("Last inserted record has id %d<br />", mysql_insert_id());
mysql_query("INSERT INTO test (id, name) values (250,'kossu')");
printf("Last inserted record has id %d<br />", mysql_insert_id());
?>

:

Last inserted record has id 100 
Last inserted record has id 125
Last inserted record has id 121
Last inserted record has id 126
Last inserted record has id 250

, 121, (126) id (.. 125 ), 122

Answer 3

, . 0:

The identifier generated for the AUTO_INCREMENT column by the previous success request, 0 if the previous request does not generate AUTO_INCREMENT or FALSE if the MySQL connection is not established. - http://php.net/manual/en/function.mysql-insert-id.php

The identifier is not generated, so it should return 0.

As proved, in practice this is not so.