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:
Add a tenant_id column to every table
Use a trigger to populate the tenant_id with the current database username on insert
Create a view for each table where tenant_id = current_database_username
Only use the views in your application
Connect to the database using the tenant specific username
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 & development (stored procedure can also be shared) simple.
假设您在单个 MySQL 实例上运行一个 MySQL 数据库 - 有多种方法可以区分哪些内容属于谁。 最明显的选择（至少对我来说）是创建一个复合主键，例如：
CREATE TABLE some_table (
id int unsigned not null auto_increment,
companyId int unsigned not null,
..
..
..,
primary key(id, company_id)
) engine = innodb;
然后通过更改主键的 companyId 部分来区分公司。 这样，您就可以将所有公司的所有数据放在同一个表/数据库中，并且在应用程序级别，您可以控制哪个公司与哪个公司 ID 相关联，并确定为某个公司显示哪些数据。
如果这不是您想要的 - 对于误解您的问题，我深表歉意。
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:
CREATE TABLE some_table (
id int unsigned not null auto_increment,
companyId int unsigned not null,
..
..
..,
primary key(id, company_id)
) engine = innodb;
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.
如果您想确保 A 公司的某人无法看到属于 B 公司的数据，您可以按照 Matthew PK 答案在应用程序级别执行此操作，例如，
如果您想确保设法破坏安全性的人，并且不过，对数据库运行任意 SQL，您需要比这更强大的东西。
如果您希望能够独立备份数据，以便可以在星期一安全地备份 C 公司，在星期日安全地备份 A 公司，并且能够仅恢复 C 公司，那么，纯粹基于应用程序的解决方案将无济于事。
Have you considered creating a different schema 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.
Renaming every table to a different and unique name (e.g. using a prefix ‘someprefix_’)
Adding a text column called ‘id_tenant’ to every table to store the name of the tenant the row belongs to
Creating a trigger for each table to automatically store the current database username to the id_tenant column before inserting a new row
Creating a view for each table with the original table name with all the columns except id_tenant. The view will only return rows where (id_tenant = current_database_username)
Only grant permission to the views (not tables) to each tenant’s database user Then, the only part of the application that needs to change is the database connection logic. When someone connects to the SaaS, the application would need to:
Connect to the database as that tenant-specific username
发布评论
评论（7）
多租户数据库有多种方法。为了讨论，它们通常分为三类。
租户。
MSDN 有一篇关于 每种设计的优缺点，以及实现示例。
Microsoft has apparently taken down the pages I referred to, but they are on on archive.org. Links have been changed to point there.
作为参考，这是第二篇文章的原始链接
There are several approaches to multi-tenant databases. For discussion, they're usually broken into three categories.
tenant.
MSDN has a good article on the pros and cons of each design, and examples of implementations.
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 original link for the second article
在 MySQL 中，我更喜欢为所有租户使用单一数据库。我通过为每个租户使用单独的数据库用户来限制对数据的访问，该用户只能访问仅显示属于该租户的行的视图。
这可以通过以下方式完成：
我已在博客文章中完整记录了这一点：
https://opensource.io/it/mysql-multi-tenant/
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:
I've fully documented this in a blog post:
https://opensource.io/it/mysql-multi-tenant/
简单的方法是：为每个共享表添加一列SEGMENT_ID。为每个客户分配正确的 SEGMENT_ID。然后根据 SEGMENT_ID 为每个客户群创建视图，这些视图将使数据与每个客户分开。通过这种方法，可以共享信息，使操作和维护都变得简单。开发（存储过程也可以共享）简单。
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 & development (stored procedure can also be shared) simple.
假设您在单个 MySQL 实例上运行一个 MySQL 数据库 - 有多种方法可以区分哪些内容属于谁。
最明显的选择（至少对我来说）是创建一个复合主键，例如：
然后通过更改主键的 companyId 部分来区分公司。
这样，您就可以将所有公司的所有数据放在同一个表/数据库中，并且在应用程序级别，您可以控制哪个公司与哪个公司 ID 相关联，并确定为某个公司显示哪些数据。
如果这不是您想要的 - 对于误解您的问题，我深表歉意。
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:
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.
您是否考虑过为每个公司创建不同的架构？
不过，您应该尝试更准确地定义您想要实现的目标。
例如，如果您想确保硬件故障不会损害多个公司的数据，则必须创建不同的实例并在不同的节点上运行它们。
如果您想确保 A 公司的某人无法看到属于 B 公司的数据，您可以按照 Matthew PK 答案在应用程序级别执行此操作，例如，
如果您想确保设法破坏安全性的人，并且不过，对数据库运行任意 SQL，您需要比这更强大的东西。
如果您希望能够独立备份数据，以便可以在星期一安全地备份 C 公司，在星期日安全地备份 A 公司，并且能够仅恢复 C 公司，那么，纯粹基于应用程序的解决方案将无济于事。
Have you considered creating a different schema 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.
给定一个特定的数据库用户，您可以将用户成员身份授予组，以指示允许他们访问其数据的公司。
我假设您将有一个
Companies表，因此只需在Companies和MySQLUsers之间创建一对多关系或类似的关系。然后，作为所有查询的条件，只需根据
UserID匹配CompanyIDGiven 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
Companiestable, so just create a one-to-many relationship betweenCompaniesandMySQLUsersor something similar.Then, as a condition of all your queries, just match the
CompanyIDbased on theUserID在我的文件Generate_multiTanentMysql.php中，我使用PHP脚本
https://github.com/ziedtuihri/SaaS_Application
解决方案设计模式：
为每个租户创建数据库用户
将每个表重命名为不同且唯一的名称（例如使用前缀“someprefix_”）
向每个表添加一个名为“id_tenant”的文本列以存储该行所属租户的名称
为每个表创建一个触发器，以便在插入新行之前自动将当前数据库用户名存储到 id_tenant 列
使用原始表名以及除 id_tenant 之外的所有列为每个表创建一个视图。该视图将仅返回 (id_tenant = current_database_username) 的行
仅向每个租户的数据库用户授予对视图（而不是表）的权限
然后，应用程序中唯一需要更改的部分是数据库连接逻辑。当有人连接到 SaaS 时，应用程序需要：
以特定于租户的用户名连接到数据库
in my file Generate_multiTanentMysql.php i do all steps with PHP script
https://github.com/ziedtuihri/SaaS_Application
A Solution Design Pattern :
Creating a database user for each tenant
Renaming every table to a different and unique name (e.g. using a prefix ‘someprefix_’)
Adding a text column called ‘id_tenant’ to every table to store the name of the tenant the row belongs to
Creating a trigger for each table to automatically store the current database username to the id_tenant column before inserting a new row
Creating a view for each table with the original table name with all the columns except id_tenant. The view will only return rows where (id_tenant = current_database_username)
Only grant permission to the views (not tables) to each tenant’s database user
Then, the only part of the application that needs to change is the database connection logic. When someone connects to the SaaS, the application would need to:
Connect to the database as that tenant-specific username