List:General Discussion« Previous MessageNext Message »
From:mfatene Date:May 19 2005 4:08pm
Subject:Re: ON DUPLICATE KEY UPDATE and AUTO_INCREMENT columns
View as plain text  
Hi,
If you add another command,
mysql> insert into bla1 values (NULL, "Cello3", NULL) on duplicate key update
whentime = NOW();

The right ID will be used.

Since last_insert_id() has a connection scope, it's better for you to use :

select max(id) from bla1;


Mathias


Selon Sven Paulus <sven@stripped>:

> Hi,
>
> I'd like to insert string values into a table. If I add a new string, I want
> to get back the value of the AUTO_INCREMENT column. If the string already
> exists in the table, I'd like to get the AUTO_INCREMENT value of the existing
> entry.
>
> I thought this might be possible using INSERT ... ON DUPLICATE KEY UPDATE
> ..., but LAST_INSERT_ID() seems to be unusable in this case.
>
> Example:
>
> mysql> CREATE TABLE `bla1` (
>     ->   `id` int(10) unsigned NOT NULL auto_increment,
>     ->   `value` varchar(255) default NULL,
>     ->   `whentime` timestamp(14) NOT NULL,
>     ->   PRIMARY KEY  (`id`),
>     ->   UNIQUE KEY `value` (`value`)
>     -> ) TYPE=MyISAM
>     -> ;
> Query OK, 0 rows affected, 1 warning (0.10 sec)
>
> mysql> insert into bla1 values (NULL, "Cello", NULL) on duplicate key update
> whentime = NOW();
> Query OK, 1 row affected (0.08 sec)
>
> mysql> select LAST_INSERT_ID();
> +------------------+
> | LAST_INSERT_ID() |
> +------------------+
> |                1 |
> +------------------+
> 1 row in set (0.04 sec)
>
> mysql> insert into bla1 values (NULL, "Hallo", NULL) on duplicate key update
> whentime = NOW();
> Query OK, 1 row affected (0.01 sec)
>
> mysql> select LAST_INSERT_ID();
> +------------------+
> | LAST_INSERT_ID() |
> +------------------+
> |                2 |
> +------------------+
> 1 row in set (0.00 sec)
>
> mysql> select * from bla1;
> +----+-------+---------------------+
> | id | value | whentime            |
> +----+-------+---------------------+
> |  1 | Cello | 2005-05-18 17:14:53 |
> |  2 | Hallo | 2005-05-18 17:15:38 |
> +----+-------+---------------------+
> 2 rows in set (0.00 sec)
>
> mysql> insert into bla1 values (NULL, "Cello", NULL) on duplicate key update
> whentime = NOW();
> Query OK, 2 rows affected (0.00 sec)
>
> mysql> select LAST_INSERT_ID();
> +------------------+
> | LAST_INSERT_ID() |
> +------------------+
> |                3 |
> +------------------+
> 1 row in set (0.00 sec)
>
> mysql> select * from bla1;
> +----+-------+---------------------+
> | id | value | whentime            |
> +----+-------+---------------------+
> |  1 | Cello | 2005-05-18 17:15:58 |
> |  2 | Hallo | 2005-05-18 17:15:38 |
> +----+-------+---------------------+
> 2 rows in set (0.00 sec)
>
>
> I expected that the last INSERT clause would set the LAST_INSERT_ID() to 1.
> But of course the MySQL manual says "The value of LAST_INSERT_ID() is not
> changed if you update the AUTO_INCREMENT column of a row with a non-magic
> value (that is, a value that is not NULL and not 0)." Ok, I didn't even
> update the id column at all. If I use id=NULL in the UPDATE clause, the id
> column is changed to 0 and the LAST_INSERT_ID() still contains the wrong
> value ...
>
> Now I'm curious if it's possible at all to use LAST_INSERT_ID() together
> with INSERT ... ON DUPLICATE KEY UPDATE ... - how can I determine if a
> row was inserted or updated and if I can trust the LAST_INSERT_ID()
> value?
>
> Something like
>    SET @myid:=0
>    ... ON DUPLICATE KEY UPDATE @myid:=id
> doesn't work, since I can't assign user variables in this part.
>
> So, is there a way to INSERT an entry if neccessary and always get the id of
> the entry?
>
> Sven
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>


Thread
ON DUPLICATE KEY UPDATE and AUTO_INCREMENT columnsSven Paulus19 May
  • Re: ON DUPLICATE KEY UPDATE and AUTO_INCREMENT columnsmfatene19 May
    • Re: ON DUPLICATE KEY UPDATE and AUTO_INCREMENT columnsSven Paulus19 May
      • Re: ON DUPLICATE KEY UPDATE and AUTO_INCREMENT columnsmfatene20 May