MySQL：在表中存储业务逻辑 [英] MySQL : Storing business logic in table

问题描述

我有一个名为test的表：

I have a table named test :

create table demo (name varchar(10), mark1 int, mark2 int);

我需要多次对每一行的mark1和mark2的总计。

I need the total of mark1 and mark2 for each row many times.

select name, (mark1 + mark2) as total from demo;

我被告知是无效的。我不能在表中添加新的总列。

Which I am told is not efficient. I am not allowed to add a new total column in the table.

我可以在Index中存储这样的业务逻辑吗？

Can I store such business logic in Index?

我创建了一个视图

CREATE VIEW view_total AS SELECT name, (mark1 + mark2) as 'total' from demo;

我用演示表填充：

DELIMITER $$
CREATE PROCEDURE InsertRand(IN NumRows INT)
    BEGIN
        DECLARE i INT;
        SET i = 1;
        START TRANSACTION;
        WHILE i <= NumRows DO
            INSERT INTO demo VALUES (i,i+1,i+2);
            SET i = i + 1;
        END WHILE;
        COMMIT;
    END$$
DELIMITER ;

CALL InsertRand(100000);

select * from view_total;

和

select * from demo;

相同，为10 ms。所以我没有获得任何好处。我试图在视图上创建索引：

is same, 10 ms. So I have not gained any benefit of view. I tried to create index over the view with :

create index demo_total_view on view_total (name, total);

其中失败，错误：

ERROR 1347 (HY000): 'test.view_total' is not BASE TABLE

任何有关如何防止合计列的冗余操作的指针？

Any pointer about how do I prevent the redundant action of totaling the columns?

推荐答案

在表格中存储您退出时可以计算的内容。例如，你想要年龄，你应该存储出生日期。

As a general rule never store in a table what you can calculate on exit from it. For instance, you want age, you should store date of birth. If you want the sum of two columns, you should store those two columns, nothing else.

维护数据库中的数据完整性，质量和一致性应该是你最重要的关注。如果有最轻微的机会，第三列，这是前两者的总和，可能是失去同步，那么它是不值得做。

Maintaining the data-integrity, -quality and -consistency in your database should be your paramount concern. If there is the slightest chance that a third column, which is the sum of the first two, could be out-of-sync then it is not worth doing.

由于您无法维护该列而不将计算嵌入到将数据插入到表中的所有代码中（开放给未来遗忘，更新可能会中断）或触发每次你插入一个触发器（大量额外的工作），你不应该这样做。

As you cannot maintain the column without embedding the calculation into all code that inserts data into the table (open to being forgotten in the future and updating may break it) or firing a trigger every time you insert something (lots of additional work) you should not do this.

你的情况是一个完美的用例。您需要以相同的方式一致地计算列。如果你让每个人都按照他们的意愿计算，那么会出现与插入计算列相同的问题，你需要保证这总是以相同的方式计算。这样做的方式是在您的表格上以标准方式预计算列，这对于每个用户都是一样的。

Your situation is a perfect use-case for views. You need to consistently calculate a column in the same way. If you let everyone calculate this as they wish then the same problems as with inserting the calculated column occur, you need to guarantee that this is always calculated the same way. The way to do this is to have a view on your table that pre-calculates the column in a standard way, that will be identical for every user.

计算一个数百的时间将比从某个地方读取更昂贵...对不对？

Calculating a sum hundreds of time would be much costlier then reading it from somewhere... right?

这完全取决于你自己的情况。如果你有较慢的磁盘，那么读取数据可能容易更昂贵，然后计算。特别是因为它是一个非常简单的计算。

Not necessarily, this depends entirely on your own situation. If you have slower disks then reading the data may easily be more expensive then calculating it. Especially since it's an extremely simple calculation.

很可能它没有什么区别，但如果它是一个主要的性能问题，你应该测试这两种情况，并决定是否潜在的数据质量损失和在表中保持计算的额外开销在从数据库中提取时值得奇怪的纳秒。

In all likelihood it will make no difference at all but if it is a major performance concern you should test both situations and decide whether the potential loss of data-quality and the additional overhead in maintaining the calculation in a table is worth the odd nano-second on extraction from the database.

这篇关于MySQL：在表中存储业务逻辑的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！