{"id":18168,"date":"2021-06-30T09:09:49","date_gmt":"2021-06-29T23:09:49","guid":{"rendered":"https:\/\/database.guide\/?p=18168"},"modified":"2021-06-30T09:09:49","modified_gmt":"2021-06-29T23:09:49","slug":"mariadb-row_count-explained","status":"publish","type":"post","link":"https:\/\/database.guide\/mariadb-row_count-explained\/","title":{"rendered":"MariaDB ROW_COUNT() Explained"},"content":{"rendered":"\n

In MariaDB, ROW_COUNT()<\/code> is a built-in function that returns the number of rows updated, inserted or deleted by the preceding statement.<\/p>\n\n\n\n

The value returned by ROW_COUNT()<\/code> is the same as the row count that the mysql<\/code> client displays and the value from the mysql_affected_rows()<\/code> C API function.<\/p>\n\n\n\n\n\n\n\n

Syntax<\/h2>\n\n\n\n

The syntax goes like this:<\/p>\n\n\n\n

ROW_COUNT()<\/code><\/pre>\n\n\n\n
No arguments are required, or accepted.<\/p>\n\n\n\n
Examples<\/h2>\n\n\n\n
DDL Statements<\/h3>\n\n\n\n
For DDL statements (including TRUNCATE<\/code>) and for other statements which don’t return any result set (such as USE<\/code>, DO<\/code>, SIGNAL<\/code> or DEALLOCATE PREPARE<\/code>), the ROW_COUNT()<\/code> function returns 0<\/code>.<\/p>\n\n\n\n
Let’s create a table:<\/p>\n\n\n\n
CREATE OR REPLACE TABLE guest (\n  guest_id INT NOT NULL AUTO_INCREMENT,\n  guest_name VARCHAR(255) NOT NULL,\n  PRIMARY KEY (guest_id)\n);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
Query OK, 0 rows affected (0.046 sec)<\/pre>\n\n\n\n
And run ROW_COUNT()<\/code>:<\/p>\n\n\n\n
SELECT ROW_COUNT();<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+-------------+\n| ROW_COUNT() |\n+-------------+\n|           0 |\n+-------------+<\/pre>\n\n\n\n
As expected, 0<\/code> is returned, because zero rows were affected.<\/p>\n\n\n\n
DML Statements<\/h3>\n\n\n\n
For DML statements other than SELECT<\/a><\/code> and for ALTER TABLE<\/a><\/code>, the ROW_COUNT()<\/code> function returns the number of affected rows.<\/p>\n\n\n\n
Below are some examples.<\/p>\n\n\n\n
Insert Data<\/h4>\n\n\n\n
Now let’s insert some rows:<\/p>\n\n\n\n
INSERT INTO guest (guest_name) VALUES ('Homer');\nINSERT INTO guest (guest_name) VALUES ('Bart');\nINSERT INTO guest (guest_name) VALUES ('Marge');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
MariaDB [Zap]> INSERT INTO guest (guest_name) VALUES ('Homer');\nQuery OK, 1 row affected (0.037 sec)\n\nMariaDB [Zap]> INSERT INTO guest (guest_name) VALUES ('Bart');\nQuery OK, 1 row affected (0.001 sec)\n\nMariaDB [Zap]> INSERT INTO guest (guest_name) VALUES ('Marge');\nQuery OK, 1 row affected (0.002 sec)<\/pre>\n\n\n\n
Each statement resulted in one row being affected.<\/p>\n\n\n\n
And let’s run ROW_COUNT()<\/code> again:<\/p>\n\n\n\n
SELECT ROW_COUNT();<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+-------------+\n| ROW_COUNT() |\n+-------------+\n|           1 |\n+-------------+<\/pre>\n\n\n\n
This returns 1<\/code> because that’s how many rows were affected in the last statement. Even though we affected three rows, it took three statements to do that (each statement inserted just one row, and ROW_COUNT()<\/code> only reports on the last statement). <\/p>\n\n\n\n
Update Data<\/h4>\n\n\n\n
Now let’s update the data in all three rows:<\/p>\n\n\n\n
UPDATE guest\nSET guest_name = 'Homer';<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
Query OK, 2 rows affected (0.008 sec)\nRows matched: 3  Changed: 2  Warnings: 0<\/pre>\n\n\n\n
My mariadb<\/code> client tells me that although three rows matched the criteria, just two rows were changed. This is because the first row already contains Homer<\/code>, which is also what we’re trying to update it to.<\/p>\n\n\n\n
Let’s see what ROW_COUNT()<\/code> returns:<\/p>\n\n\n\n
SELECT ROW_COUNT();<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+-------------+\n| ROW_COUNT() |\n+-------------+\n|           2 |\n+-------------+<\/pre>\n\n\n\n
As expected, it returns 2<\/code>, because that’s how many rows were actually updated.<\/p>\n\n\n\n
Result Set Statements<\/h3>\n\n\n\n
For statements that return a result set (such as SELECT<\/code>, SHOW<\/code>, DESC<\/code> or HELP<\/code>), the ROW_COUNT()<\/code> function returns -1<\/code>, even when the result set is empty. This is also true for administrative statements, such as OPTIMIZE<\/code>.<\/p>\n\n\n\n
Example:<\/p>\n\n\n\n
SELECT * FROM guest;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+----------+------------+\n| guest_id | guest_name |\n+----------+------------+\n|        1 | Homer      |\n|        2 | Homer      |\n|        3 | Homer      |\n+----------+------------+\n3 rows in set (0.000 sec)<\/pre>\n\n\n\n
Now call ROW_COUNT()<\/code> again:<\/p>\n\n\n\n
SELECT ROW_COUNT();<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+-------------+\n| ROW_COUNT() |\n+-------------+\n|          -1 |\n+-------------+<\/pre>\n\n\n\n
Foreign Keys & Triggers<\/h2>\n\n\n\n
Note that ROW_COUNT()<\/code> does not take into account rows that are not directly deleted\/updated by the last statement. This means that rows deleted by foreign keys<\/a> or triggers are not counted.<\/p>\n\n\n\n
More Information<\/h2>\n\n\n\n
See the MariaDB documentation<\/a> for more details and a few other things to be mindful of when using this function. <\/p>\n","protected":false},"excerpt":{"rendered":"
In MariaDB, ROW_COUNT() is a built-in function that returns the number of rows updated, inserted or deleted by the preceding statement. The value returned by ROW_COUNT() is the same as the row count that the mysql client displays and the value from the mysql_affected_rows() C API function.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[117],"tags":[93,20],"class_list":["post-18168","post","type-post","status-publish","format-standard","hentry","category-mariadb","tag-functions","tag-what-is"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/18168","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=18168"}],"version-history":[{"count":14,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/18168\/revisions"}],"predecessor-version":[{"id":18234,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/18168\/revisions\/18234"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=18168"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=18168"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=18168"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}