CODEWITHSUNDEEP
mysqlsqlmysql-workbench
user3609842
user3609842

Reputation: 1

Query entire mysql database

I have a database in mysql, which consists of a few hundred tables (user1,user2,user3..) and each table has the exact same columns. the two specific columns that i am interested in are the 'body' and 'date created'.

What i would like to be able to do is to query all the tables simultaneously for any entry that has a 'date created' value between '2013-08-09' and '2013-08-15' while also presenting me with the equivalent 'body' field.

Currently, I run the following query that brings me back the results only for a single table:

select * 
FROM user1 
where datecreated between '2001-09-09' and '2001-09-15'

and this gives me the 'body' and the 'date created'.

Any ideas?

Upvotes: 0

Views: 60

Answers (1)

Charles Bretana
Charles Bretana

Reputation: 146499

You're going to need to construct a script that creates a temp table, then iterates through all user tables (does MySQL have a way to access the system catalog ?), and then, for each iteration, run a select on that table, and insert data into the temp table for each select executed in the iteration, then output the contents of the temp table.

But I would be errant if I did not suggest, strongly, that you put all this data from all these tables into a single table with a userId column to distinguish which user each row is for. Your database schema design is very very wrong.

Upvotes: 4

Related Questions