如何在2个不同的数据库上保留2个表的联接? [英] How To Left Join 2 Tables On 2 Different Databases?

问题描述

我有第一个数据库(dbA)，它具有这样的表，名为用户名:

I have first database (dbA) with table like this, named Username :

+------------------+--------------+
| Username         | PhoneNumber  |
+------------------+--------------+
| jamesbond007     | 555-0074     |
| batmanbegins     | 555-0392     |
+------------------+--------------+

然后，在另一侧，我有 dbB ，其中的表名为 PrivateMessage :

then, on the other side, I have dbB with table like this, named PrivateMessage :

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

现在，如何组合来自2个不同数据库的这两个表，以便输出看起来像这样:

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  |
+------------------+--------------+---------------------------------+

推荐答案

您可以简单地连接不同数据库的表.您需要在FROM子句中指定数据库名称.要使其更短，请在其上添加ALIAS

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,

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`

，但是在某些情况下，username中没有消息.在这种情况下，如果您仍要显示dba.Username的所有记录，请使用LEFT JOIN.

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.

从您的注释中读取，这些表具有不同的collation.解决方法是在连接的语句中指定COLLATE，

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`

您可以将latin1_swedish_ci更改为所需的任何内容.

you can change latin1_swedish_ci to whatever you want.

有关COLLATION的更多信息，请参阅

For more info on COLLATION, see this full list of

MySQL中的字符集和排序规则

Character Sets and Collations in MySQL

如果您具有足够的权限来ALTER个表，只需使用此语法即可手动转换并匹配它们的排序规则，

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个不同的数据库上保留2个表的联接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！