How to obtain the affected row count from the last DML statement (INSERT, UPDATE or DELETE) using the ROW_COUNT() function?

ROW_COUNT() is a MySQL built-in function that returns the affected row count from the last DML (Data Manipulation Language) statement (INSERT, UPDATE and DELETE). For example:

CREATE TABLE MyTable (comment CHAR(80));

INSERT INTO MyTable VALUES ('I like it!'), ('Good job1'), ('Best example!');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

SELECT ROW_COUNT();
  -- +-------------+
  -- | ROW_COUNT() |
  -- +-------------+
  -- |           3 |
  -- +-------------+

UPDATE MyTable SET Comment = REPLACE(Comment, 'Good', 'Perfect');
Query OK, 1 row affected (0.01 sec)
Rows matched: 3  Changed: 1  Warnings: 0

SELECT ROW_COUNT();
  -- +-------------+
  -- | ROW_COUNT() |
  -- +-------------+
  -- |           1 |
  -- +-------------+

DELETE FROM MyTable WHERE Comment LIKE '%like%';
Query OK, 1 row affected (0.01 sec)

SELECT ROW_COUNT();
  -- +-------------+
  -- | ROW_COUNT() |
  -- +-------------+
  -- |           1 |
  -- +-------------+

If ROW_COUNT() is called after a DDL (Data Definition Language) statement, it will return 0. For example:

DROP TABLE MyTable;
Query OK, 0 rows affected (0.01 sec)

SELECT ROW_COUNT();
  -- +-------------+
  -- | ROW_COUNT() |
  -- +-------------+
  -- |           0 |
  -- +-------------+

CREATE TABLE MyTable (Comment CHAR(80));
Query OK, 0 rows affected (0.01 sec)

SELECT ROW_COUNT();
  -- +-------------+
  -- | ROW_COUNT() |
  -- +-------------+
  -- |           0 |
  -- +-------------+

If ROW_COUNT() is called after a SELECT statement, it will return -1. For example:

SELECT * FROM MyTable;
Empty set (0.00 sec)

SELECT ROW_COUNT();
  -- +-------------+
  -- | ROW_COUNT() |
  -- +-------------+
  -- |          -1 |
  -- +-------------+

Reference information of the ROW_COUNT() function:

ROW_COUNT(): count
  Returns the affected row count if the last statement is a DML statement, 
  0 if the last statement is DDL statement, or -1 if the last statement 
   is a SELECT statement.

Arguments, return value and availability:
  count: Return value. The affected rows of the last statement.
  Available since MySQL 4.0.

⇒ SCHEMA() - Synonym for DATABASE()

⇐ ROLES_GRAPHML() - User Role Graph in GraphML Format

⇑ MySQL Functions on System Information

⇑⇑ MySQL Function References