MySQL View from different database with the same table schema

Question

I am in a situation right now where I have to create a MySQL view from different databases on the same host. The team that created the data used different databases for every year (like sales_2014, sales_2015) etc.

All databases have the same tables with almost the same schema. Only some columns are different, but i only need those that are the same so I don't have to care about those that are different.

For my project I need to have that data in one view or table. What is the best way to do this? Should I create a view for every database and connect all views via a single view on a different database, should I just use one big view for all tables from the different databases or should i use something different than views?

Answer 1

Simply reference whatever databases you want. Note the "db.tbl" syntax:

CREATE VIEW ...
    AS
    SELECT ...
        FROM sales_2014.blah AS x
        JOIN sales_2015.blah AS y ON ...

Similarly, you could use UNION ALL.

CREATE OR REPLACE VIEW
    AS all_sales
        SELECT * FROM 2014.sales
    UNION ALL
        SELECT * FROM 2015.sales;

For 'sanity', you might want to create a new database to store the VIEWs in. You might call it sales_all.