[TYPO3-dev] Should t3lib_db support MySQL's REPLACE INTO statement?

[Xavier Perseguers]

Hi Steffen,

>> Martin Kutschker schrieb:
>> "Extension" refers to an extension of the SQL standard, not an extension
>> of the Mysql server.
>>
>> Masi
> 
> ah - i misunderstood, thx. The wording produce "alarm" :-)
> 
> Reading the comments in the doc it seems not to be unproblematic, eg 
> that it makes a delete and the innoDB issues.
> I also don't understand the difference between REPLACE and INSERT ... ON 
> DUPLICATE KEY UPDATE, does anyone?

When you perform a REPLACE on a non existing row, then you INSERT it. 
Thus it's the same as writing INSERT INTO...

When you perform a REPLACE on an existing row, you would like instead to 
perform an UPDATE but MySQL does not bother to check whether the row 
exists, it DELETEs it then performs an INSERT. This leads to loose all 
associated data (with constraints and ON DELETE CASCADE statements) 
whereas you just wanted to UPDATE some row.

Writing INSERT INTO ... ON DUPLICATE KEY UPDATE means that whenever you 
try to INSERT a record with a primary key that is already there, instead 
of having a constraint error, the statement should automatically be 
converted to an UPDATE statement, which is really nice and I think far 
better than the original REPLACE statement.

-- 
Xavier Perseguers
http://xavier.perseguers.ch/en/tutorials/typo3.html