- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
How can we get the total number of rows affected by MySQL query?
MySQL ROW_COUNT() can be used to get the total number of rows affected by MySQL query. To illustrate it we are creating a procedure with the help of which we can insert records in a table and it will show us how many rows have been affected.
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 I row is affected after inserting the data into the ‘testing123’ table.
- Related Articles
- How can we write PHP script to count the affected rows by MySQL query?
- Can we get total number of rows in a MySQL database?
- Create a MySQL stored procedure that counts the number of rows gets affected by MySQL query?
- Which PHP function is used to give the number of rows affected by MySQL query?
- Get total number of rows while using LIMIT in MySQL?
- MySQL command-line tool: How to find out number of rows affected by a DELETE?
- In MySQL, how we can get the total value by category in one output row?
- How can we get all the unique rows in MySQL result set?
- How to get number of rows in a table without using count(*) MySQL query?
- MySQL query to find the number of rows in the last query
- In MySQL, how can we get the number code of a particular character?
- How can we simulate the MySQL INTERSECT query?
- How can we simulate the MySQL MINUS query?
- How can we get the structure of a MySQL view as we can get the structure of a MySQL table?
- How can we get the definition of a MySQL view as we can get the definition of a MySQL table?