拆分 MySQL 数据库

发布于 2024-11-27 05:52:51 字数 592 浏览 1 评论 0原文

我有一个关于拆分数据库的问题。这是有问题的查询：

SELECT COUNT(*) FROM monies.link_monies_new lcn '
         . 'INNER JOIN products.link l ON lcn.link_id = l.link_id '
         . 'INNER JOIN products.products_format af ON l.product_format_id = af.product_format_id '
         . 'INNER JOIN products.products_categories ac ON af.product_category_id = ac.product_category_id '
         . 'WHERE lcn.click_time BETWEEN FROM_UNIXTIME(1311721200) AND FROM_UNIXTIME(1311807600) '
         . 'AND ac.product_category_id = 1

问题是现在数据库资金和产品将位于不同的服务器中。解决办法是什么？我需要两次查询吗？我有点失落。

谢谢！

原文

I have a question about split databases. This is the query in question:

SELECT COUNT(*) FROM monies.link_monies_new lcn '
         . 'INNER JOIN products.link l ON lcn.link_id = l.link_id '
         . 'INNER JOIN products.products_format af ON l.product_format_id = af.product_format_id '
         . 'INNER JOIN products.products_categories ac ON af.product_category_id = ac.product_category_id '
         . 'WHERE lcn.click_time BETWEEN FROM_UNIXTIME(1311721200) AND FROM_UNIXTIME(1311807600) '
         . 'AND ac.product_category_id = 1

The problem is that now the databases monies and products will be in different servers. What is the solution? Do I need two queries? I am a bit lost.

Thanks!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

冧九 2024-12-04 05:52:51

http://dev.mysql.com/doc/refman/5.0 /en/federated-use.html

如果我正确理解了你的问题，你想要在两个数据库之间进行查询连接表，并且数据库将位于不同的服务器上。

假设您在“monies”服务器上运行查询，您将在monies 服务器上创建联合表，指向“products”服务器。这样，您的查询将继续工作，并且 MySQL 神奇地管理它们位于物理上独立的服务器上的事实。

性能调整可能会出现问题，但这是让查询保持现在状态的最简单方法。

例如，您可以在“monies”服务器上创建一个名为“products”的本地数据库；在产品中，您将创建一个“链接”表：

CREATE TABLE link (
    link_id     INT(20) NOT NULL AUTO_INCREMENT,
    ....<<other columns from the link table>>
    PRIMARY KEY  (link_id)
)
ENGINE=FEDERATED

CONNECTION='mysql://fed_user@products_server:9306/products/link';

然后您可以运行上面的查询而不进行任何更改。

http://dev.mysql.com/doc/refman/5.0/en/federated-use.html

If I've understood your question properly, you want to have a query joining tables between 2 databases, and the databases will be located on separate servers.

Assuming you run the query on the "monies" server, you would create federated tables on the monies server, pointing at the "products" server. That way, your queries continue to work, and MySQL magically manages the fact they're on physically separate servers.

Performance tuning could be problematic, but it's the simplest way to keep your queries working as they are now.

For instance, you could create a local database on the "monies" server called "products"; within products, you would create a "link" table:

CREATE TABLE link (
    link_id     INT(20) NOT NULL AUTO_INCREMENT,
    ....<<other columns from the link table>>
    PRIMARY KEY  (link_id)
)
ENGINE=FEDERATED

CONNECTION='mysql://fed_user@products_server:9306/products/link';

You could then run the query above without changing anything.

回复收藏 0 原文

~没有更多了~