在数据库（SQL）和编程领域执行操作和逻辑的好处（PHP，C＃...）

Question

What are the benefits of performing some functions or operations in the database compared to doing them in the some other programming language?

For example, let's assume there is a mysql database school with table students. Then the students table has the following attributes (id, name, age, dob...): will it be better to do like the age calculation in the database by subtracting the dob from the current date and then storing it in age column for all students? Or is it better to calculate it in a programming language and then store the age result into the database?

The goal here is to let each student's age update automatically each year. Also to make sure the application performance is top notch(which includes speed).

So which method is better?

Answer 1

The best idea is to store only the DATE_OF_BIRTH in the database. When the application needs to know a student's age it can calculate it using the current date.

As for whether it's better to do the calculations in the database or the application, usually you should so in SQL what can be done in SQL. Given that we have to query the data anyway, no application code is going to be speedier than

select student_id
        , date_of birth
        , floor(months_between(sysdate, date_of_birth)/12) as age_in_years
from students

This solution uses Oracle RDBMS functions; the precise syntax will be different for SQL Server or MySQL.

---

"am looking to make sure the application performs well and fast"

The Hadoopers often talk about bringing the computation to the data. Well that's not a new insight, that's what SQL does, and has been doing for decades.