Delete rows across multiple databases

Question

I'm using PHP and MySQL. I need to do a query:

DELETE FROM db1.players WHERE acc NOT IN (SELECT id FROM db2.accounts)

The problem is, that db1 and db2 are located on different servers. What is the fastest solution for such problem?

To be precise: I'm using 2 connections, so I think I can't use one query for it.

Answer 1

The syntax database.table.field should actually work in MySQL. But I guess you've to run the query directly on the MySQL server. Not sure about this.

Have you tried the following?

DELETE FROM db1.players WHERE db1.players.acc NOT IN (SELECT db2.accounts.id FROM db2.accounts)

Or did I get you wrong and the two databases are not on the same server? Have a look here: http://www.dottedidesign.com/node/14

Answer 2

I don't know if it is the fastest, but I would create a temporary table on db2 containing account ids, export that table to db1 and run the query there.

For the exporting part you can either use mysql builtin export/import functions, where you would have to consider finding an unused table name or use the CSV-export/import of mysql.

If the expected number of results from the inner query is reasonably small, you can transfer from within PHP using strings:

$ids = query($db1, "SELECT GROUP_CONCAT(id) FROM accounts");
query($db2, "DELETE FROM players WHERE acc NOT IN ($ids)");

Answer 3

I'd try something like:

• create temporary table for the ids on the local server
• statement to fetch ids from remote server
• prepared statement to insert ids into temp. table
• transfering ids remote->local
• DELETE using the temp.table

<?php
$pdoRemote = new PDO('mysql:host=somewhere.else;dbname=foo', '...', '...');
$pdoRemote->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdoLocal = new PDO('mysql:host=localhost;dbname=bar', '...', '...');
$pdoLocal->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$pdoLocal->exec('CREATE TEMPORARY TABLE remoteIds ( id int )'); // maybe engine=heap
$stmtLocal = $pdoLocal->prepare('INSERT INTO remoteIds (id) VALUES (:id)');
$stmtLocal->bindParam(':id', $id);

foreach( $pdoRemote->query('SELECT id FROM accounts') as $row ) {
    $id = $row['id'];
    $stmtLocal->execute();
}
unset($pdoRemote);

Answer 4

You will either have to save the list you want to an array and compare it with the other database

or

You can make a federated table and make it seem like the query is running on 1 database.

Answer 5

To query multiple databases, on different servers, using a single query you can use federated tables.

You could also use replication, or temporary tables. This way you're accessing the databases on the same server.

The only other alternative I can think of would be to use MySQL Proxy to parse your query into 2 or more queries.

I think I'm right in saying that you can't do it the way you are be able to with MSSQL using the sp_addlinkedserver procedure.

You might be better using two queries.