The ROW_COUNT() function in MySQL is used to obtain the number of rows affected by the last statement that directly or indirectly modifies a table. This can be particularly useful in scenarios where you want to know the number of rows affected by an INSERT, UPDATE, or DELETE statement.

Syntax

Here is the basic syntax of the ROW_COUNT() function:

SELECT ROW_COUNT();

This function doesn’t require any arguments and can be used in a standalone SELECT statement or as part of a larger query.

Examples

Now, let’s look at a few examples to illustrate the usage of the ROW_COUNT() function:

Using ROW_COUNT() with an INSERT statement

INSERT INTO your_table (column1, column2, column3)
VALUES (value1, value2, value3);

SELECT ROW_COUNT();

In this example, the INSERT statement is executed first, adding a new row to the table. Subsequently, the ROW_COUNT() function is used to retrieve the number of rows affected, which, in this case, would be 1.

Using ROW_COUNT() with an UPDATE statement

UPDATE your_table
SET column1 = new_value
WHERE some_condition;

SELECT ROW_COUNT();

This example demonstrates how to use ROW_COUNT() with an UPDATE statement. The function will return the number of rows that were updated based on the specified condition.

Using ROW_COUNT() with a DELETE statement

DELETE FROM your_table
WHERE some_condition;

SELECT ROW_COUNT();

Here, the ROW_COUNT() function is employed to obtain the number of rows deleted by the DELETE statement.

Using ROW_COUNT() in a stored procedure

CREATE PROCEDURE UpdateAndCount()
BEGIN
    UPDATE your_table
    SET column1 = new_value
    WHERE some_condition;

    SELECT ROW_COUNT() AS 'RowsAffected';
END //
DELIMITER ;

CALL UpdateAndCount();

This example showcases the use of ROW_COUNT() within a stored procedure. The procedure executes an UPDATE statement and then returns the number of affected rows using SELECT ROW_COUNT() AS ‘RowsAffected’;.

In summary, the ROW_COUNT() function in MySQL provides a convenient way to retrieve the number of rows affected by the last executed SQL statement, enabling developers to handle and analyze the impact of their queries.