mysql业务逻辑,MySQL：在表中存储业务逻辑

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.