last_insert

last_insert_id

am 27.12.2009 17:13:26 von Victor Subervi

Re: last_insert_id

am 27.12.2009 17:27:36 von Mattia Merzi

2009/12/27 Victor Subervi :
> mysql> select * from products;
[...]
> mysql> select last_insert_id() from products;
[...]
> Now, I was expecting 1, not 0! What up?

[...] LAST_INSERT_ID() (no arguments) returns the first
automatically generated value successfully inserted for
an AUTO_INCREMENT column as a result of the most
recently executed INSERT statement. [...] If no rows
were (successfully) inserted, LAST_INSERT_ID() returns 0.

http://dev.mysql.com/doc/refman/5.1/en/information-functions .html#function_last-insert-id
http://dev.mysql.com/doc/refman/5.1/en/getting-unique-id.htm l

Greetings,

Mattia.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: last_insert_id

am 27.12.2009 17:42:30 von Victor Subervi

Re: last_insert_id

am 27.12.2009 18:00:08 von Michael Dykman

Re: last_insert_id

am 27.12.2009 18:49:09 von Victor Subervi

--001485394218eeec6a047bb967ca
Content-Type: text/plain; charset=ISO-8859-1

On Sun, Dec 27, 2009 at 12:00 PM, Michael Dykman wrote:

> last_insert_id() returns the last id auto-incremented in *the current
> session*. If you disconnect and reconnect, it can not be retrieved.
>

Ahah! So how do I retrieve the last id inserted irrespective of connection?
TIA,
V

--001485394218eeec6a047bb967ca--

Re: last_insert_id

am 27.12.2009 19:30:17 von Gary Smith

Victor Subervi wrote:
> On Sun, Dec 27, 2009 at 12:00 PM, Michael Dykman wrote:
>
>
>> last_insert_id() returns the last id auto-incremented in *the current
>> session*. If you disconnect and reconnect, it can not be retrieved.
>>
>>
>
> Ahah! So how do I retrieve the last id inserted irrespective of connection?
>
Would max() work for you? This isn't necessarily foolproof, as it would
show the highest ID if you used max(id), for instance - this won't
necessarily be what you were expecting, but in most cases will be what
you'd imagine it would be.

An example of where it wouldn't be: Although ID is auto_increment, you
could define a row as, say, '10005583429'. This would be a valid input.
Selecting max(id) would return that number. However, auto_increment
wouldn't change - it would still be '34' (or whatever) for the next
line. Thus, max(id) would be wrong for however long it takes for
auto_increment to get to that figure, which could potentially be a long
time.

Cheers,

Gary

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: last_insert_id

am 27.12.2009 19:44:30 von edberg

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@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=gcdmg-mysql-2@m.gmane.org

Re: last_insert_id

am 27.12.2009 20:04:16 von Victor Subervi

--000e0cd5c6ca8ff909047bba7414
Content-Type: text/plain; charset=ISO-8859-1

On Sun, Dec 27, 2009 at 1:30 PM, Gary Smith wrote:

> Victor Subervi wrote:
>
>> On Sun, Dec 27, 2009 at 12:00 PM, Michael Dykman
>> wrote:
>>
>>
>>
>>> last_insert_id() returns the last id auto-incremented in *the current
>>> session*. If you disconnect and reconnect, it can not be retrieved.
>>>
>>>
>>>
>>
>> Ahah! So how do I retrieve the last id inserted irrespective of
>> connection?
>>
>>
> Would max() work for you?

Ahah! No space! Got it. Thanks.
V

--000e0cd5c6ca8ff909047bba7414--

Re: last_insert_id

am 27.12.2009 20:26:04 von Gary Smith

Steve Edberg wrote:
> (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.
Aah... I'd mis-remembered on this. Is there an SQL mode or somesuch
which changes this behaviour, or is my mind dribbling out of my ears?

Gary

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: last_insert_id

am 27.12.2009 23:10:08 von Carsten Pedersen

Gary Smith skrev:

....

> An example of where it wouldn't be: Although ID is auto_increment, you
> could define a row as, say, '10005583429'. This would be a valid input.
> Selecting max(id) would return that number. However, auto_increment
> wouldn't change - it would still be '34' (or whatever) for the next
> line.

Not quite...

CREATE TABLE t (id bigint unsigned primary key auto_increment);
INSERT INTO t VALUES (10005583429);
INSERT INTO t VALUES (null);
SELECT * FROM t;

+-------------+
| id |
+-------------+
| 10005583429 |
| 10005583430 |
+-------------+
2 rows in set (0.00 sec)

/ Carsten

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: last_insert_id

am 27.12.2009 23:10:24 von Mark Goodge

Gary Smith wrote:
> Steve Edberg wrote:
>> (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.

> Aah... I'd mis-remembered on this. Is there an SQL mode or somesuch
> which changes this behaviour, or is my mind dribbling out of my ears?

As far as I'm aware there's no mode to change the default behaviour, but
you can always reset the autoincrement value:

ALTER TABLE tbl AUTO_INCREMENT = n;

Do that, and the next inserted record will have id = n, provided that n
is greater than the current maximum value. If, on the other hand, n is
lower than or equal to the current maximum value, the next id will be
the next value higher than the current maximum. So

ALTER TABLE tbl AUTO_INCREMENT = 1;

on a non-empty table is functionally equivalent to

ALTER TABLE tbl AUTO_INCREMENT = MAX(id) + l

(which isn't valid SQL, so don't try it!)

If you want to reuse autoincrement values above the current maximum,
therefore, you can achieve that in practice by resetting the
autoincrement value prior to any insertion.

What you can't do, though, is get autoincrement to insert values into
the middle of a sequence. So if you have, say, ids 1,2,3,4,5,8,9 and you
issue

ALTER TABLE tbl AUTO_INCREMENT = 1;

or

ALTER TABLE tbl AUTO_INCREMENT = 6;

then the next inserted id will still be 10, not 6.

Mark
--
http://mark.goodge.co.uk

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org