Just for the sake of using this lonely forum once more, I'll put my SQL question here.
Say I have a table foo with two simple columns id and bar. id is an auto_increment integer, and bar is a string. Now, what I want to do is to lookup the id given bar value, and if it not exists to add a new record and return id of that new record.
Either I do this in one query (how?) and if I can't I must use at least two queries using some locking mechanism (in order not to end up the same bar twice).
Anyone who can shed some light on my rusty SQL knowledge?
Just looking at the mysql reference, you would then need a temporary var
SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
SELECT * FROM shop WHERE price=@min_price OR price=@max_price;
Putting the two together, I suspect
BEGIN;
SELECT @foo_var := foo FROM mytable WHERE bar = `something`;
IF @foo_var IS NOT NULL
...do your thing
ELSE
...do your other thing
COMMIT;
The mysql api (say, from php) will then tell you the id generated by the last inser (if there was one). To wit:
int mysql_insert_id (resource [link_identifier])
mysql_insert_id() returns the ID generated for an AUTO_INCREMENT column by the previous INSERT query using the given link_identifier. If link_identifier isn't specified, the last opened link is assumed.
mysql_insert_id() returns 0 if the previous query does not generate an AUTO_INCREMENT value. If you need to save the value for later, be sure to call mysql_insert_id() immediately after the query that generates the value.
Note: The value of the MySQL SQL function LAST_INSERT_ID() always contains the most recently generated AUTO_INCREMENT value, and is not reset between queries.
This BEGIN COMMIT stuff is a transaction right? Does this act atomicly towards other "clients"? Because I don't want that another client does SELECT @foo_var := foo FROM mytable WHERE bar = `something`; before I did the appropriate action.
This has nothing to do with stored procedures, right?
I've figured out how to do it with locks by now, but transactions seem cooler ;) OK, I know that's not a good enough reason to prefer them, but somehow it sounds more database-ish. I'll have to learn a bit more about transactions I guess.
Turns out there's a much simplier solution to my problem: UNIQUE =) If I set bar to be unique, I can INSERT (which may possibly fail) before I select, avoiding the race condition.
In my case, the constraint to be unique is actually about two columns, but that seems to be no problem because you can set a UNIQUE KEY over multiple columns at once. Huray. =)
