maria-discuss team mailing list archive

["Yuki ." <choppin.block.party@xxxxxxxxx>]

Hi,

I made a post at https://stackoverflow.com/questions/48306962/mysql-
insert-id-for-insert-select-statement, but have not received any helpful
answer, may be you could help me.
I am using MaridDB but I read MySQL documentation because it has much more
elaborated documentation than the same page for MariaDB.

I am doing similar INSERT...SELECT query to this

    INSERT INTO table (value1, value2)
    SELECT 'stuff for value1', 'stuff for value2' FROM DUAL
    WHERE NOT EXISTS (SELECT * FROM table
          WHERE value1='stuff for value1' AND value2='stuff for value2')
    LIMIT 1

, where table has auto-genrated id.

And I would like to know if it was inserted or not, of course. I assume the
way to do that is to use `mysql_insert_id()`. It returns 0 if no insertions
happen and 1 if insertions happen. Looking more details [here](
https://dev.mysql.com/doc/refman/5.7/en/mysql-insert-id.html).

> If an INSERT ... SELECT statement is executed, and **NO** automatically
> generated value is successfully inserted, mysql_insert_id() **RETURNS**
> the ID of the last inserted row.

What does it return if no auto-generated ID was successfully inserted? Is
this a doc typo?

So far I did testing in C and `mysql_insert_id()` returns always 0 if
insertion did not happen even if the last insertion succeeded and
`mysql_insert_id()` returned non-zero result. A paragraphs in the same
manual, mentioned above, confirms this behavior by saying:


>  mysql_insert_id() returns 0 if the previous statement does not use an
AUTO_INCREMENT value. ....
>
> The value of mysql_insert_id() is affected only by statements issued
within the current client connection. It is not affected by statements
issued by other clients.
>
> The LAST_INSERT_ID() SQL function will contain the value of the first
automatically generated value that was successfully inserted.
LAST_INSERT_ID() is not reset between statements because the value of that
function is maintained in the server. ....

And that feels kind of logical otherwise `INSERT...SELECT` would be useless
in many cases, if you cannot know within the code if your insertion worked
or not. But it totally contradicts to the statement above. Did anyone have
experience with this?

>From MariaDB manual, also suggests that the value should be zero in case of
insertion did not happen:

> The mysql_insert_id() function returns the ID generated by a query on
> a table with a column having the AUTO_INCREMENT attribute or the value
> for the last usage of LAST_INSERT_ID(expr). If the last query wasn't
> an INSERT or UPDATE statement or if the modified table does not have a
> column with the AUTO_INCREMENT attribute and LAST_INSERT_ID was not
> used, this function will return zero.

Does `mysql_insert_id()` of MariaDB returns 0 for `INSERT...SELECT` if an
insertion did not happen, or it might return some other non-zero value?  Do
you think it is a typo at MySQL documentation?

Best,
Yuki