Search code examples
mysqlalter

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

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)?

Solution

  • 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