How to obtain the number of rows affected by a statement in MySQL

In Perl DBI scripts, the row count for statements that modify rows is returned by do():

my $count = $dbh->do ($stmt);

# report 0 rows if an error occurred

printf "Number of rows affected: %d\n", (defined ($count) ? $count : 0);

If you prepare a statement first and then execute it, execute() returns the row count:

my $sth = $dbh->prepare ($stmt);

my $count = $sth->execute ();

printf "Number of rows affected: %d\n", (defined ($count) ? $count : 0);

You can tell MySQL whether to return rows-changed or rows-matched counts by specifying mysql_client_found_rows in the options part of the data source name argument of the connect() call when you connect to the MySQL server. Set the option to 0 for rows-changed counts and 1 for rows-matched counts. Here’s an example:

my %conn_attrs = (PrintError => 0, RaiseError => 1, AutoCommit => 1);

my $dsn = "DBI:mysql:cookbook:localhost;mysql_client_found_rows=1";

my $dbh = DBI->connect ($dsn, "cbuser", "cbpass", \%conn_attrs);

mysql_client_found_rows changes the row-reporting behavior for the duration of the connection.

Although the default behavior for MySQL itself is to return rows-changed counts, recent versions of the Perl DBI driver for MySQL automatically request rows-matched counts unless you specify otherwise. For applications that depend on a particular behavior, it’s best to explicitly set the mysql_client_found_rows option in the DSN to the appropriate value.

For statements that modify rows, Ruby DBI returns row counts similarly to Perl DBI scripts for the do method. That is, do itself returns the count:

count = dbh.do(stmt)

puts "Number of rows affected: #{count}"

If you use execute to execute a statement, execute does not return the row count. Instead, use the statement handle rows method to get the count after executing the statement:

sth = dbh.execute(stmt)

puts "Number of rows affected: #{sth.rows}"

The Ruby DBI driver for MySQL returns rows-changed counts by default, but the driver supports a mysql_client_found_rows option that enables you to control whether the server returns rows-changed or rows-matched counts. Its use is analogous to Perl DBI. For example, to request rows-matched counts, do this:

dsn = "DBI:Mysql:database=cookbook;host=localhost;mysql_client_found_rows=1"

dbh = DBI.connect(dsn, "cbuser", "cbpass")

In PHP, invoke the connection object’s affectedRows() method to find out how many rows a statement changed:

$result =& $conn->query ($stmt);

# report 0 rows if the statement failed

$count = (PEAR::isError ($result) ? 0 : $conn->affectedRows ());

print ("Number of rows affected: $count\n");

Python’s DB-API makes the rows-changed count available as the value of the statement cursor’s rowcount attribute:

cursor = conn.cursor ()

cursor.execute (stmt)

print "Number of rows affected: %d" % cursor.rowcount

To obtain rows-matched counts instead, import the MySQLdb client constants and pass the FOUND_ROWS flag in the client_flag parameter of the connect() method:

import MySQLdb.constants.CLIENT



conn = MySQLdb.connect (db = "cookbook",

                        host = "localhost",

                        user = "cbuser",

                        passwd = "cbpass",

                        client_flag = MySQLdb.constants.CLIENT.FOUND_ROWS)

For statements that modify rows, the MySQL Connector/J JDBC driver provides rows-matched counts rather than rows-changed counts. This is done for conformance with the JDBC specification.

The Java JDBC interface provides row counts two different ways, depending on the method you invoke to execute the statement. If you use executeUpdate(), the row count is its return value:

Statement s = conn.createStatement ();

int count = s.executeUpdate (stmt);

s.close ();

System.out.println ("Number of rows affected: " + count);

If you use execute(), that method returns true or false to indicate whether the statement produces a result set. For statements such as UPDATE or DELETE that return no result set, execute() returns false and the row count is available by calling the getUpdateCount() method:

Statement s = conn.createStatement ();

if (!s.execute (stmt))

{

  // there is no result set, print the row count

  System.out.println ("Number of rows affected: " + s.getUpdateCount ());

}

s.close ();