MySQL：在表中存储业务逻辑(MySQL : Storing business logic in table)

MySQL：在表中存储业务逻辑(MySQL : Storing business logic in table)

我有一个名为test的表：

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

我需要多次为每一行提供mark1和mark2的总和。

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

据我所知，效率不高。 我不允许在表格中添加新的总列。

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

我创建了一个视图

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毫秒。 所以我没有从视角中获益。 我尝试在视图上创建索引：

create index demo_total_view on view_total (name, total);

失败的错误：

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

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

I have a table named test :

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

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.

Can I store such business logic in Index?

I created a view

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

I populated the demo table with:

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);

The execution time of

select * from view_total;

and

select * from demo;

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);

which failed with error :

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.