List:General Discussion« Previous MessageNext Message »
From:Jim McNeely Date:December 19 2011 9:55pm
Subject:Re: UPDATE triggers with REPLACE statements
View as plain text  
With REPLACE, you just set up the query the same as an INSERT statement but otherwise it
just works. With ON DUPLICATE UPDATE you have to set up the whole query with the entire
text all over again as an update. The query strings for what I'm doing are in some cases
pushing enough text in medical report fields that it uses a MediumText data type, and I
am watchful of bandwidth and performance, so this seems better - I'm not sending the
field names and values twice. It is also something I don't have to program, I can just
set the engine. The performance bottleneck is NOT likely going to be MySQL with either
engine, but the processes creating these queries have some limitations.

Anyway, I just thought I would share. BTW I experimented, and innoDB does updates and
fires off update triggers for REPLACE statements, but MyISAM does delete/inserts.

Jim McNeely

On Dec 19, 2011, at 1:28 PM, Claudio Nanni wrote:

> Good to know and good that you took time to read the manual, good approach.
> 
> But why bother with REPLACE if you will go with INSERT.....ON DUPLICATE KEY
> UPDATE?
> 
> The storage engine is a property of your table and you can set it and/or
> change it, it is the low-level layer (physical) of the database that takes
> care on how data is actually stored and retrieved.
> 
> You can check your table with:
> 
> SHOW TABLE STATUS LIKE 'your-table-name';
> 
> Manual page: http://kae.li/iiiga
> 
> Cheers
> 
> Claudio
> 
> 2011/12/19 Jim McNeely <jim@stripped>
> 
>> In the MySQL documentation, we find this tantalizing statement:
>> 
>> "It is possible that in the case of a duplicate-key error, a storage
>> engine may perform the REPLACE as an update rather than a delete plus
>> insert, but the semantics are the same. There are no user-visible effects
>> other than a possible difference in how the storage engine increments
>> Handler_xxx status variables."
>> 
>> Does anyone know what engine this is? I can't seem to find any info via
>> google. If I could live with the choice of engine, I could make this work
>> with no extra programming at all.
>> 
>> Thanks,
>> 
>> Jim McNeely
>> 
>> On Dec 18, 2011, at 11:26 AM, Claudio Nanni wrote:
>> 
>>> Only if you can change the application you could use INSERT....ON
>> DUPLICATE
>>> KEY UPDATE  instead of REPLACE.
>>> 
>>> Check Peter's post here: http://kae.li/iiigi
>>> 
>>> Cheers
>>> 
>>> Claudio
>>> 
>>> 
>>> 2011/12/17 Jim McNeely <jim@stripped>
>>> 
>>>> Here is a fun one!
>>>> 
>>>> I have a set of tables that get populated and changed a lot from lots of
>>>> REPLACE statements. Now, I need an ON UPDATE trigger, but of course the
>>>> trigger never gets triggered because REPLACES are all deletes and
>> inserts.
>>>> 
>>>> The trigger is going to populate another table as a queue for a system
>> to
>>>> do something whenever a particular field changes.
>>>> 
>>>> SO, does anyone have some slick idea how to handle this little dilemma?
>> I
>>>> have an idea but I have a feeling there is something better out there.
>>>> 
>>>> Thanks!
>>>> 
>>>> Jim McNeely
>>>> --
>>>> MySQL General Mailing List
>>>> For list archives: http://lists.mysql.com/mysql
>>>> To unsubscribe:    http://lists.mysql.com/mysql
>>>> 
>>>> 
>>> 
>>> 
>>> --
>>> Claudio
>> 
>> 
> 
> 
> -- 
> Claudio

Thread
UPDATE triggers with REPLACE statementsJim McNeely17 Dec
  • Re: UPDATE triggers with REPLACE statementshsv18 Dec
  • Re: UPDATE triggers with REPLACE statementsClaudio Nanni18 Dec
    • Re: UPDATE triggers with REPLACE statementsJim McNeely19 Dec
    • Re: UPDATE triggers with REPLACE statementsJim McNeely19 Dec
      • Re: UPDATE triggers with REPLACE statementsClaudio Nanni19 Dec
        • Re: UPDATE triggers with REPLACE statementsJim McNeely19 Dec
          • Re: UPDATE triggers with REPLACE statementshsv20 Dec
      • Re: UPDATE triggers with REPLACE statementshsv19 Dec
        • Re: UPDATE triggers with REPLACE statementsJim McNeely19 Dec
          • Re: UPDATE triggers with REPLACE statementshsv23 Jan