Does MySQL have such a function as for example excel: set a single column with a formula so that each cell in this column = the sum of other cells in a row? Moreover, when the values ​​in the row change, the amount changes automatically. Or do you need to do all this manually?

    2 answers 2

    It is not customary to store redundant data in the database. And a column that always has a certain value, depending on other columns in the same table, is obvious redundancy. In order to not have to write the required calculations each time and at the same time not to store redundant data in the database, the VIEW was invented.

    For example, create a view "add" column with the sum of columns A and B:

    create or replace view MyView as select T.*, A + B as AB from TableX T ;

    After this, we can use this view instead of a table to retrieve data when retrieving data:

     select * from MyView where AB > 10;

    As a result of the query, we will receive data from the TableX table in which the sum of columns A and B is greater than 10. The output will also contain the column 'AB' containing the sum.

      Yes, you can solve this problem through a trigger. For example, the path has a table tbl with three columns one , two , other . You can create two triggers to insert and update data, for example, by placing the sum of two other columns in the other column (you can not assign a value to the other column)

       DELIMITER // CREATE TRIGGER addTbl BEFORE INSERT ON tbl FOR EACH ROW BEGIN SET NEW.other = NEW.one + NEW.two; END// CREATE TRIGGER updateTbl BEFORE UPDATE ON tbl FOR EACH ROW BEGIN SET NEW.other = NEW.one + NEW.two; END//

      Since we use the semicolon delimiter as part of the trigger, the above example uses the DELIMITER command to change the indication of the end of the query to // (this is either not required in GUI environments or a separate field is used for the separator).