INSERT IGNORE INTO SELECT LAST_INSERT_ID()

Question

I have the below code. The thing is with SELECT LAST_INSERT_ID() this always returns last inserted row. Can i do a check with INSERT IGNORE if a row was actually inserted before getting the last row or do i simple do a select statement again i.e.

SET _inserted_geolocation_id  =(SELECT id FROM geolocation where latitude= _geolocation_latitude AND longitude = _geolocation_longitude
    AND zoom = _geolocation_zoom AND yaw = _geolocation_yaw AND pitch =_geolocation_pitch);

BEGIN

DECLARE _inserted_geolocation_id int;

INSERT IGNORE INTO geolocation (latitude, longitude, zoom, yaw, pitch) 
VALUES (_geolocation_latitude, _geolocation_longitude, _geolocation_zoom, _geolocation_yaw, _geolocation_pitch);

/*SET _inserted_geolocation_id  = (SELECT LAST_INSERT_ID());*/
SET _inserted_geolocation_id  =(SELECT id FROM geolocation where latitude= _geolocation_latitude AND longitude = _geolocation_longitude
    AND zoom = _geolocation_zoom AND yaw = _geolocation_yaw AND pitch =_geolocation_pitch);

SELECT _inserted_geolocation_id;
END

marcelj marcelj · Accepted Answer · 2012-11-24T17:08:18

LAST_INSERT_ID() returns 0 if no row was inserted by INSERT IGNORE, see Documentation:

If you use INSERT IGNORE and the row is ignored, the AUTO_INCREMENT counter is not incremented and LAST_INSERT_ID() returns 0, which reflects that no row was inserted.

INSERT IGNORE INTO SELECT LAST_INSERT_ID()

1 Answers