SQL "matching found rows" vs "affected rows"

En introduction, la conclusion

client MariaDB/MySQL : "affected (changed) rows" par défaut.
client SQLite : "found (matched) rows" uniquement ! pas moyen de le changer ?...
PHP + MariaDB/MySQL : "affected (changed) rows" par défaut, possibilité de passer en "found (matched) rows" avec l'option PDO::MYSQL_ATTR_FOUND_ROWS => true dans le connecteur
PHP + SQLite : "found (matched) rows" uniquement ! pas moyen de le changer ?..
Connecteur/J MySQL +.MariaDB/MySQL : "found (matched) rows" par défaut, possibilité de passer en "affected (changed) rows" avec useAffectedRows=true
Connecteur/J MariaDB+.MariaDB/MySQL : "found (matched) rows" par défaut, mais impossible de le changer.

MySQL client (mysql Ver 15.1 Distrib 10.0.27-MariaDB)

MariaDB [test]> SHOW CREATE TABLE table_update_count;
+--------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table              | Create Table                                                                                                                                                                                                                                  |
+--------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| table_update_count | CREATE TABLE `table_update_count` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `val` varchar(3) COLLATE latin1_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci |
+--------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> TRUNCATE TABLE table_update_count;
Query OK, 0 rows affected (0.07 sec)

MariaDB [test]> INSERT INTO table_update_count (val) VALUES ('AA');
Query OK, 1 row affected (0.01 sec)

MariaDB [test]> INSERT INTO table_update_count (val) VALUES ('AB');
Query OK, 1 row affected (0.01 sec)

MariaDB [test]> UPDATE table_update_count SET val = 'AB';
Query OK, 1 row affected (0.01 sec)
Rows matched: 2  Changed: 1  Warnings: 0

MariaDB [test]> SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

MariaDB [test]> UPDATE table_update_count SET val = 'AB';
Query OK, 0 rows affected (0.02 sec)
Rows matched: 2  Changed: 0  Warnings: 0

MariaDB [test]> SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

On constate que MariaDB/MySQL fonctionne en "found rows" :

le premier UPDATE matche 2 rows, et change 1 row, puisque l'autre vaut déjà 'AB'
le seconde UPDATE matche 2 rows, et ne change rien puisque toutes les valeurs valent déjà 'AB'

ROW_COUNT()
- MariaDB - ROW_COUNT() returns the number of rows updated, inserted or deleted by the preceding statement. This is the same as the row count that the mysql client displays and the value from the mysql_affected_rows() C API function. (Returns the number of affected rows by the last operation associated with mysql).
- MySQL ROW_COUNT() The number of rows updated.

SQLite

SQLite version 3.15.0 2016-10-14 10:20:30
Enter ".help" for usage hints.
sqlite> DROP TABLE "main"."table_update_count";
sqlite> SELECT CHANGES();
0
sqlite> CREATE  TABLE "main"."table_update_count" ("id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE , "val" VARCHAR);
sqlite> SELECT CHANGES();
0
sqlite> INSERT INTO table_update_count (val) VALUES ('AA');
sqlite> SELECT CHANGES();
1
sqlite> INSERT INTO table_update_count (val) VALUES ('AB');
sqlite> SELECT CHANGES();
1
sqlite> UPDATE table_update_count SET val = 'AB';
sqlite> SELECT CHANGES();
2
sqlite> UPDATE table_update_count SET val = 'AA' WHERE id = 1;
sqlite> SELECT CHANGES();
1
sqlite> UPDATE table_update_count SET val = 'AB' WHERE id = 1 OR id = 2;
sqlite> SELECT CHANGES();
2
sqlite>

On constate que SQLite fonctionne en "affected rows" :

le premier UPDATE matche 2 rows, et bien qu'en seul row soit changé, puisque l'autre vaut déjà 'AB', il indique 2 changements !
le seconde UPDATE matche 2 rows, et bien qu'aucun row soit changé, puisque toutes les valeurs valent déjà 'AB', il indique 2 changements !

CHANGES

changes() The changes() function returns the number of database rows that were changed or inserted or deleted by the most recently completed INSERT, DELETE, or UPDATE statement, exclusive of statements in lower-level triggers. The changes() SQL function is a wrapper around the sqlite3_changes() C/C++ function and hence follows the same rules for counting changes.

SQLite C Interface - Count The Number Of Rows Modified int sqlite3_changes(sqlite3*); This function returns the number of rows modified, inserted or deleted by the most recently completed INSERT, UPDATE or DELETE statement on the database connection specified by the only parameter. Executing any other type of SQL statement does not modify the value returned by this function.

PRAGMA count_changes

By default, this deprecated pragma is false and these statements do not return anything. If set to true, each of the mentioned statement will return an one-column, one-row table consisting of a single integer value indicating impacted rows by the operation.

no way for SQLite ?

UPDATE SQLite, how to get numbers of affected changed rows (not the found matched rows)
SQLite changes() counts non-changing UPDATEs The database does not compare old and new values; any UPDATEd row always counts as "changed" even if the values happen to be the same. "the UPDATE affects only those rows for which the result of evaluating the WHERE clause expression as a boolean expression is true"

no way for SQLite !

It's technically impossible in the core of SQLite ? Maybe with a new option. Or just a feature you don't want implement ?
It is a feature I do not what to implement, because it would slow down the code for the overwhelming majority of users who do not want it. (D. Richard Hipp)

MariaDB/MySQL/SQLite avec PHP

PDOStatement::rowCount — Retourne le nombre de lignes affectées par le dernier appel à la fonction PDOStatement::execute()

avec MariaDB/MySQL, retourne le nombre véritable de changements
avec SQLite, retourne le nombre de rows correspondants au critère WHERE

$dbh = new PDO('sqlite:D:\\SQLite\\my_db_test.sqlite');
$query = "UPDATE table_update_count SET val = 'AB'";
echo $query . PHP_EOL;
$stmt = $dbh->prepare($query);
$stmt->execute();
echo $stmt->rowCount(); // 2 (au lieu d'un seul)

mysqli_result::$num_rows -- mysqli_num_rows — Retourne le nombre de lignes dans un résultat

   /* Détermine le nombre de lignes du jeu de résultats */
   $row_cnt = $result->num_rows;

mysqli::$affected_rows -- mysqli_affected_rows — Retourne le nombre de lignes affectées par la dernière opération MySQL

   $mysqli->affected_rows
   mysqli_affected_rows($link)

MYSQL_ATTR_FOUND_ROWS

Fonctions MySQL (PDO_MYSQL) PDO::MYSQL_ATTR_FOUND_ROWS (integer) Retourne le nombre d'enregistrements trouvés, pas le nombre d'enregistrements changés.
MySQL Functions (PDO_MYSQL) PDO::MYSQL_ATTR_FOUND_ROWS (integer) Return the number of found (matched) rows, not the number of changed rows.
php-src/ext/pdo_mysql/pdo_mysql.c REGISTER_PDO_CLASS_CONST_LONG("MYSQL_ATTR_FOUND_ROWS", (zend_long)PDO_MYSQL_ATTR_FOUND_ROWS);
php-src/ext/pdo_mysql/mysql_driver.c if (pdo_attr_lval(driver_options, PDO_MYSQL_ATTR_FOUND_ROWS, 0)) { connect_opts |= CLIENT_FOUND_ROWS; }
Affected rows inconsistent across database engines In D8, we're now requiring PHP 5.3.10, so we should be able to set the flag MYSQL_ATTR_FOUND_ROWS.
Bug #44135 PDO MySQL does not support CLIENT_FOUND_ROWS

MYSQLI_CLIENT_FOUND_ROWS

MYSQLI_CLIENT_FOUND_ROWS Retourne le nombre de ligne trouvées, pas le nombre de
Bug #53425 mysqli_real_connect() ignores client flags when built to call libmysql MYSQLI_CLIENT_FOUND_ROWS are properly passed downlignes affectées.

MariaDB/MySQL avec JAVA/JDBC

Lors d'UPDATE, par défaut, le connecteur JDBC retourne le "matching found rows".
Néanmoins, il est possible d'obtenir le nombre d'"affected rows" du moins avec le connecteur MySQL (mais actuellement pas avec le connecteur MariaDB).
Cela se fait avec l'option de connexion "useAffectedRows=true".

MySQL - Driver/Datasource Class Names, URL Syntax and Configuration Properties for Connector/J

useAffectedRows Default: false

Don't set the CLIENT_FOUND_ROWS flag when connecting to the server (not JDBC-compliant, will break most applications that rely on "found" rows vs. "affected rows" for DML statements), but does cause "correct" update counts from "INSERT ... ON DUPLICATE KEY UPDATE" statements to be returned by the server.

Since version: 5.1.7 (Oct 21, 2008) (Bug #39352 We've added a connection property "useAffectedRows" that allows the driver to connect without setting "CLIENT_FOUND_ROWS", which should be a work-around to this issue, however it can only be set at connect time due to mysqld design, so if your application requires "found rows" functionality in other places, you'll have to maintain two sets of connections.)

If you want the updated count, you can specify useAffectedRows=true as a non-standard URL option.

jdbc:mysql://mariadb.myhost:3306/my_database?useAffectedRows=true

useAffectedRows MariaDB ?

Alternative aux moteurs et connecteurs qui ne retourne pas le "count only changed rows"

UPDATE t SET val='AB' WHERE val<>'AB'; -- when updating
SELECT COUNT(*) FROM t WHERE val <> 'AB'; -- before updating

PlaceOweb Dotclear