Get data of all tables in database-CodePudding

I'm using mysql version 5.7, it has around 2000 tables, each of tables has columns gmt_modified as mandatory.
So now i want to build a report to see how much data in each time range, eg: 3-6-9-12 months.
what i'm about to do is base on gmt_modified (timestamp) to do that.
I just wonder mysql support the way to build that report or not
or anybody know how can we select gmt_modified of all tables in a schema?
We can list columns in all tables by this way:

select * 
  from information_schema.columns 
 where table_schema = 'your_DB_name' 
   and table_name = 'Your_tablename'

but how to select data of column as well?
Thanks

CodePudding user response：

You can write an SQL that writes an SQL, and then copy the SQL into the query window and execute it

  select CONCAT('
    SELECT ''', table_name, ''', (YEAR(CURDATE())*12   MONTH(CURDATE()) - (YEAR(gmt_modified)*12   MONTH(gmt_modified)))/3 as period, COUNT(*) as ctr 
    FROM ', table_name, ' 
    WHERE gmt_modified > DATE_ADD(curdate(), INTERVAL Year -1) 
    GROUP BY period UNION ALL'
  )

  from information_schema.columns 
  where column_name = 'gmt_modified'

This will essentially write 2000 queries (or however many tables have a gmt_modified column) as a query result that you can then copy into another query window, remove the trailing UNION ALL that is causing a syntax error, and then run it. It might take a long time..

Note, I've picked on a relatively naive way of calculating a month diff: months are difficult because they vary in length. Perhaps a 30/60/90 days etc would suit better, do a period diff on hours and divide by 24 then 30

CodePudding user response：

To approach this problem, you first need to generate main text of your query from a query-building-query... something like this:

select concat('  select gmt_modified FROM ',table_name,' UNION ALL')
from information_schema.tables where table_schema = 'your_DB_name';

This will give you output something like:

select gmt_modified FROM table1 UNION ALL
select gmt_modified FROM table2 UNION ALL
select gmt_modified FROM table3 UNION ALL
select gmt_modified FROM table4 UNION ALL
select gmt_modified FROM table5 UNION ALL
select gmt_modified FROM table6 UNION ALL
select gmt_modified FROM table7 UNION ALL

Remove the last words UNION ALL from last line. Then you wrap it with the aggregation query. like this

select count(gmt_modified) as record_count FROM (
    select gmt_modified FROM table1 UNION ALL
    select gmt_modified FROM table2 UNION ALL
    select gmt_modified FROM table3 UNION ALL
    select gmt_modified FROM table4 UNION ALL
    select gmt_modified FROM table5 UNION ALL
    select gmt_modified FROM table6 UNION ALL
    select gmt_modified FROM table7
) details
GROUP BY QUARTER(gmt_modified);

So basically, you'll need to regenerate the main text, each time you remove/add a table to the db.