Mysql 8 - How to report of all schemas?
We're using MySQL and wish to report on all of their schemas (databases in MySQL terms) in one view.
In older versions of MySQL connectors you had to specify this schema in the data source connection, but with the new 8.0.x jdbc driver, you don’t need the reference in the connection.
This means that you could see tables from all schemas in the data source connection and also in a single view.
The problem is that when you start using tables from different schemas in the view, there are errors because the schema is not included in the SQL queries.
I have the option ticked in the data source connection to ‘include schema’, but I think this more refers to a typical schema within a database, rather than the MySQL schema which is a database.
Has anyone encountered this issue before or have any work arounds?
The best I can at the moment is use virtual tables for all tables and reference the schema in the SQL, but this is not best practice from a query performance side.
The same question
There are a couple of things you can do here at the database level to get you what you want.
Create Database View using sql below, just add in your names.
Here’s the MySQL docs on this;https://dev.mysql.com/doc/refman/8.0/en/union.html
We'd suggestion to use use UNION and not virtual tables. Better to let the DBMS do the heavy lifting. Also, UNION or VIEW running inside the database is going to be better for performance.
Regards,
David
There are a couple of things you can do here at the database level to get you what you want.
Create Database View using sql below, just add in your names.
Here’s the MySQL docs on this;https://dev.mysql.com/doc/refman/8.0/en/union.html
We'd suggestion to use use UNION and not virtual tables. Better to let the DBMS do the heavy lifting. Also, UNION or VIEW running inside the database is going to be better for performance.
Regards,
David
Replies have been locked on this page!