如何在2个不同的数据库中加入2个表?

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 dbB with 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 :

现在，如何从两个不同的数据库中合并这两个表，使输出看起来像这样:

+------------------+--------------+---------------------------------+
| Username         | PhoneNumber  | Message                         |
+------------------+--------------+---------------------------------+
| jamesbond007     | 555-0074     | I need new bond-girl            |
| batmanbegins     | 555-0392     | thanks for the paycheck, Nolan  |
+------------------+--------------+---------------------------------+

4 个解决方案

#1

You can simply join the table of different database. You need to specify the database name in your FROM clause. To make it shorter, add an ALIAS on it,

您可以简单地加入不同数据库的表。您需要在FROM子句中指定数据库名。为了使它更短，添加一个别名，

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 username won't have messages. In this case use LEFT JOIN if you want still to show all the records of dba.Username.

但是，在用户名中有可能没有消息。在这种情况下，如果您还想显示所有的dba.Username记录，请使用LEFT JOIN。

Reading from your comments, the tables have different collation. The work around on this is to specify COLLATE on your joined statements,

阅读你的评论，表格有不同的排序。这方面的工作是在你的连接语句中指定排序，

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_ci to whatever you want.

您可以将latin1_swedish_ci更改为您想要的任何类型。

For more info on COLLATION, see this full list of

要了解更多的整理信息，请查看完整的列表

Character Sets and Collations in MySQL

MySQL中的字符集和排序

---

If you have enough privilege to ALTER the tables, simply use this syntax to manually convert and match their collations,

如果您有足够的权限来修改表，只需使用此语法手工转换和匹配它们的排序，

ALTER TABLE tbl_name CONVERT TO CHARACTER SET latin2 COLLATE 'latin2_general_ci';

#2

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 JOIN to retrieve only users with messages)
• 左连接将返回所有用户，也会返回那些没有消息的用户(使用内连接只检索有消息的用户)
• Users with multiple messages will appear multiple times (use aggregations and GROUP BY to only retrieve one message per user - you'll have to supply a criterion to choose the one message)
• 拥有多条消息的用户将多次出现(使用聚合和分组BY，每个用户只能检索一条消息——您必须提供一个选择一条消息的标准)
• 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)
• 您需要两个数据库上的查询特权(否则，两个数据库上都有特权的一些用户必须进行复制，例如在crontab中，表或表的子集从数据库到另一个数据库)
• Collations might not match. If this is the case, you have to change collation on one of the two tables using either COLLATE or 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 verify that 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).
• 排序可能不匹配。如果是这样，您必须使用COLLATE或使用CONVERT: CONVERT(DB .table)将一个DB的字段转换为另一个DB的字符集，对两个表中的一个表进行排序。字段使用Latin1)，这将防止使用索引从而降低性能。您可以修改这两个表中的一个，但是要验证您没有破坏使用ALTER'ed表的查询或应用程序(在必要时，将整个数据库转换为经过良好调和的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 userid to 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.
• 即使在两个表中都有索引，在文本字段上的连接也不是很有效;最好让消息表持有一个惟一的数字userid来引用消息所有者。我理解使用不同逻辑的两个不同的数据库可能不利于这个解决方案，但是您可以应用上面的“技巧”(“复制一个表或它的子集”)，并定期将转换后的和ID化的表从DB导出到另一个DB。一个周期性的查询将会非常昂贵，但是所有后续的连接都将从中受益。

Test run

This creates two tables with the same structure in two different databases, and joins them while in a third database.

这将在两个不同的数据库中创建具有相同结构的两个表，并在第三个数据库中合并它们。

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>

#3

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).

如果无法访问它们，则必须使用不同的方法(比如在查询或类似的情况之前将一个表复制到另一个数据库)。

#4

Try the below code

尝试以下代码

SELECT * FROM dbA.Username JOIN dbB.PrivateMessage USING(Username);