← Back to team overview

maria-discuss team mailing list archive

Re: Fwd: mysql_insert_id() for INSERT...SELECT statement

 

Hi Sergei,

Thank you very much for the help.

You suggest to check `mysql_affected_rows` first and if it is `1`, get id
from `mysql_insert_id`, is it right? Because my goal is to get an ID in
case of a successful insertion.

Best,
Yuki

On Tue, Jan 23, 2018 at 1:46 PM, Sergei Golubchik <serg@xxxxxxxxxxx> wrote:

> Hi, Yuki!
>
> On Jan 22, Yuki . wrote:
> >
> > 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).
>
> The intended way to do it is to look at mysql_affected_rows().
> https://mariadb.com/kb/en/library/mysql_affected_rows/
>
> At least in C API (perhaps in some abtraction layers or connectors for
> some languages one can only access mysql_insert_id()?).
>
> Regards,
> Sergei
> Chief Architect MariaDB
> and security@xxxxxxxxxxx
>

Follow ups

References