blocks|key|139210|text|多租户数据库有几种方法。对于讨论，它们通常分为三类。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|139211|139212|每个租户一个数据库。|unordered-list-item|139213|共享数据库，每个租户一个架构。|139214|共享数据库，共享架构。租户标识符(租户密钥)将每行与正确的租户相关联。|139215|139216|MSDN有一篇关于pros+and+cons+of+each+design和examples+of+implementations的好文章。|offset|length|139217|​|139218|139219|微软显然已经删除了我提到的页面，但它们在archive.org上。链接已更改为指向那里。|139220|139221|作为参考，这是original+link+for+the+second+article|139222|entityMap|0|LINK|mutability|MUTABLE|url|https://web.archive.org/web/20110311185417/http://msdn.microsoft.com/en-us/library/aa479086.aspx|1|https://web.archive.org/web/20071012043908/http://msdn2.microsoft.com/en-us/library/aa479086.aspx|2|https://msdn.microsoft.com/en-us/library/aa479086.aspx#mlttntda_topic4^0|0|0|0|0|0|0|9|S|0|12|R|1|0|0|0|0|0|7|10|2|0^^$0|@$1|2|3|4|5|6|7|18|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|19|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|1A|8|@]|9|@]|A|$]]|$1|F|3|G|5|E|7|1B|8|@]|9|@]|A|$]]|$1|H|3|I|5|E|7|1C|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|1D|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|1E|8|@]|9|@$M|1F|N|1G|1|1H]|$M|1I|N|1J|1|1K]]|A|$]]|$1|O|3|P|5|6|7|1L|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|1M|8|@]|9|@]|A|$]]|$1|R|3|S|5|6|7|1N|8|@]|9|@]|A|$]]|$1|T|3|P|5|6|7|1O|8|@]|9|@]|A|$]]|$1|U|3|V|5|6|7|1P|8|@]|9|@$M|1Q|N|1R|1|1S]]|A|$]]|$1|W|3|-4|5|6|7|1T|8|@]|9|@]|A|$]]]|X|$Y|$5|Z|10|11|A|$12|13]]|14|$5|Z|10|11|A|$12|15]]|16|$5|Z|10|11|A|$12|17]]]]

There are several approaches to multi-tenant databases. For discussion, they're usually broken into three categories.

<ul>
<li>One database per tenant.</li>
<li>Shared database, one schema per
tenant.</li>
<li>Shared database, shared schema. A tenant identifier (tenant key) associates every row with the right tenant.</li>
</ul>

MSDN has a good article on the <a href="https://web.archive.org/web/20110311185417/http://msdn.microsoft.com/en-us/library/aa479086.aspx" rel="noreferrer">pros and cons of each design</a>, and <a href="https://web.archive.org/web/20071012043908/http://msdn2.microsoft.com/en-us/library/aa479086.aspx" rel="noreferrer">examples of implementations</a>.

<hr>
Microsoft has apparently taken down the pages I referred to, but they are on on archive.org. Links have been changed to point there. 

For reference, this is the <a href="https://msdn.microsoft.com/en-us/library/aa479086.aspx#mlttntda_topic4" rel="noreferrer">original link for the second article</a>

blocks|key|2229158|text|在MySQL中，我更喜欢为所有租户使用一个数据库。我通过对每个租户使用单独的数据库用户来限制对数据的访问，每个租户只能访问只显示属于该租户的行的视图。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2229159|这可以通过以下方式完成：|2229160|2229161|将tenant_id列添加到每个表中|ordered-list-item|2229162|使用触发器在插入时使用当前数据库用户名填充tenant_id+|2229163|为tenant_id+=+current_database_username|2229164|Only的每个表创建视图使用特定于租户的用户名|2229165|将application|2229166|Connect中的视图用于数据库|unordered-list-item|2229167|2229168|我已经在一篇博客文章中完整地记录了这一点：https://opensource.io/it/mysql-multi-tenant/|offset|length|2229169|entityMap|0|LINK|mutability|MUTABLE|url|https://opensource.io/it/mysql-multi-tenant/^0|0|0|0|0|0|0|0|0|0|0|L|18|0|0^^$0|@$1|2|3|4|5|6|7|15|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|16|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|17|8|@]|9|@]|A|$]]|$1|E|3|F|5|G|7|18|8|@]|9|@]|A|$]]|$1|H|3|I|5|G|7|19|8|@]|9|@]|A|$]]|$1|J|3|K|5|G|7|1A|8|@]|9|@]|A|$]]|$1|L|3|M|5|G|7|1B|8|@]|9|@]|A|$]]|$1|N|3|O|5|6|7|1C|8|@]|9|@]|A|$]]|$1|P|3|Q|5|R|7|1D|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|1E|8|@]|9|@]|A|$]]|$1|T|3|U|5|6|7|1F|8|@]|9|@$V|1G|W|1H|1|1I]]|A|$]]|$1|X|3|-4|5|6|7|1J|8|@]|9|@]|A|$]]]|Y|$Z|$5|10|11|12|A|$13|14]]]]

In MySQL I prefer to use a single database for all tenants. I restrict access to the data by using a separate database user for each tenant that only has access to views that only show rows that belong to that tenant.

This can be done by:

<ol>
<li>Add a tenant_id column to every table</li>
<li>Use a trigger to populate the tenant_id with the current database username on insert</li>
<li>Create a view for each table where tenant_id = current_database_username</li>
<li>Only use the views in your application</li>
<li>Connect to the database using the tenant specific username</li>
</ol>

I've fully documented this in a blog post:
<a href="https://opensource.io/it/mysql-multi-tenant/" rel="noreferrer">https://opensource.io/it/mysql-multi-tenant/</a>

blocks|key|2229141|text|简单的方法是:对于每个共享表，添加一个列SEGMENT_ID。为每个客户分配适当的SEGMENT_ID。然后在SEGMENT_ID上为每个客户群创建视图，这些视图将保持与每个客户的数据分离。通过这种方法，可以共享信息，使操作和开发(存储过程也可以共享)变得简单。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2229142|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

The simple way is: for each shared table, add a column says SEGMENT_ID. Assigned proper SEGMENT_ID to each customer. Then create views for each customer base on the SEGMENT_ID, These views will keep data separated from each customers. With this method, information can be shared, make it simple for both operation &amp; development (stored procedure can also be shared) simple.

blocks|key|2229014|text|假设您在单个MySQL实例上运行一个MySQL数据库-有几种方法可以区分哪些数据库属于谁。最明显的选择(至少对我而言)是创建一个复合主键，例如：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2229015|CREATE+TABLE+some_table+(
id+int+unsigned+not+null+auto_increment,
companyId+int+unsigned+not+null,
..
..
..,
primary+key(id,+company_id)
)+engine+=+innodb;|code-block|syntax|javascript|2229016|然后通过改变主键的companyId部分来区分公司。这样，您就可以将所有公司的所有数据放在同一个表/数据库中，并且在应用程序级别上，您可以控制哪个公司与哪个companyId关联，并决定为某个公司显示哪些数据。|2229017|如果这不是你要找的--我为误解你的问题道歉。|2229018|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Assuming you'd run one MySQL database on a single MySQL instance - there are several ways how to distinguish between what's belonging to whom.
Most obvious choice (for me at least) would be creating a composite primary key such as:

<pre><code>CREATE TABLE some_table (
id int unsigned not null auto_increment,
companyId int unsigned not null,
..
..
..,
primary key(id, company_id)
) engine = innodb;
</code></pre>

and then distinguishing between companies by changing the companyId part of the primary key.
That way you can have all the data of all the companies in the same table / database and at application level you can control what company is tied to which companyId and determine which data to display for certain company.

If this wasn't what you were looking for - my apologies for misunderstanding your question.

blocks|key|139170|text|您是否考虑过为每家公司创建不同的schema？|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|139171|不过，你应该试着更准确地定义你想要实现的目标。|139172|例如，如果您希望确保硬件故障不会危及多个公司的数据，则必须创建不同的实例并在不同的节点上运行它们。|139173|如果您希望确保公司A的某人不能看到属于公司B的数据，则可以按照Matthew+PK答案在应用程序级别执行此操作，例如|139174|但是，如果您希望确保有人设法破坏安全性并针对DB运行任意SQL，那么您需要更健壮的东西。|139175|如果您希望能够独立备份数据，以便可以在周一安全地备份公司C，在周日安全地备份公司A，并且能够仅恢复公司C，那么，再说一次，纯粹基于应用程序的解决方案不会有任何帮助。|139176|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.0/en/create-database.html^0|G|6|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|V|8|@]|9|@$A|W|B|X|1|Y]]|C|$]]|$1|D|3|E|5|6|7|Z|8|@]|9|@]|C|$]]|$1|F|3|G|5|6|7|10|8|@]|9|@]|C|$]]|$1|H|3|I|5|6|7|11|8|@]|9|@]|C|$]]|$1|J|3|K|5|6|7|12|8|@]|9|@]|C|$]]|$1|L|3|M|5|6|7|13|8|@]|9|@]|C|$]]|$1|N|3|-4|5|6|7|14|8|@]|9|@]|C|$]]]|O|$P|$5|Q|R|S|C|$T|U]]]]

Have you considered creating a different <a href="http://dev.mysql.com/doc/refman/5.0/en/create-database.html" rel="nofollow">schema</a> for each company? 

You should try to define more precisely what you want to achieve, though.

If you want to make sure that an HW failure doesn't compromise data for more than one company, for example, you have to create different instances and run them on different nodes.

If you want to make sure that someone from company A cannot see data that belong to company B you can do that at the application level as per Matthew PK answer, for example

If you want to be sure that someone who manages to compromise the security and run arbitrary SQL against the DB you need something more robust than that, though.

If you want to be able to backup data independently so that you can safely backup Company C on mondays and Company A on sundays and be able to restore just company C then, again, a purely application-based solution won't help.

blocks|key|139136|text|给定一个特定的DB用户，您可以将一个用户成员资格分配给一个(或多个)组，以指示他们有权访问其数据的公司。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|139137|我假设您将拥有一个Companies表，因此只需在Companies和MySQLUsers或类似的表之间创建一对多关系即可。|offset|length|style|CODE|139138|然后，作为所有查询的条件，只需匹配基于UserID的CompanyID|139139|entityMap^0|0|9|9|P|9|Z|A|0|J|6|Q|9|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@$D|N|E|O|F|G]|$D|P|E|Q|F|G]|$D|R|E|S|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|T|8|@$D|U|E|V|F|G]|$D|W|E|X|F|G]]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|K|$]]

Given a specific DB User, you could give a user membership to group(s) indicating the companies whose data they are permitted to access.

I presume you're going to have a <code>Companies</code> table, so just create a one-to-many relationship between <code>Companies</code> and <code>MySQLUsers</code> or something similar.

Then, as a condition of all your queries, just match the <code>CompanyID</code> based on the <code>UserID</code>

Let us say I need to design a database which will host data for multiple companies. Now for security and admin purposes I need to make sure that the data for different companies is properly isolated but I also do not want to start 10 mysql processes for hosting the data for 10 companies on 10 different servers. What are the best ways to do this with the mysql database.

How to design a multi tenant mysql database

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

EdgeOne AI 安全实战专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云AI代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

功能1上新10个字符

功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符。

功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符。

功能5描述100个字符功能5描述100个字符功能5描述100个字符功能5描述100个字符功能5描述100个字符功能5描述100个字符

功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符

功能4上新

文章&问答评论现已支持表情

全新交互，全新视觉，新增快捷键、悬浮工具栏、高亮块等功能并同时优化现有功能，全面提升创作效率和体验

社区富文本编辑器全新改版！诚邀体验～ 

精选全网热门MCP server，让你的AI更好用 🚀

💥开发者 MCP广场重磅上线！

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

比方说，我需要设计一个数据库，它将托管多个公司的数据。现在，出于安全和管理的目的，我需要确保不同公司的数据被适当隔离，但我也不想启动10个mysql进程来托管10个不同服务器上的10个公司的数据。用mysql数据库做这件事的最佳方法是什么？

问如何设计多租户mysql数据库
EN

回答 6

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何设计多租户mysql数据库EN

回答 6

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何设计多租户mysql数据库
EN