MySQL 如何在2个不同的数据库上左连接2个表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12685193/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How To Left Join 2 Tables On 2 Different Databases?
提问by Saint Robson
I have first database (dbA)with table like this, named Username:
我有第一个数据库(dbA)和这样的表，名为Username：
+------------------+--------------+
| Username | PhoneNumber |
+------------------+--------------+
| jamesbond007 | 555-0074 |
| batmanbegins | 555-0392 |
+------------------+--------------+
then, on the other side, I have dbBwith table like this, named PrivateMessage:
然后，在另一方面，我有dbB和这样的表，名为PrivateMessage：
+------------------+---------------------------------+
| Username | Message |
+------------------+---------------------------------+
| jamesbond007 | I need new bond-girl |
| batmanbegins | thanks for the paycheck, Nolan |
+------------------+---------------------------------+
now, how to combine this two tables from 2 different databases so the output will look like this :
现在，如何组合来自 2 个不同数据库的这两个表，以便输出如下所示：
+------------------+--------------+---------------------------------+
| Username | PhoneNumber | Message |
+------------------+--------------+---------------------------------+
| jamesbond007 | 555-0074 | I need new bond-girl |
| batmanbegins | 555-0392 | thanks for the paycheck, Nolan |
+------------------+--------------+---------------------------------+
采纳答案by John Woo
You can simply join the table of different database. You need to specify the database name in your FROMclause. To make it shorter, add an ALIASon it,
您可以简单地加入不同数据库的表。您需要在您的FROM子句中指定数据库名称。为了使它更短，添加一个ALIAS，
SELECT a.*, -- this will display all columns of dba.`UserName`
b.`Message`
FROM dba.`UserName` a -- or LEFT JOIN to show all rows whether it exists or not
INNER JOIN dbB.`PrivateMessage` b
ON a.`username` = b.`username`
but some how, there are possiblities where-in a usernamewon't have messages. In this case use LEFT JOINif you want still to show all the records of dba.Username.
但有些方法，有可能username不会有消息。在这种情况下，LEFT JOIN如果您仍想显示dba.Username.
Reading from your comments, the tables have different collation. The work around on this is to specify COLLATEon your joined statements,
从您的评论中阅读，表格有不同的collation. 解决此问题的方法是COLLATE在您的连接语句中指定，
SELECT a.*, -- this will display all columns of dba.`UserName`
b.`Message`
FROM dba.`UserName` COLLATE latin1_swedish_ci a
LEFT JOIN dbB.`PrivateMessage` COLLATE latin1_swedish_ci b
ON a.`username` = b.`username`
you can change latin1_swedish_cito whatever you want.
您可以更改latin1_swedish_ci为任何您想要的。
For more info on COLLATION, see this full list of
有关 COLLATION 的更多信息，请参阅此完整列表
Character Sets and Collations in MySQL
If you have enough privilege to ALTERthe tables, simply use this syntax to manually convert and match their collations,
如果您ALTER对表有足够的权限，只需使用此语法手动转换和匹配它们的排序规则，
ALTER TABLE tbl_name CONVERT TO CHARACTER SET latin2 COLLATE 'latin2_general_ci';
回答by LSerni
Same as you would a normal table, except specifying the database:
与普通表相同，除了指定数据库：
SELECT dbA.Username, dbA.PhoneNumber, dbB.Message
FROM dbA.Username LEFT JOIN dbB.PrivateMessage
ON (dbA.UserName.Username = dbB.PrivateMessage.Username);
Things to look out for:
需要注意的事项：
- LEFT JOIN will return all users, also those with no messages (use
INNER JOINto retrieve only users withmessages) - Users with multiple messages will appear multiple times (use aggregations and
GROUP BYto only retrieve one message per user - you'll have to supply a criterion to choose the one message) - You need query privileges on both databases (otherwise some user with privileges on both has to copy, e.g. periodically in crontab, a table or a subset of a table from a database to the other)
- Collations might not match. If this is the case, you have to change collation on one of the two tables using either COLLATEor converting the field of one DB to the charset of the other with CONVERT: CONVERT(db.table.field USING Latin1), which will prevent using indexes thus decreasing performances. You can modify one of the two tables, but verifythat you're not disrupting whatever query or application is using the
ALTER'ed table (in a pinch, convert the whole database to well-tempered UTF8). JOINs on text fields aren't very efficient even if you have INDEX on that in both tables; it would be better to have the Message table holding a unique, numeric useridto refer to the message owner. I understand that two different databases with different logics might not be conducive to this solution, but you could apply one of the above "tricks" ("copy a table or subset thereof") and export, periodically, a converted and ID'ed table from a DB to the other. That one periodical query would be expensive, but all subsequent JOINs would greatly benefit.
- LEFT JOIN 将返回所有用户，也包括没有消息的
INNER JOIN用户（用于仅检索有消息的用户） - 有多条消息的用户将出现多次（使用聚合并且
GROUP BY每个用户只检索一条消息 - 您必须提供一个标准来选择一条消息） - 您需要对两个数据库的查询权限（否则某些对这两个数据库都具有权限的用户必须定期复制，例如在 crontab 中，从数据库到另一个表或表的子集）
- 排序规则可能不匹配。如果是这种情况，您必须使用COLLATE更改两个表之一的排序规则，或者使用CONVERT将一个 DB 的字段转换为另一个的字符集：CONVERT(db.table.field USING Latin1)，这将防止使用索引从而降低性能。您可以修改两个表中的一个，但要确认您没有中断使用
ALTER'ed 表的任何查询或应用程序（在紧要关头，将整个数据库转换为良好的 UTF8）。 JOIN即使在两个表中都有 INDEX，文本字段上的 s 也不是很有效；最好让 Message 表保存一个唯一的数字用户 ID来引用消息所有者。我知道具有不同逻辑的两个不同的数据库可能不利于此解决方案，但您可以应用上述“技巧”之一（“复制表或其子集”）并定期导出转换后的 ID 表从一个数据库到另一个。那个定期查询会很昂贵，但所有后续的 JOIN 都会大大受益。
Test run
测试运行
This creates two tables with the same structure in two different databases, and joins them while in a thirddatabase.
这会在两个不同的数据库中创建两个具有相同结构的表，并在第三个数据库中连接它们。
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.6.30 openSUSE package Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> CREATE DATABASE first_database; Query OK, 1 row affected (0.01 sec) mysql> CREATE DATABASE second_database; Query OK, 1 row affected (0.00 sec) mysql> USE first_database; Database changed mysql> CREATE TABLE mytable ( x integer, t varchar(32) ); Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO mytable ( x, t ) VALUES ( 1, 'One in First Database' ), ( 2, 'Two in First Database' ); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> USE second_database; Database changed mysql> CREATE TABLE mytable ( x integer, t varchar(32) ); Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO mytable ( x, t ) VALUES ( 1, 'One in Second Database' ), ( 3, 'Three in Second Database' ); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> USE test; Database changed mysql> SELECT * FROM first_database.mytable LEFT JOIN second_database.mytable USING ( x ); +------+-----------------------+------------------------+ | x | t | t | +------+-----------------------+------------------------+ | 1 | One in First Database | One in Second Database | | 2 | Two in First Database | NULL | +------+-----------------------+------------------------+ 2 rows in set (0.00 sec) mysql>
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.6.30 openSUSE package Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> CREATE DATABASE first_database; Query OK, 1 row affected (0.01 sec) mysql> CREATE DATABASE second_database; Query OK, 1 row affected (0.00 sec) mysql> USE first_database; Database changed mysql> CREATE TABLE mytable ( x integer, t varchar(32) ); Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO mytable ( x, t ) VALUES ( 1, 'One in First Database' ), ( 2, 'Two in First Database' ); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> USE second_database; Database changed mysql> CREATE TABLE mytable ( x integer, t varchar(32) ); Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO mytable ( x, t ) VALUES ( 1, 'One in Second Database' ), ( 3, 'Three in Second Database' ); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> USE test; Database changed mysql> SELECT * FROM first_database.mytable LEFT JOIN second_database.mytable USING ( x ); +------+-----------------------+------------------------+ | x | t | t | +------+-----------------------+------------------------+ | 1 | One in First Database | One in Second Database | | 2 | Two in First Database | NULL | +------+-----------------------+------------------------+ 2 rows in set (0.00 sec) mysql>
回答by Naveen Jain
Try the below code
试试下面的代码
SELECT * FROM dbA.Username JOIN dbB.PrivateMessage USING(Username);
回答by Bjoern
The SQL for this is rather easy...
用于此的 SQL 相当简单......
SELECT A.Username, A.PhoneNumber, B.Message
FROM dbA.Username as A
INNER JOIN dbB.PrivateMessage as B ON A.Username = B.Username
...assuming you can access both databases within your connection.
...假设您可以在连接中访问两个数据库。
If you cannot access them, you have to work on a different approach (like copying one table to the other database before querying or something similar).
如果您无法访问它们，则必须采用不同的方法（例如在查询之前将一个表复制到另一个数据库或类似的方法）。
