How can we get the total number of rows affected by a MySQL query?

MySQL ROW_COUNT() Can be used to get the total number of rows affected by a MySQL query. To illustrate this, we are creating a procedure through which we can insert records into a table and it will display how many rows are affected.

Example

mysql> Delimiter //
mysql> CREATE PROCEDURE `query`.`row_cnt` (IN command VarChar(60000))
    -> BEGIN
    ->    SET @query = command;
    ->    PREPARE stmt FROM @query;
    ->    EXECUTE stmt;
    ->    SELECT ROW_COUNT() AS 'Affected rows';
    -> END //
Query OK, 0 rows affected (0.00 sec)

mysql> Delimiter ;
mysql> Create table Testing123(First Varchar(20), Second Varchar(20));
Query OK, 0 rows affected (0.48 sec)

mysql> CALL row_cnt("INSERT INTO testing123(First,Second) Values('Testing First','Testing Second');");
+---------------+
| Affected rows |
+---------------+
|             1 |
+---------------+
1 row in set (0.10 sec)

Query OK, 0 rows affected (0.11 sec)

The above result set shows that after inserting data into the ‘testing123’ table, the number of affected rows is 1.

The above is the detailed content of How can we get the total number of rows affected by a MySQL query?. For more information, please follow other related articles on the PHP Chinese website!