>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
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
