1

I have two counters in a mysql table. I would like to have another column that contains the difference between counter column 1 and counter column 2. Could I use a trigger after counter column 2 is updated which will subtract the number in counter 2 from the number in counter 1 and put the results in count column 3? I would want to index count column 3 for queries.

6
2

You can use a generated column in your CREATE TABLE statement to always have an up-to-date value:

CREATE TABLE TestTable(
    id INT AUTO_INCREMENT PRIMARY KEY,
    col1 INT NOT NULL,
    col2 INT NOT NULL,
    col3 INT AS (col1 - col2)
);

The column can be virtual or stored (virtual is default) and each has their own use cases as detailed in the link above.

Note: only applicable for MySQL 5.7.6 & higher.

1
  • This seems to do exactly what I need without triggers at all. – Ben Giles Jun 9 '16 at 20:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.