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, 243🔥, 0💬
Popular Posts:
What Is a Parameter File in Oracle? A parameter file is a file that contains a list of initializatio...
How To Fix the INSERT Command Denied Error in MySQL? The reason for getting the "1142: INSERT comman...
How To Turn on mysql Extension on the PHP Engine in MySQL? The "mysql" API extension is provided as ...
Where to find answers to frequently asked questions on INSERT, UPDATE and DELETE Statements in MySQL...
What Is Program Global Area (PGA) in Oracle? A Program Global Area (PGA) is a memory buffer that is ...