Calculated MYSQL Column

Question

I currently have a table that has 4 columns - valueone, valuetwo, valuethree and additioncolumn. I would like to know how to make the additioncolumn store the addition of valueone+valuetwo+valuethree.

I am new to MySQL and tried some syntax I found online and still no difference.

CREATE TABLE `calculation` (
  `valueone` int(11) NOT NULL,
  `valuetwo` int(11) NOT NULL,
  `valuethree` int(11) NOT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `additioncolumnn` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

INSERT INTO `calculation` VALUES (10,10,10,1,0),(20,20,20,2,0);

The value 0 there should be for the first row 30 and 60 for the second row.

Answer 1

If you're using MySQL 5.7, you can use generated columns. See https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html

CREATE TABLE `calculation` (
  `valueone` int(11) NOT NULL,
  `valuetwo` int(11) NOT NULL,
  `valuethree` int(11) NOT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `additioncolumnn` int(11) AS (`valueone`+`valuetwo`+`valuethree`),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

If you're using an older version of MySQL before they introduced the feature of generated columns, you'll have to use triggers:

CREATE TRIGGER CalcOnInsert BEFORE INSERT ON calculation
FOR EACH ROW 
  SET NEW.additionalcolumn = NEW.valueone + NEW.valuetwo + NEW.valuethree;

CREATE TRIGGER CalcOnUpdate BEFORE UPDATE ON calculation
FOR EACH ROW 
  SET NEW.additionalcolumn = NEW.valueone + NEW.valuetwo + NEW.valuethree;

Answer 2

I would suggest doing this as a view:

create view v_calculation as
    select c.*, (valueone + valuetwo + valuethree) as additioncolumn
    from calculation c;

When you use the view, the value is calculated automatically. Hence, it always up-to-date.

If you actually store the value, you will need a trigger to keep it up-to-date in the event one of the underlying columns change. That is a hassle and additional overhead on inserts and updates.

As Bill points out, the most recent version of MySQL does support generated columns, so that is the best solution.