INSERT, ON DUPLICATE KEY UPDATE and LAST_INSERT_ID ()

Question

According to the answers to the question on en.so, I came to the conclusion that the tools indicated there are what is required in my task, namely: to get the identifier of the "inserted" line, even if this did not happen in fact.

INSERT INTO `table` (`id`, `text`) VALUES (1, 'tra-ta-ta') ON DUPLICATE KEY UPDATE `id` = LAST_INSERT_ID(`id`);

... while the id column is PRIMARY KEY and AUTO_INCREMENT , and the text column is UNIQUE .

But based on this code, it turns out that the id will be replaced (the assignment sign, as it were, hints at this):

 `id` = LAST_INSERT_ID(`id`)

Does this mean that the value of the identifier in the corresponding column will indeed be replaced (albeit by the same value) with the corresponding consequences, such as, for example, ON UPDATE CASCADE for FOREIGN KEY for any connected table? And maybe ON DELETE CASCADE will work, as if using REPLACE instead of INSERT ? Then quite a disaster.

Am I right in my doubts, and if this is true, then how is it possible to solve the problem?

But, if you write the subject of your question (what you want to achieve), and the necessary data - of course at the beginning of the question, then everything will be just great!
It is possible that this is nonsense (in the evening I am not a worker), but what prevents to make an experiment?
@Majestio, the second time you tell me how to make out questions.
If you personally understanding is difficult, please continue to ignore.
@Alexander Muksimov, I do not use triggers, but foreign keys - yes.
I thought that the question would get an answer much faster than I could get to the bottom of it, having tried all the options for possible exceptions.
In the end, the situation with the use of on duplicate update , it seems to me, is quite ordinary for those who are constantly working with the DBMS.
And, nevertheless, if you “wish to be done well to you,” designate the details.

Accepted Answer · 2017-01-12T18:02:21

 mysql> create table `table` (id serial, `text` varchar(16) unique); Query OK, 0 rows affected (0.05 sec) mysql> INSERT INTO `table` (`id`, `text`) VALUES (1, 'tra-ta-ta') -> ON DUPLICATE KEY UPDATE `id` = LAST_INSERT_ID(`id`); Query OK, 1 row affected (0.00 sec) mysql> select * from `table`; +----+-----------+ | id | text | +----+-----------+ | 1 | tra-ta-ta | +----+-----------+ 1 row in set (0.00 sec) mysql> INSERT INTO `table` (`id`, `text`) VALUES (2, 'tro-lo-lo') -> ON DUPLICATE KEY UPDATE `id` = LAST_INSERT_ID(`id`); Query OK, 1 row affected (0.00 sec) mysql> select * from `table`; +----+-----------+ | id | text | +----+-----------+ | 1 | tra-ta-ta | | 2 | tro-lo-lo | +----+-----------+ 2 rows in set (0.00 sec) mysql> INSERT INTO `table` (`id`, `text`) VALUES (3, 'tra-ta-ta') -> ON DUPLICATE KEY UPDATE `id` = LAST_INSERT_ID(`id`); Query OK, 0 rows affected (0.00 sec) mysql> select LAST_INSERT_ID(); +------------------+ | LAST_INSERT_ID() | +------------------+ | 1 | +------------------+ 1 row in set (0.00 sec) mysql> select * from `table`; +----+-----------+ | id | text | +----+-----------+ | 1 | tra-ta-ta | | 2 | tro-lo-lo | +----+-----------+ 2 rows in set (0.00 sec)

As we can see, in the third request for replenishment / change

Query OK, 0 rows affected (0.00 sec)

Yes, MySQL has such a feature - it considers only the actual update to be updated. Those. related to the fact that the totals are not equal to the original.

UPD: Somewhere nakosyachil. I feel, but I can not figure it out, too evening. Tomorrow I'll dig everything and accomplish my goal.

For example, regarding the question in the text of the foreign key .
That is, Query OK, 0 rows affected (0.00 sec) Have you safely viewed?
Probably I asked an extremely simple question, but it comes from my small experience working with a DBMS.
You just dropped the code listing, without really explaining (actually, like last time) what is connected with what and from what follows.
"0 rows affected" I saw, but did not associate it with foreign keys.
related to the fact that the totals are NOT EQUAL to the original.
Only in this case the triggers will shoot and the protchaya ... it seemed to me that this is generally known to be trivial ...
The site brings together people of different levels of training.
Even if it so happens that the questioner himself is specifically and quite well acquainted with the topic, then other readers will come later, who may not be so burdened with relevant knowledge.
Therefore, in any case, it is useful to give an answer explaining the principal points.

INSERT, ON DUPLICATE KEY UPDATE and LAST_INSERT_ID ()

1 answer 1

More articles: