MySQL is a relational database management system that organizes your data in a table form.
Each MySQL table can store many rows of records. To find out how many rows or records a single table has, you can use the SELECT or SELECT COUNT statement.
For example, suppose you have a MySQL scores table with the following entries:
+----+-------+-------+
| id | name | score |
+----+-------+-------+
| 1 | Jack | 4.9 |
| 2 | Susan | 7.4 |
| 3 | Mark | 6.73 |
| 4 | Fany | 9.15 |
+----+-------+-------+
When you use the SELECT statement with any column that you want to retrieve, MySQL will return the result set together with information on how many rows are returned by the query.
Now let’s select the id column from the scores table. Here’s the result returned by MySQL:
mysql> SELECT id FROM scores;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
+----+
4 rows in set (0.00 sec)
As you can see above, MySQL responds with 4 rows in set right after the query result.
Besides the SELECT query, you can also use the COUNT() function to count how many rows you have in your MySQL table.
Take a look at the following MySQL query:
SELECT COUNT(*) FROM scores;
The asterisk symbol basically means all rows in MySQL, so passing the symbol to the COUNT() function will cause MySQL to count all rows you have in the table.
The returned result set will be as shown below:
mysql> SELECT COUNT(*) FROM scores;
+----------+
| COUNT(*) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)
Now you can see that the query COUNT(*) returns four because the table contains four data rows.
The text says 1 row in set because the query table only returns a single row count.
Note that the COUNT(*) function will count both null and non null values. When you pass a column that has NULL value to the COUNT() function, the result will be different.
Let’s adjust the example table and add a NULL value to the name column:
+----+-------+-------+
| id | name | score |
+----+-------+-------+
| 1 | Jack | 4.9 |
| 2 | Susan | 7.4 |
| 3 | NULL | 6.73 |
| 4 | Fany | 9.15 |
+----+-------+-------+
Now when you count the name column, the result set will be as shown below:
mysql> SELECT count(id), count(name) FROM scores;
+-----------+-------------+
| count(id) | count(name) |
+-----------+-------------+
| 4 | 3 |
+-----------+-------------+
1 row in set (0.01 sec)
As you can see, counting the name column only returns three while counting the id column returns four.
It’s recommended to use COUNT(*) instead of passing an arbitrary column when you need to find how many rows your MySQL table has.
And that’s how you can get the number of records or rows in a table using MySQL 😉