ROW_COUNT()
returns a value as follows:
-
DDL statements: 0. This applies to statements such as
CREATE TABLE
orDROP TABLE
. -
DML statements other than
SELECT
: The number of affected rows. This applies to statements such asUPDATE
,INSERT
, orDELETE
(as before), but now also to statements such asALTER TABLE
andLOAD DATA
. -
SELECT
: -1 if the statement returns a result set, or the number of rows “affected” if it does not. For example, forSELECT * FROM t1
,ROW_COUNT()
returns -1. ForSELECT * FROM t1 INTO OUTFILE '
,file_name
'ROW_COUNT()
returns the number of rows written to the file. -
SIGNAL
statements: 0.
For UPDATE
statements, the affected-rows value by default is the number of rows actually changed. If you specify the CLIENT_FOUND_ROWS
flag to mysql_real_connect()
when connecting to mysqld, the affected-rows value is the number of rows “found”; that is, matched by the WHERE
clause.
For REPLACE
statements, the affected-rows value is 2 if the new row replaced an old row, because in this case, one row was inserted after the duplicate was deleted.
For INSERT ... ON DUPLICATE KEY UPDATE
statements, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values. If you specify the CLIENT_FOUND_ROWS
flag, the affected-rows value is 1 (not 0) if an existing row is set to its current values.
The ROW_COUNT()
value is similar to the value from the mysql_affected_rows()
C API function and the row count that the mysql client displays following statement execution.