I need to INSERT a new row into my TABLE(with unique field 'A'), if it already exists(duplicate field, insert failed) - just return the ID of the existing one.
This code works well:
insert into TABLE set A=1 on duplicate key update id=last_insert_id(id)
But now I have another problem: how do I know if the returning ID belongs to a new (inserted) row or it's just an old one?
Yes, I can do "SELECT id WHERE A=1" beforehand, but it would overcomplicate the program code, require two steps, and just looks ugly. Besides, in future I may want to remove some UNIQUE indexes, then I'll have to rewrite the program as well to change all the 'where' checks. Maybe there is a better solution?
[solved, see my answer]
Found this solution. It works in console, but doesn't work in my program (must be a bug in the client, idk) - so probably it will work fine for everyone (except me, sigh)
Just check the 'affected rows count' - it will be 1 for the new record and 0 for the old one
mysql> INSERT INTO EMAIL set addr="test" ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
Query OK, 1 row affected (0.01 sec)<----- 1 = INSERTed
mysql> select last_insert_id(); //returns 1
mysql> INSERT INTO EMAIL set addr="test" ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
Query OK, 0 rows affected (0.00 sec) <----- 0 = OLD
mysql> select last_insert_id(); //returns 1
UPD: it didn't work for me because my program kept sending the 'CLIENT_FOUND_ROWS ' flag when connecting to mysql. Removed it, now everything is fine!