Drupal 中的跨数据库表联接？
drupal 中可以进行跨数据库连接吗？
假设您有两个数据库：drupal 和 old_data_source。您想要将 drupal.node 加入到 old_data_source.page。
可以调用db_set_active()来使用多个数据库。但是找不到任何有关跨数据库表连接的信息！
（我正在将此作为迁移脚本的一部分进行研究，并将删除 old_data_source）
Is it possible to do cross database joins in drupal?
Say you have two databases, drupal and old_data_source. You want to join drupal.node to old_data_source.page.
db_set_active() can be called to use multiple databases. However can't find anything about cross database table joins!
(I'm looking into this as part of a migration script and will be removing old_data_source)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
有一个鲜为人知的技巧可以实现这一点，它依赖于称为表前缀的功能。您可以配置 settings.php 以使用另一个数据库的名称作为查询中某些表的前缀，以便可以进行跨数据库联接。
查看 default.settings.php（类似的语法适用于 Drupal 6）以了解如何进行设置。对于最初的问题，您的默认 $databases 条目上会有一个前缀数组，如下所示：
然后使用以下内容构建一个查询：
Drupal DB 抽象层将解释 {page} 并将其转换为 SQL就像：
唯一需要确保的是与默认 Drupal 数据库关联的数据库用户具有访问这两个数据库的权限，否则此技巧将不起作用。
There is a little-known trick to accomplish this, which relies on a feature called table prefixing. You can configure your settings.php to prefix certain tables in your queries with the name of another database, so you can do cross-database joins.
Have a look at the examples in the comments of the default.settings.php (similar syntax applies in Drupal 6) to see how to set this up. In the case of the original question, you'd have a prefix array on your default $databases entry that looks like:
And then build a query with something like:
The Drupal DB abstraction layer will interpret the {page} and convert it to SQL like:
The only other thing to make sure of is that the database user associated with your default Drupal database has privileges to access both databases, or this trick won't work.
我想补充一下这个答案，因为我自己最近也遇到了这个问题。到目前为止，最简单的解决方案（就我而言）是创建一个 mysql 视图在幕后进行了加入。这消除了 drupal 本身内跨数据库连接的需要。
I would just like to add to this answer, because I confronted this problem myself recently. The easiest solution by far and away (in my case) was to create a mysql view that did the join behind the scenes. That eliminates the need for the cross database join within drupal itself.
Drupal 不支持同时拥有多个活动数据库。主要原因可能是这是 mysql 特有的功能。
您可以使用 php 来完成此操作并跳过 Drupal 数据库层。由于它仅适用于应该运行一次的迁移脚本，因此可以将代码数据库指定为特定的。只是我们
mysql_ connect等。结束使用db_set_active可能是个好主意Drupal doesn't support having more than one active database at a time. Main reason is probably that this is a mysql specific feature.
You can do this with php and skip the Drupal database layer. Since it's only for a migration script that should be run once, it'll be fine to make the code database specific. Just us
mysql_ connectetc. Probably be a good idea to end usingdb_set_active除了在我之前提到的 settings.php 技巧中使用前缀之外，您还可以对传递给 db_query() 的查询字符串中的前缀进行硬编码（类似于您在问题中所做的操作）。
因此，在这种情况下，您将
在 d7 上进行测试。
当然，您在 db_set_active 参数中使用的用户必须有权访问这两个数据库。
In addition to using the prefix in settings.php trick noted before me, you can also hardcode the prefix in the query string that you pass to db_query() (similar to what you did in your question).
So in this case you'd have
Tested on d7.
Of course the user you're using in the db_set_active argument has to have access to both databases.