I eventually settled for a bit of a mix of on duplicate col=VALUES(col) and col2=col2 as before we save the tweets we do some preg_replace stuff on it so no need to update it with the raw tweet again:

$sql = “INSERT INTO tweets (id, tweet, date, who, approved)
VALUES (\”$twitter_tweet_id\”, \”{$this->CI->db->escape($tweet_val->title)}\”, \”{$this->CI->db->escape($tweet_val->pubDate)}\”, \”{$this->CI->db->escape($tweet_val->author)}\”, ‘no’)
ON DUPLICATE KEY UPDATE id=VALUES(id), tweet=tweet, date=VALUES(date), who=who, approved=approved”;

I vote for UPSERT, too though; would have been much easier.

Cheers,

Kevin

This allows adding new rows which either are fully qualified or use some or all column defaults while only modifying the desired subset of columns when the key is already present.

REPLACE INTO fails in this respect — once the row is deleted, only the values in the new definition will be populated, so you’d better have somehow managed to define everything you don’t want unset/defaulted for existing rows, in which case you’re basically reinserting the entire table anyway.

This could well be another source of the data loss @Tony happened upon.

The previous developer(s) were quite fond of the “REPLACE INTO” syntax; the company’s web app was littered with it. Many problems plagued the system, the most common was the “random” loss of data.

As it turned out, the database has been in use for many years; some tables holding more than 1M records, as well as keyed with composites. Inevitably, someone would complain to the mother company’s I.T. dept about the app being slow. Mother company’s I.T. would just simply restart the MySQL instance in order to get performance back up. This occurred many times per day.

Of course, the sluggishness was due to the fact that the db engine was rebuilding the indexes for large tables. Restarting the server simply resulted in nightmarish situations.

Long story short: a good db programmer SHOULD understand the semantics of “REPLACE INTO” and carefully consider whether it is necessary.

One thing that was not mentioned in this article was the additional overhead of foreign-key relationships and how those tables are impacted.