Collections:
ROW_COUNT() - Affected Rows from Last DML Statement
How to obtain the affected row count from the last DML statement (INSERT, UPDATE or DELETE) using the ROW_COUNT() function?
✍: FYIcenter.com
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
2023-12-19, 235🔥, 0💬
Popular Posts:
What Happens If the Imported Table Already Exists in Oracle? If the import process tries to import a...
How To Verify a User name with SQLCMD Tool in SQL Server? The quickest way to verify a user name in ...
How To Update Multiple Rows with One UPDATE Statement in SQL Server? If the WHERE clause in an UPDAT...
Can Date and Time Values Be Converted into Integers in SQL Server Transact-SQL? Can date and time va...
How To Get the Definition of a Stored Procedure Back in SQL Server Transact-SQL? If you want get the...