design for style

clients
resources

MySQL query multiple databases

200711Apr

I thought it couldn't be done. I'd searched for it across the web. Terms such as mysql query multiple databases turned up nuthin' - that's right, not just nothing, but _flat-out_nuthin'_.

Today I was speaking with Matt Westgate of Lullabot about the challenges of managing 60+ Drupal web sites on a single codebase with each site having its own database, and Matt (who has a Drupal book coming out) mentioned liking MySQL for the fact that you could query multiple databases with a single query.

"Wha-? Really?" I said. But then it was time to go to my seat as the workshop put on by Matt's company was about to start and I didn't want to miss any of that gold.

But as the presenters ramped up, I began to play on my laptop with what I knew of the syntax for preventing ambiguities in MySQL, trying to guess at how a multi-database query would work, and lo and behold, I put something together that worked:

SELECT arbogast.node.nid, mcguffin.node.nid FROM arbogast.node, mcguffin.node WHERE arbogast.node.nid = 1 AND mcguffin.node.nid = arbogast.node.nid;

Note: arbogast and mcguffin are databases, node is a table common to each database, and nid is a field in each of those tables.

This is great news for me.

If someone could point me to some documentation on this subject, I'd appreciate it...

Update 4/11/07

Perhaps a more fleshed out version of the query would be more meaningful:

SELECT arbogast.node.nid as anid, mcguffin.node.nid as mnid, arbogast.node.title as atitle, mcguffin.node.title as mtitle FROM arbogast.node, mcguffin.node WHERE arbogast.node.nid = 1 AND mcguffin.node.nid = arbogast.node.nid;

tags

MySQL query multiple databases

Does each database need to have a common table name, or can I use a common table field to perform the query from two differently named tables?

I didn't know, so I gave it a try...

What's the connection-string in PHP, C# etc...

So, what environment did you use to run these queries in? It is my understanding that in PHP e.g. you need to run mysql_select_db() first. Once you've done that: you're stuck with one particular database, correct? So how am I going to reference another database if I've just selected one?

In addition: does MySql allow you to create "links" to other databases? E.g., the cross-database referencing would be really cool to exploit in views: making links to other databases that thus seem to be embedded in the original one.

Does anybody have more details on this?

Thanks in advance,

Yves

it just works

thanks

single query for multiple databases

your sample is for multiple databases in a local machine. What if multiple databases located in multiple remote servers?

I too would like to know how

I too would like to know how to query separate databases that are on different servers. So far all I've come up with is making multiple queries and storing everything in a temporary table, and then querying that table for the final result. Less than ideal, but after an exhausting evening with google, it seems to be the only route?

solution for remote server

1. first add the server to your local sql server (from where you want to run the query)

exec sp_addlinkedserver [192.168.1.130]

2. now run the query as

select * from [192.168.1.130].[pubs].[dbo].[authors]

Ummm...

That's correct for Microsoft SQL Server, but won't work for MySQL.

What if the username and

What if the username and password are different (and out of your control) for each database? I am getting a sql command denied error.

Update 9/2008

You can always query multiple db's on the same server.
To query multiple db's on different servers with a single query you need to either; combine the tables using federated tables, replication or temporary tables
In this case you're accessing multiple db's on the same server
or
parse the query
In this case you're splitting the query into two or more queries at the router/proxy server.

This can be done with:

Sql Relay - http://sqlrelay.sourceforge.net/sqlrelay/router.html

Mysql Proxy - http://forge.mysql.com/wiki/MySQL_Proxy

Mysql Federated Storage engine -
link to an example of a hack for combining multiple db server logs in tables
http://www.oreillynet.com/databases/blog/2007/05/hacking_mysql_table_log...

You could write your own query filter/proxy using either a plugin or UDF.

Mysql Plugin - http://dev.mysql.com/doc/refman/5.1/en/plugin-api.html

Mysql UDF - http://dev.mysql.com/doc/refman/5.0/en/adding-functions.html

© 2008