{"id":6041,"date":"2017-07-05T23:27:25","date_gmt":"2017-07-06T06:27:25","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=6041"},"modified":"2024-01-05T20:20:03","modified_gmt":"2024-01-06T03:20:03","slug":"mysql-row-count","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-row-count\/","title":{"rendered":"MySQL Row Count: How to Get Row Count in MySQL"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn various ways to get MySQL row count in the database.<\/p>\n\n\n\n

Getting MySQL row count of a single table<\/h2>\n\n\n\n

To get the row count of a single table, you use the COUNT(*)<\/a><\/code> in a SELECT<\/code><\/a> statement as follows:<\/p>\n\n\n

SELECT<\/span> \n    COUNT<\/span>(*)\nFROM<\/span>\n    table_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SELECT<\/span> \n    COUNT<\/span>(*)\nFROM<\/span>\n    customers;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n
+----------+\n| COUNT(*) |\n+----------+\n|      122<\/span> |\n+----------+\n1<\/span> row in<\/span> set<\/span> (0.01 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Getting MySQL row count of two or more tables<\/h2>\n\n\n\n
To get the row count of multiple tables, you use the UNION<\/code><\/a> operator to combine result sets returned by each individual SELECT<\/code> statement.<\/p>\n\n\n\n
For example, to get the row count of customers<\/code> and orders<\/code> tables in a single query, you use the following statement.<\/p>\n\n\n
SELECT<\/span> \n    'customers'<\/span> tablename, \n     COUNT<\/span>(*) rows<\/span>\nFROM<\/span>\n    customers \nUNION<\/span> \nSELECT<\/span> \n    'orders'<\/span> tablename, \n     COUNT<\/span>(*) rows<\/span>\nFROM<\/span>\n    orders;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n
+-----------+------+\n| tablename | rows |\n+-----------+------+\n| customers |  122<\/span> |\n| orders    |  326<\/span> |\n+-----------+------+\n2<\/span> rows in<\/span> set<\/span> (0.01 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Getting MySQL row count of all tables in a specific database<\/h2>\n\n\n\n
To get the row count of all tables in a specific database e.g., classicmodels<\/code>, you use the following steps:<\/p>\n\n\n\n
    \n
  1. First, get all table names in the database<\/li>\n\n\n\n
  2. Second, construct an SQL statement that includes all SELECT COUNT(*) FROM table_name<\/code> statements for all tables separated by UNION<\/code>.<\/li>\n\n\n\n
  3. Third, execute the SQL statement using a prepared statement<\/a>.<\/li>\n<\/ol>\n\n\n\n
    First, to get all table names of a database, you query from the information_schema<\/code> database as follows:<\/p>\n\n\n
    SELECT<\/span> \n    table_name\nFROM<\/span>\n    information_schema.tables\nWHERE<\/span>\n    table_schema = 'classicmodels'<\/span>\n        AND<\/span> table_type = 'BASE TABLE'<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n
    +--------------+\n| TABLE_NAME   |\n+--------------+\n| customers    |\n| employees    |\n| offices      |\n| orderdetails |\n| orders       |\n| payments     |\n| productlines |\n| products     |\n+--------------+\n8<\/span> rows in<\/span> set<\/span> (0.02 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
    SELECT<\/span> \n    CONCAT<\/span>(GROUP_CONCAT<\/span>(CONCAT<\/span>('SELECT \\'',\n                        table_name,\n                        '<\/span>\\' table_name,COUNT(*) rows FROM '<\/span>,\n                        table_name)\n                SEPARATOR ' UNION '<\/span>),\n            ' ORDER BY table_name'<\/span>)\nINTO<\/span> @sql<\/span> \nFROM<\/span>\n    table_list;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    In this query, table_list<\/code> is a list of table names which is the result of the query in the first step.<\/p>\n\n\n\n
    SELECT<\/span> \n    CONCAT<\/span>(GROUP_CONCAT<\/span>(CONCAT<\/span>('SELECT \\'',\n                        table_name,\n                        '<\/span>\\' table_name,COUNT(*) rows FROM '<\/span>,\n                        table_name)\n                SEPARATOR ' UNION '<\/span>),\n            ' ORDER BY table_name'<\/span>)\nINTO<\/span> @sql<\/span> \nFROM<\/span>\n    (SELECT<\/span> \n        table_name\n    FROM<\/span>\n        information_schema.tables\n    WHERE<\/span>\n        table_schema = 'classicmodels'<\/span>\n            AND<\/span> table_type = 'BASE TABLE'<\/span>) table_list\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    WITH<\/span> table_list AS<\/span> (\nSELECT<\/span>\n    table_name\n  FROM<\/span> information_schema.tables \n  WHERE<\/span> table_schema = 'classicmodels'<\/span> AND<\/span>\n        table_type = 'BASE TABLE'<\/span>\n) \nSELECT<\/span> CONCAT<\/span>(\n            GROUP_CONCAT<\/span>(CONCAT<\/span>(\"SELECT '\"<\/span>,table_name,\"' table_name,COUNT(*) rows FROM \"<\/span>,table_name) SEPARATOR \" UNION \"<\/span>),\n            ' ORDER BY table_name'<\/span>\n        )\nINTO<\/span> @sql<\/span>\nFROM<\/span> table_list; \n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    PREPARE<\/span> s FROM<\/span>  @sql<\/span>;\nEXECUTE<\/span> s;\nDEALLOCATE<\/span> PREPARE<\/span> s;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    \"MySQL<\/figure>\n\n\n\n
    Getting MySQL row count of all tables in a database with one query<\/h2>\n\n\n\n
    A quick way to get the row count of all tables in a database is by querying data from the information_schema<\/code> database directly:<\/p>\n\n\n
    SELECT<\/span> \n    table_name, \n    table_rows\nFROM<\/span>\n    information_schema.tables\nWHERE<\/span>\n    table_schema = 'classicmodels'<\/span>\nORDER<\/span> BY<\/span> table_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    \"MySQL<\/figure>\n\n\n\n
    This method is sometimes not accurate because the row count in the information_schema and\u00a0the actual row count in the tables are not synchronized. <\/p>\n\n\n\n
    To avoid it, you have to run the ANALYZE TABLE<\/a><\/code> statement before querying the row count from information_schema<\/code> database.<\/p>\n\n\n
    ANALYZE<\/span> TABLE<\/span> table_name, ...;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    In this tutorial, you have learned various ways to get the row count of one or more tables in the MySQL database.<\/p>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
    Third, you execute the @sql<\/code> statement using the prepared statement<\/a> as follows:<\/p>\n\n\n
    If you are using MySQL 8.0+, you can use a MySQL CTE<\/a> (common table expression) instead of a derived table:<\/p>\n\n\n
    The following query uses the first query as a derived table<\/a> and returns an SQL statement as a string.<\/p>\n\n\n
    Second, to construct the SQL statement, we use the GROUP_CONCAT<\/code> <\/a>and CONCAT<\/code> <\/a>functions as follows:<\/p>\n\n\n
For example, to get the number of rows in the customers<\/code> table in the sample database<\/a>, you use the following statement:<\/p>\n\n\n