List:General Discussion« Previous MessageNext Message »
From:Steve Edberg Date:December 27 2009 6:44pm
Subject:Re: last_insert_id
View as plain text  
At 11:13 AM -0500 12/27/09, you wrote:
>Hi;
>
>mysql> select * from products;
>+----+----------+----------+-------+--------+-------------+-------+------------+--------------+------------+-------------+------------------+-------------------+--------------+-----------+---------------+--------+----------+--------------+------+------+-------------+--------------------------+
>| ID | SKU      | Category | Name  | Title  | Description | Price |
>SortFactor | Availability | OutOfStock | ShipFlatFee | ShipPercentPrice |
>ShipPercentWeight | Associations | TempPrice | LastDatePrice | Weight |
>Metal    | PercentMetal | pic0 | pic1 | sizes       |
>colorsShadesNumbersShort |
>+----+----------+----------+-------+--------+-------------+-------+------------+--------------+------------+-------------+------------------+-------------------+--------------+-----------+---------------+--------+----------+--------------+------+------+-------------+--------------------------+
>|  1 | prodSKU1 | prodCat1 | name1 | title1 | desc        | 12.34 |
>500 |            1 |          0 |       10.00 |                5
>|                 2 |              |         1 | 2000-01-01    |   2.50 |
>14k gold |           20 | NULL | NULL | Extra-small
>|                          |
>+----+----------+----------+-------+--------+-------------+-------+------------+--------------+------------+-------------+------------------+-------------------+--------------+-----------+---------------+--------+----------+--------------+------+------+-------------+--------------------------+
>1 row in set (0.00 sec)
>
>mysql> select last_insert_id() from products;
>+------------------+
>| last_insert_id() |
>+------------------+
>|                0 |
>+------------------+
>1 row in set (0.00 sec)
>
>mysql>
>
>Now, I was expecting 1, not 0! What up?
>TIA,
>Victor


The normal procedure would be to:

insert into products values (null, 'prodsku2',...);
select last_insert_id();

(assuming ID is your autoincremented field). Do the select 
last_insert_id() immediately after your insert, and it is guaranteed 
to give you the ID of the record you just inserted, regardless of 
what inserts may be happening in other sessions (and if the insert 
was not successful, it will return 0).

If you want to get the highest ID that has been inserted regardless 
of session or without doing an insert first, you could do a select 
max(ID). Depending on your overall database design, this may or may 
not give you what you want. Eg:

(1) you can explicitly specify a value for an autoincrement field 
(eg, insert into products values (1000,'prodsku3'...), which could 
leave a gap. However, the next autoincrement value in this case would 
be 1001 and is probably what you want.

(2) autoincrement values are not reused after deletion, so if you 
deleted the record with ID=1000 inserted in (1), the next 
autoincrement would still be 1001, even if the existing records are 
IDs 1,2,3. This is usually the desired behavior, but again, may not 
be what *you* need.

I'd recommend spending some time reading the documentation for 
autoincrement fields and the last_insert_id() function.

	- sbe -




-- 
+------------------------------------------------------------------------+
| Steve Edberg                                  edberg@stripped |
| Programming/Database/SysAdmin            http://www.edberg-online.com/ |
+------------------------------------------------------------------------+
Thread
last_insert_idVictor Subervi27 Dec
  • Re: last_insert_idMattia Merzi27 Dec
    • Re: last_insert_idVictor Subervi27 Dec
      • Re: last_insert_idMichael Dykman27 Dec
        • Re: last_insert_idVictor Subervi27 Dec
          • Re: last_insert_idGary Smith27 Dec
            • Re: last_insert_idVictor Subervi27 Dec
            • Re: last_insert_idCarsten Pedersen27 Dec
  • Re: last_insert_idSteve Edberg27 Dec
    • Re: last_insert_idGary Smith27 Dec
      • Re: last_insert_idMark Goodge27 Dec