问题
有没有一种方法可以自动计算相关表(一对多),如果我们添加或删除相关项目,计数将自动递增/递减。
所以,我显然可以只做一个 COUNT,但出于性能原因,它需要数百万条记录,并且需要多次查询。作为我的解决方案,而不是每次都计数,我实际上会创建一个计数器,在添加新的相关项目时添加 1,或者在删除或取消引用另一个项目时删除 1
为了做到这一点,我可能只创建另一个表作为计数器,并在不计数的情况下查询该表。
有没有更好的方法,最好是自动的?
例子
图式
create table object
(
object_id int auto_increment primary key,
name varchar(120) not null
);
create table item
(
item_id varchar(63) not null,
object_id int not null,
primary key (object_id, item_id)
);
insert into object (name) VALUES ("hello");
insert into item (item_id, object_id) VALUES
("item1", 1),
("item2", 1),
("item3", 1),
("item4", 1);
对象“hello”有 4 个项目:
select count(*) from item where object_id = 1;
-- ouput: 4
但是,我发现作为变通方法,我可以创建一个计数器(使用后端 Python),每次执行CRUD操作时,计数器都会更新。例如:
计数器模式
create table item_counter
(
counter bigint NOT NULL DEFAULT 0,
object_id int NOT NULL primary key
)
所以现在我们可以做这样的事情的ORM(同样,它将在 Python 中处理,但这并不重要,例如:
-- create object AND item_counter
insert into object (name) VALUES ("hello");
-- create in the same time a counter
insert into item_counter (object_id) VALUES ((SELECT object_id FROM object where name = "hello"));
-- create items
insert into item (item_id, object_id) VALUES ("item1", 2);
update item_counter set counter = counter + 1 where object_id = 2;
insert into item (item_id, object_id) VALUES ("item2", 2);
update item_counter set counter = counter + 1 where object_id = 2;
insert into item (item_id, object_id) VALUES ("item3", 2);
update item_counter set counter = counter + 1 where object_id = 2;
insert into item (item_id, object_id) VALUES ("item4", 2);
update item_counter set counter = counter + 1 where object_id = 2;
-- select the counter instead
select counter from item_counter where object_id = 2;
如果它是在 python 中,它看起来像
# pseudo ORM code
class ItemORM:
def save(self, item_id, object_id):
self.orm.save(item_id, object_id)
counter = self.orm.get_counter(object_id)
counter.add()
那么,有没有更好的方法,尤其是 MySQL 可以自动执行的操作?
更多上下文
如果你想知道为什么,假设我在一个大型代码库中工作,其中有许多遗留代码和外部客户端使用的 API 依赖项,目前没有反实现,但这只是我克服它的想法。因此,更改大部分代码风险很大,相反,调整 MYSQL 表可能是更好的解决方案。
使用触发器可以轻松解决该任务。
例如,如果 INSERT 是对
item
表的唯一操作,则只需要 AFTER INSERT 触发器:具有完整触发器集的演示。