>> 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.
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
UPDATE table_name SET a=b WHERE (condition) AND a<>b;
The later query will in some context be MUCH faster than the original
'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.