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 edb...@edberg-online.com |
| Programming/Database/SysAdmin http://www.edberg-online.com/ |
+------------------------------------------------------------------------+
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
