Mysql - How to create column that automatically sums rows?

I have a mysql database table called "character" with columns like name, strength, intelligence, skill. I want to create a column that sums up strength, intelligence and skill AUTOMATICALLY. Is this possible?

I have seen loads of pages showing how to do queries. I did a query just fine, such as select (str+intel+skl) as sum from character; It returns a sum just fine.

But what I'm missing (don't understand) is how to:

1. eithe开发者_StackOverflow社区r AUTOMATE that query into my mysql db (for example, when I do "select * from character", it will show "strength, intelligence, skill, sum"),
2. OR where/how to incorporate the mysql query into my rails app so that SUM shows up in real time, and when edits to, for e.g. strength occur, the SUM is updated accordingly.

A key point, I am summing columns across a row (strength, intelligence, skill), not summing a column (strengths of several characters).

---

Your best option is to use a trigger (or the similar code at the app level).

Using a view works too, but if you need it frequently you'll want it pre-calculated for performance reasons.

For an introduction to triggers, see:

http://dev.mysql.com/doc/refman/5.6/en/triggers.html

You probably want something like this:

CREATE TRIGGER character_sum_ins BEFORE INSERT ON character
FOR EACH ROW SET NEW.sum = NEW.strength + NEW.intelligence + NEW.skill;

CREATE TRIGGER character_sum_upd BEFORE UPDATE ON character
FOR EACH ROW SET NEW.sum = NEW.strength + NEW.intelligence + NEW.skill;

Alternatively, pre-calculate the sum in your app and store it accordingly.

Side note: ask yourself if you really need this stored though. As point out by another commenter, there might be no need for the auto-calculated data at all.

---

This should work, if I'm understanding your question correctly and the strength, intelligence, skill columns are numeric data types.

select strength, intelligence, skill, strength+intelligence+skill as sum
from character

As suggested, a view could then be created pretty easily with:

create view totals as
select strength, intelligence, skill, strength+intelligence+skill as sum
from character

---

You would want to make this into a stored procedure. The procedure could do the calculations, and you could read the results as though they were columns.

You could also probably get away with a simple view for this use case.

---

Don't do this in mysql, just add the values up when displaying them, seriously.

Or if you really want, sum them in mysql in your SELECT statement when returning them.

Don't use a trigger just to maintain a total of some numbers - they are a seriously screwy way of confusing all future develoeprs on the project.