List:MySQL and Perl« Previous MessageNext Message »
From:Michael Widenius Date:August 28 1999 9:39am
Subject:Re: BUG: $sth->rows broken for updates
View as plain text  
<cut>

>> Not only that but it would make mysql based DBI programs more compatible
>> with other DBD implementations such as Oracle, Ingres, DB2, etc.

Eric> If MySQL's behaviour is nonstandard, that fact overrides any
Eric> argument I can think of for it.  Compatibility rules!  (IMO, of
Eric> course.)  If other SQL databases report number of rows matched,
Eric> it seems sensible to coerce MySQL into doing the same by default.

Hi!

This has been up a couple of times:

Note that there is NO standard about this.  My argument is that a SQL
server is allowed to do the following modification to a query:

UPDATE table_name SET a=b WHERE condition

to

UPDATE table_name SET a=b WHERE (condition) AND a<>b;

The later query will in some context be MUCH faster than the original
one.

'affected' rows are also something that is quite vague.  One should
instead talk about 'matched' or 'changed' rows.

MySQL will also soon start supporting updates that involves many tables.  In
this context the above optimization will become even more important!

The ONLY way SQL allows you to know the number of rows involved is by
first doing an SELECT COUNT(*) and then doing the update.

Regards,
Monty
Thread
BUG: $sth->rows broken for updatesGregory C. Falck27 Aug
  • Re: BUG: $sth->rows broken for updatesJochen Wiedmann27 Aug
    • Re: BUG: $sth->rows broken for updatesMichael Widenius28 Aug
  • Re: BUG: $sth->rows broken for updatesGregory C. Falck28 Aug
    • Re: BUG: $sth->rows broken for updatesEric Siegerman28 Aug
      • Re: BUG: $sth->rows broken for updatesMichael Widenius28 Aug
      • Re: BUG: $sth->rows broken for updatesJochen Wiedmann28 Aug
      • Re: BUG: $sth->rows broken for updatesJochen Wiedmann29 Aug
  • Re: BUG: $sth->rows broken for updatesJochen Wiedmann28 Aug
    • Re: BUG: $sth->rows broken for updatesMichael Widenius29 Aug
  • Re: BUG: $sth->rows broken for updatesJochen Wiedmann29 Aug
  • Re: BUG: $sth->rows broken for updatesJochen Wiedmann30 Aug