mysql - 同步6个不同mysql数据库的用户表数据

Question

我有 6 个 mysql 数据库，它们具有相同的表名，例如 users_table 和 email_address 字段，现在我想从所有 6 个数据库中获取 email_address，这些数据库在任何一个数据库表中都缺失。

请给我一些建议。

Answer 1

此选项选择其中一个数据库中丢失的所有电子邮件，并指出它们存在于哪个数据库中。

SELECT email, GROUP_CONCAT(db_Name) AS emailIsOnlyIn FROM (
   SELECT 'db1' as db_Name, email FROM db1.yourTable
   UNION ALL
   SELECT 'db2' as db_Name, email FROM db2.yourTable
   UNION ALL
   SELECT 'db3' as db_Name, email FROM db3.yourTable
   UNION ALL
   SELECT 'db4' as db_Name, email FROM db4.yourTable
   UNION ALL
   SELECT 'db5' as db_Name, email FROM db5.yourTable
   UNION ALL
   SELECT 'db6' as db_Name, email FROM db6.yourTable
) subquery_alias
GROUP BY email
HAVING COUNT(DISTINCT db_name) < 6;

如果您想知道电子​​邮件在哪个数据库中丢失，那就有点复杂了:

SELECT
*
FROM (
   SELECT email FROM db1.yourTable
   UNION 
   SELECT email FROM db2.yourTable
   UNION 
   SELECT email FROM db3.yourTable
   UNION 
   SELECT email FROM db4.yourTable
   UNION
   SELECT email FROM db5.yourTable
   UNION 
   SELECT email FROM db6.yourTable
) sq,
    (    SELECT 'db1' as db_Name2
       UNION ALL
       SELECT 'db2' as db_Name2
       UNION ALL
       SELECT 'db3' as db_Name2
       UNION ALL
       SELECT 'db4' as db_Name2
       UNION ALL
       SELECT 'db5' as db_Name2
       UNION ALL
       SELECT 'db6' as db_Name2
   )dummy
WHERE NOT EXISTS (SELECT 1 FROM (
   SELECT 'db1' as db_Name, email FROM db1.yourTable
   UNION ALL
   SELECT 'db2' as db_Name, email FROM db2.yourTable
   UNION ALL
   SELECT 'db3' as db_Name, email FROM db3.yourTable
   UNION ALL
   SELECT 'db4' as db_Name, email FROM db4.yourTable
   UNION ALL
   SELECT 'db5' as db_Name, email FROM db5.yourTable
   UNION ALL
   SELECT 'db6' as db_Name, email FROM db6.yourTable
   ) subquery_alias
 WHERE sq.email = subquery_alias.email AND subquery_alias.db_Name = dummy.db_Name2
)

• 这是一个sqlfiddle这演示了概念验证