mySQL's REPLACE INTO command
Posted on Nov 11, 2005
I was in an interview today and I showed the interviewer some sample code. I'd brought a function to save an associative array (eg $_POST) to a database table, taken from a database abstraction layer I wrote a while back for a small (low traffic) site. I thought it was fancy because it automatically built the sql statement and automatically detected whether an update or an insert needed to be done based on the presence of the primary key in the associative array.
I felt like a total newb when the interviewer looked at it and said "couldn't you just do a replace into"? I never even noticed mySQL had a replace command, it's certainly not part of any SQL standard I can find. It may make my code as unportable as the grand piano my girlfriend Nicole wants so badly, but still, it could potentially eliminate one database operation and over 20 lines of code. So I did some investigating. It turns out mySQL's replace command is pretty neat, but I'm not sure it suits the purpose the function I wrote serves. From the manual: REPLACE works exactly like INSERT, except that if an old record in the table has the same value as a new record on a UNIQUE index or PRIMARY KEY, the old record is deleted before the new record is inserted. At first, this seems like a god-send. But then I started thinking about how this actually works. Instead of updating the original record, it deletes the original record and inputs a new one. This might be okay for some instances, but often I've used functions like this for multiple pages of a user sign up process. For example, on the first page, the user enters in their username, their password, and maybe a few other things. On the second page, the user enters in their address info... etc. If I rewrote my old function to use the replace command, saving the second form would blow away all the information from the first form. But just for kicks, I rewrote the function how this would work anyway. BTW, I found a great site that compares the difference between SQL implementations: http://troels.arvin.dk/db/rdbms/ blog comments powered by Disqus |
|