Mysql设置相对于其他值的值？ [重复]

Possible Duplicate:
Create a trigger that updates a column on one table when a column in another table is updated

I got a database table with these values

UpVotes | DownVotes | PercentVotes
8       | 2         | 80
10      | 0         | 100
5       | 5         | 50
560     | 34        | 94

is it possible to get the mysql database to work like this:

UpVotes | DownVotes | PercentVotes
8       | 2         | (UpVotes/(UpVotes + DownVotes)) x 100 = 80
10      | 0         | (UpVotes/(UpVotes + DownVotes)) x 100 = 100
5       | 5         | (UpVotes/(UpVotes + DownVotes)) x 100 = 50
560     | 34        | (UpVotes/(UpVotes + DownVotes)) x 100 = 94

automatically without having to update it via any script? Just like in excel when you can then change the UpVotes value or DownVotes value and the PercentVotes value automatically becomes the new correct percent of upvotes according to the total votes.

Sorry for my bad english but i hope you get my point. :)

You can use a trigger for that. Something like this:

delimiter |
CREATE TRIGGER calc_percentages BEFORE INSERT on your_table
FOR EACH ROW
BEGIN
    SET NEW.PercentVotes = NEW.UpVotes/(NEW.UpVotes + NEW.DownVotes);
END
|
delimiter ;

It will run on every insert and add the calculated value. If you want it to update after an update then just add another trigger for after update instead of before insert.

Since the percentage information is a calculated value you can calculate it in your selects on-the-fly and don't store it in your DB. But if you really need the performance gain then use a trigger to store it.

You would want to run something like this on the select, you may need to check the math though.

select UpVotes, DownVotes ((UpVotes / (UpVotes + DownVotes)) * 100) as PercentVotes from my_table;

Yes it is possible. You can use triggers. Just set trigger on update and insert and it will change value of column.

if you are using triggers then try something like this

delimiter |

CREATE TRIGGER insertPercentVotes AFTER INSERT ON YourTableName

// calculate percentage here
      // Execute Update query here  
 END;
|

delimiter //
CREATE TRIGGER ins_votepercent BEFORE INSERT ON votetable FOR EACH ROW 
  SET NEW.PercentVotes = (NEW.UpVotes/(NEW.UpVotes+NEW.DownVotes)) * 100  
  END;//

CREATE TRIGGER upd_votepercent BEFORE UPDATE ON votetable FOR EACH ROW 
  SET NEW.PercentVotes = (NEW.UpVotes/(NEW.UpVotes+NEW.DownVotes)) * 100 
  END;//

See MySQL Manual