CODEWITHSUNDEEP
mysqldatabase
MTK
MTK

Reputation: 3580

Mysql find value in multiple database (same structure)

With the plan that I have with my hosting, I have size limit for the databases, but I do not have limit in number of database. To solve this I have thought of dividing my databases by countries and each database has the same structure. The problem is how to look up data in all these databases. For example, each database can have a users table. The problem is how to find a user (for example) in all these databases. (Can be 250 of databases)

/*This would be for one database*/
SELECT user_name FROM DB1.users WHERE user_name = 'my_user_name' LIMIT 1;

Is there any way to search 'my_user_name' from all databases ? (Users is only one example ... the value to search can be products , city , coordinates etc etc)

Upvotes: 0

Views: 39

Answers (2)

Z. Bagley
Z. Bagley

Reputation: 9270

As long as they're on the same server you can use the inner join command:

SELECT a.userID, b.usersFirstName, b.usersLastName FROM databaseA.dbo.TableA a inner join database B.dbo.TableB b ON a.userID=b.userID

Upvotes: 1

Dai
Dai

Reputation: 155438

You will need to open separate connections to each database and re-execute the query for each database, then combine the data together in your own code - this will be messy and you will run into problems with colliding primary-keys unless you prefix them all with a database-name qualifier.

You haven't said what language you're using, but in psuedocode it would be like this:

let connectionStrings: String[] {
    "server=dbServer,database=asia",
    "server=dbServer,database=europe",
    "server=dbServer,database=northAmerica"
}

let users = new List<User>()

foreach(connectionString in connectionStrings) {

    let connection = new DBConnection( connectionString )
    let reader = connection.executeQuery( "SELECT * FROM Users" )
    while( reader.read() ) {

        let user = new User() {
            userId = connection.databaseName + reader.get("userId"),
            userName = reader.get("userName"),
            // etc
        }
        users.add( user );
    }
}

return users;

Repeat this for all the queries you want to do.

This is a bad idea - databases are cheap, most shared hosting providers offer gigabyte-sized databases even in their $5/mo plans.

Upvotes: 1

Related Questions