Reason for the "PDO extension" Lastinsertid function to return 0

Problem

When inserting data using the PDO extension of PHP, it is sometimes necessary to get the ID of the last inserted record as the return information. How can we achieve this demand?

Lastinsertid function

Use the Lastinsertid function of PDO.

However, the recent use of the process found that sometimes the Lastinsertid function returns 0. Why is that?

Let's take a look at the description of the Lastinsertid function in the PHP manual.

Returns the ID or sequence value of the last inserted row.

Take a look at some of the following examples.

Test examples

The primary key is the ID field, and the self-increment constraint is used.

 
  GetMessage ();    }    for ($i = 0; $i <, $i + +) {            $sql = ' INSERT into ' tbl_test ' (ID, name) VALUE (: ID,: Name) ';            $data = Array (                    ': id ' = = ',                    ': Name ' = ' "user_$i"            );            $sth = $dbh->prepare ($sql);            $sth->execute ($data);    }    $sql = ' INSERT into ' tbl_test ' (ID, name) VALUE (: ID,: Name) ';    $new _data = Array (            ': id ' = = ',            ': Name ' = ' user_new '    );    $sth = $dbh->prepare ($sql);    $sth->execute ($new _data);    $last _id = $dbh->lastinsertid ();    Echo ' last ID: '. $last _id;

Results

Last Id:11

The primary key is the ID field, and the self-increment constraint is not used.

 
  GetMessage ();    }    for ($i = 0; $i <, $i + +) {            $sql = ' INSERT into ' tbl_test ' (ID, name) VALUE (: ID,: Name) ';            $data = Array (                    ': id ' = = $i,                    ': Name ' = ' user_$i '            );            $sth = $dbh->prepare ($sql);            $sth->execute ($data);    }    $sql = ' INSERT into ' tbl_test ' (ID, name) VALUE (: ID,: Name) ';    $new _data = Array (            ': id ' = = ',            ': Name ' = ' user_new '    );    $sth = $dbh->prepare ($sql);    $sth->execute ($new _data);    $last _id = $dbh->lastinsertid ();    Echo ' last ID: '. $last _id;

Results

Last id:0

The primary key is not an ID field, and the primary key uses the self-increment constraint.

 
  GetMessage ();    }    for ($i = 0; $i <, $i + +) {            $sql = ' INSERT into ' tbl_test ' (tbl_id, name) VALUE (: tbl_id,: Name) ';            $data = Array (                    ': tbl_id ' = $i,                    ': Name ' = ' user_$i '            );            $sth = $dbh->prepare ($sql);            $sth->execute ($data);    }    $sql = ' INSERT into ' tbl_test ' (tbl_id, name) VALUE (: tbl_id,: Name) ';    $new _data = Array (            ': tbl_id ' = ', '            : Name ' = ' user_new '    );    $sth = $dbh->prepare ($sql);    $sth->execute ($new _data);    $last _id = $dbh->lastinsertid ();    Echo ' last ID: '. $last _id;

Results

Last Id:11

The primary key is not an ID field, and the self-increment constraint is not used.

 
  GetMessage ();    }    for ($i = 0; $i <, $i + +) {            $sql = ' INSERT into ' tbl_test ' (tbl_id, name) VALUE (: tbl_id,: Name) ';            $data = Array (                    ': tbl_id ' = Uniqid (),                    ': Name ' = ' user_$i '            );            $sth = $dbh->prepare ($sql);            $sth->execute ($data);    }    $sql = ' INSERT into ' tbl_test ' (tbl_id, name) VALUE (: tbl_id,: Name) ';    $new _data = Array (            ': tbl_id ' = + uniqid (),            ': Name ' = ' user_new '    );    $sth = $dbh->prepare ($sql);    $sth->execute ($new _data);    $last _id = $dbh->lastinsertid ();    Echo ' last ID: '. $last _id;

Results

Last id:0

View PHP Source code

As you can see, some examples return 0, and some examples return the latest IDs. So what happens when the Lastinsertid returns 0? Search on the Internet a lot of information, and did not find the answer, open PHP source code, found that the implementation of the function last_insert_id source code is this:

As you can see, the value of the ID returned by the function is the value returned by calling the MYSQL_INSERT_ID function in the MySQL API.

View MySQL Manual

Open the MySQL manual and find this section here:

MYSQL_INSERT_ID () returns the value stored into a auto_increment column, whether that value was automatically generated by Storing NULL or 0 or is specified as an explicit value. LAST_INSERT_ID () returns only automatically generated auto_increment values. If you store a explicit value other than NULL or 0, it does not affect the value returned by last_insert_id ().

Conclusion

As you can tell from the description of the manual, the MYSQL_INSERT_ID function returns the value of the field stored in the auto_increment constraint if the field in the table does not use a auto_increment constraint or is inserted with a unique value that you generate. Then the function does not return the value you stored, but returns null or 0. Therefore, in tables that do not use the auto_increment constraint, or if the ID is the only Id,lastinsertid function generated by itself, the return is 0.

This article is to discuss the cause of a problem, because of the limited personal level, if there are suggestions and criticisms, welcomed the point.

Note: This article uses the php5.4.15,mysql5.5.41.

