List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:April 8 2002 12:56am
Subject:Re: last_insert_id gives 0
View as plain text  
>I am running MySQL 3.23.41-17 on a stock SuSE 7.3 install.  I have a table
>"customers" with an auto-incrementing primary key "customer_id".  If I insert
>a record:
>mysql> insert into customers(customer_id) values ("10");
>and then ask for the last insert:
>mysql> select last_insert_id();
>(which I thought was the correct syntax) I get the response "0".

You're inserting a specific value into the AUTO_INCREMENT field, which
doesn't result in the creation of a new automatic sequence number.

If you want to insert a record with a specific value in that column, *and*
you want the value to be treated like an AUTO_INCREMENT value, so that
LAST_INSERT_ID() will return that value, then do this:

INSERT INTO customers (customer_id VALUES(LAST_INSERT_ID(10));

>
>If I ask:
>mysql> select last_insert_id() from customers;
>I get as many zeros as there are records.
>
>If I ask:
>mysql> select last_insert_id(customer_id) from customers;
>I get the ids for all the records.
>
>If I then ask:
>mysql> select last_insert_id();
>I get the last record inserted (which was what was supposed to happen, I
>thought), BUT if I insert another record and repeat:
>mysql> select last_insert_id();
>I get the same number as the last time I asked - ie the last but one.
>
>However, if I then ask:
>mysql> select last_insert_id(customer_id) from customers;
>followed by:
>mysql> select last_insert_id();
>I get the most recently-added record number.
>
>Could some kind person on the list please explain what is happening here, and
>why the standard syntax does not give the results I expect?

The standard syntax is for automatically created sequence numbers.
You're not automatically creating a sequence number.

>
>Thank you.
>
>Kevin

Thread
last_insert_id gives 0Kevin Donnelly7 Apr
  • Re: last_insert_id gives 0Paul DuBois8 Apr
    • Re: last_insert_id gives 0Dicky Wahyu Purnomo8 Apr
      • Re: last_insert_id gives 0Paul DuBois8 Apr
    • Re: last_insert_id gives 0Kevin Donnelly8 Apr