How can I have a column in a MySQL database be a function of other columns?

Question

I'm working with a database that keeps track of where a reader is in a book. I do this by having a page count column and a current page column.

Is there a way to add a 'progress' column that would be equal to (currentpage/pagecount)?

Answer 1

You have two options. You can either create that field on SELECT:

SELECT book_id, user_id, (currentpage/pagecount) AS progress FROM user_books;

Or create a VIEW, which will allow you to query it without explicitly stating the operation:

CREATE VIEW user_books_progress AS
  (SELECT book_id, user_id, (currentpage/pagecount) AS progress FROM user_books);

Then you can just query your view as a normal table:

SELECT book_id, user_id, progress FROM user_books_progress;

More information about Arithmetic Operators and Views are available in the documentation:

11.5.1: Arithmetic Operators
12.1.12. CREATE VIEW Syntax

Answer 2

You cannot define a column to be a function. There are at least two workarounds to this:

• Update your progress column using a BEFORE UPDATE trigger, you'll need one for BEFORE INSERT as well.
• Use a view that calculates the progress column for you.