mysql - 共享租户对象
问题描述
我有一个带有单个数据库的多租户应用程序。我有一个存储所有对象的“实体”表。“sahred_entity”表用于存储租户 X 与租户 Y 共享的对象。例如,“租户 2”可以将“ID 为 4 的实体”共享给“租户 1”。
在下面的示例中,“ID 为 4 的实体”共享给“租户 1”和“租户 3”
+--------+--------------------------------------------------
| Table | Create Table
+--------+--------------------------------------------------
| entity | CREATE TABLE `entity` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`tenant_id` int(10) unsigned NOT NULL,
`added_at` timestamp NOT NULL,
`color` varchar(20) NOT NULL,
`size` varchar(5) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 |
+--------+--------------------------------------------------
+---------------+---------------------------------------
| Table | Create Table
+---------------+---------------------------------------
| shared_entity | CREATE TABLE `shared_entity` (
`tenant_to` int(10) unsigned NOT NULL,
`tenant_from` int(10) unsigned NOT NULL,
`entity_id` int(10) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------------+---------------------------------------
样本数据为
select * from entity;
+----+-----------+---------------------+--------+------+
| id | tenant_id | added_at | color | size |
+----+-----------+---------------------+--------+------+
| 1 | 1 | 2019-03-07 00:00:00 | red | m |
| 2 | 1 | 2019-03-07 00:00:00 | green | xl |
| 3 | 2 | 2019-03-07 00:00:00 | green | xl |
| 4 | 2 | 2019-03-07 00:00:00 | red | m |
| 5 | 3 | 2019-03-07 00:00:00 | yellow | l |
+----+-----------+---------------------+--------+------+
select * from shared_entity;
+-----------+-------------+-----------+
| tenant_to | tenant_from | entity_id |
+-----------+-------------+-----------+
| 1 | 2 | 4 |
| 3 | 2 | 4 |
+-----------+-------------+-----------+
现在我需要创建一个简单的搜索查询。现在我找到了两种方法来做到这一点。第一种是通过自我加入
SELECT e.* FROM `entity` as e
LEFT JOIN entity as e1 ON (e.id = e1.id AND e1.tenant_id = 1)
LEFT JOIN entity as e2 ON (e.id = e2.id AND e2.id IN (4))
WHERE (e1.id IS NOT NULL OR e2.id IS NOT NULL) AND e.`color` = 'red';
第二种是通过子查询和联合
SELECT * FROM
(
SELECT * FROM entity as e1 WHERE e1.tenant_id = 1
UNION
SELECT * FROM entity as e2 WHERE e2.id IN(4)
) as entity
WHERE color = 'red';
两个查询都返回预期结果
+----+-----------+---------------------+-------+------+
| id | tenant_id | added_at | color | size |
+----+-----------+---------------------+-------+------+
| 1 | 1 | 2019-03-07 00:00:00 | red | m |
| 4 | 2 | 2019-03-07 00:00:00 | red | m |
+----+-----------+---------------------+-------+------+
但是哪种方法更适合大表?如何创建正确的索引?或者也许有更好的解决方案?
解决方案
您还可以使用以下查询来获得相同的结果
SELECT *
FROM entity
WHERE (tenant_id = 1 or id = 4) AND color = 'red'
我不清楚为什么你需要所有的连接
推荐阅读
- laravel - (Laravel) 如何从 API 控制器中提取数据,以便在 vue-chartjs 中显示
- python - 为什么输入名称循环保持无限且不让其余代码运行?
- python - 如何将图像列表转换为python中的数字列表?TypeError: int() 参数必须是字符串、类似字节的对象或数字,而不是“图像”
- php - laravel 间歇性检测不到 mimes txt
- driver - 为 riffa 创建两个单独的设备文件流
- reactjs - 将选择框值重置为默认 onChange Reactjs
- python - flask-mail 和 twilio 具有相同的函数名称,称为 Message()。如何解决名称冲突
- debugging - Vim:如何开始和停止记录正在发生的一切?
- python - ZODB 忽略目标缓存对象计数和目标缓存内存大小
- sql - 时间戳数据类型上的 Oracle max 函数