duanben1324 2014-03-21 17:30
浏览 52
已采纳

在同一服务器上连接两个相似的数据库

I have two databases with same schema. I need to perform a join on both tables in order to get page results. I am clueless about joining databases. More so since my existing query has joins within first database.

This is my query:

$base = mysql_query("SELECT DISTINCT page.path, 
                page.site_id, 
                (SELECT metadata_custom.value 
                 FROM   metadata_custom 
                 WHERE  field = 'academic search title' 
                        AND page.id = metadata_custom.page_id) AS value, 
                page.id 
FROM   page 
       INNER JOIN metadata 
               ON page.metadata_id = metadata.id 
       INNER JOIN metadata_custom 
               ON page.id = metadata_custom.page_id 
WHERE  field = 'academic search keywords' 
       AND value LIKE 'undergraduate%' 
ORDER  BY value ");  

I need same result but after this first database is joined with second. Any pointers?

Update: There are two connections here, with same host and different userid and password. I am not getting any connection error though but script is not returning anything. Must there be an error here:

    $base = mysql_query("select distinct page.path,page.site_id,
 (select metadata_custom.value from db1.metadata_custom where field='academic search title' and page.id=metadata_custom.page_id) 
 AS value,
page.id from db1.page inner join db1.metadata on db1.page.metadata_id=db1.metadata.id inner join db1.metadata_custom on db1.page.id=db1.metadata_custom.page_id where field='academic search keywords' and
value like 'undergraduate%' 

UNION

select distinct page.path,site_id,(select metadata_custom.value from metadata_custom where field='academic search title' and page.id=metadata_custom.page_id) AS value,
page.id from db2.page inner join db2.metadata on db2.page.metadata_id=db2.metadata.id inner join db2.metadata_custom on db2.page.id=db2.metadata_custom.page_id where field='academic search keywords' and
value like 'undergraduate%' 

 ORDER BY value", $connection);   

db1 and db2 are the two databases here

I used same connection link since I read somewhere that it is connection to server and can be used to access any database on the same server.

Thank you for your help and time, much appreciate it.

  • 写回答

1条回答 默认 最新

  • douqilin4296 2014-03-21 17:39
    关注

    Sounds like you want a UNION or UNION ALL set operator. That operator combines the rows from two different SELECT statements. The two SELECT statements have to return the same number of columns and the same datatype for each column.

    For example:

    SELECT 'foo' AS a, 1 AS b
     UNION ALL
    SELECT 'bar', 3
    

    To query a different database, you need to qualify the table references with the database name

    SELECT t.mycol
      FROM mytable t
     UNION ALL
    SELECT o.somecol
      FROM otherdatabase.mytable o
    

    Given that you are using the DISTINCT keyword in your original query, you can use the UNION operator, which eliminates duplicate rows. (The UNION ALL operator does not perform the extra step of identifying and removing duplicate rows.)


    FOLLOWUP

    A MySQL query can't reference two different connections; a single query can run in one connection only. (Obviously, you can run two separate queries, against two different connections.)

    You said 'another databases', I took that to mean another database on the same MySQL instance.

    A single connection can access any database that it is granted privileges on.

    In order for a user (e.g. user1) to be able to select from tables in each database, that user has to be granted SELECT privilege on the tables. To grant SELECT privilege on all tables in databases db1 and db2 to user1:

    GRANT SELECT ON db1.* TO user1@'%' ;
    GRANT SELECT ON db2.* TO user1@'%' ;
    

    Then user1 can run a query that references tables in both databases:

    SELECT t1.id
      FROM db1.mytable t1
     UNION ALL
    SELECT t2.id
      FROM db2.mytable t2
    

    As a convenient shorthand, we typically do not qualify table references with the database name, and instead rely on the current database being set via a "USE db1;" statement or equivalent.

    For example, mytable here is equivalent to a reference to db1.mytable, the table identified as mytable in the db1 database, (assuming obviously that the USE statement succeeds.)

    USE db1;
    SELECT t1.id
      FROM mytable t1
    

    This way, we can run an identical statement against a different database, with no change to the statement, all we change is the current database:

    USE db2;
    SELECT t1.id
      FROM mytable t1
    

    In this case, mytable is a reference to db2.mytable, the table in the db2 database.

    To reference tables in two different databases, we have to qualify at least the reference to the table in the "other" database.

    Which is why we had to write:

    SELECT t1.id
      FROM mytable t1
     UNION ALL
    SELECT t2.id
      FROM otherdatabase.mytable t2
    

    Note, again, that otherdatabase here is the name of another database on the same MySQL instance. The currently connected user has to have been granted SELECT privilege on otherdatabase.mytable (It could be through a "grant select" on every table in otherdatabase, or it could be global privilege to select from any table in any database.)

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题