Is there a way to get the count of rows in all tables in a MySQL database without running a SELECT count()
on each table?
相关问题
- SQL join to get the cartesian product of 2 columns
- sql execution latency when assign to a variable
- Difference between Types.INTEGER and Types.NULL in
- php PDO::FETCH_ASSOC doesnt detect select after ba
- sqlyog export query result as csv
This stored procedure lists tables, counts records, and produces a total number of records at the end.
To run it after adding this procedure:
-
The Procedure:
This is what I do to get the actual count (no using the schema)
It's slower but more accurate.
It's a two step process at
Get list of tables for your db. You can get it using
Create and assign the list of tables to the array variable in this bash script (separated by a single space just like in the code below)
Run it:
That's all you need.
If you want the exact numbers, use the following ruby script. You need Ruby and RubyGems.
Install following Gems:
File: count_table_records.rb
Go back to the command-line:
Output:
You can probably put something together with Tables table. I've never done it, but it looks like it has a column for TABLE_ROWS and one for TABLE NAME.
To get rows per table, you can use a query like this:
Note from the docs though: For InnoDB tables, the row count is only a rough estimate used in SQL optimization. You'll need to use COUNT(*) for exact counts (which is more expensive).