使用PHP，比较两个单列MYSQL表，添加表A中不存在于表B中的记录，并删除执行的记录

Question

I have two tables in a DB, A and B. Each of the tables consist of just one column, email. So I want to check each email in table B against table A, and if it exists, delete it; if it does not exist, add it.

How do I do this?

Answer 1

Ideally, you should really do this in pure SQL, without having to muck about with php.

Let's say your table A has column a integer and table B has column b integer. Then you can do something like this:

create temporary table X as select a from A join B on A.a=B.b;
delete from B where b in (select a from X);
delete from A where a in (select a from X);
insert into A (a) (select b from B);

This does the following:

1. Creates a temporary table and insert into it all records that exist in both A and B
2. Deletes from B all records that exist in both tables
3. Deletes from A all records that exist in both tables
4. Insert into A everything that's remaining in B

As table X is created as temporary, it will be automatically dropped when the database connection is closed.

Now, if you need to call this from PHP, you can do the following:

$db = new PDO($CONNECT_STRING, USERNAME, PASSWORD);
$db->exec("create temporary table X as select a from A join B on A.a=B.b");
$db->exec("delete from B where b in (select a from X)");
$db->exec("delete from A where a in (select a from X)");
$db->exec("insert into A (select b from B)");
$db = null;

EDIT:

If all you need is records from B that do not exist in A, then you can do a simple SQL like this:

select b from B where b not in (select a from A)