How to create a Generated Columns 'Age', calculate 'age' from colomn 'birthday' in MySQL?

Question

Here is my sql:

create table student(
    id smallint primary key auto_increment,
    class_id smallint not null ,
    name varchar(10) not null ,
    birthday date not null ,
    sex bool not null ,
    age int as (to_days(now()-birthday))
);

output:

[2022-04-10 12:08:42] [HY000][3763] Expression of generated column 'age' contains a disallowed function: now.
[2022-04-10 12:08:42] [HY000][3763] Expression of generated column 'age' contains a disallowed function: now.

I searched for information and learned that functions with indeterminate return values like now() cannot be used in calculated column expressions. How should I implement the calculation of the birthday column?

Answer 1

Don't use age column in table creation. You can find age when it querying as shown in the below:

SELECT TIMESTAMPDIFF(YEAR, birthday ,CURDATE()) as age 
FROM student

You can also check this question and answers

UPDATE:

Yes, you can do it by using the MySQL generated column, So you have to recreate the student table as follows:

CREATE TABLE student 
(
    id smallint primary key auto_increment,
    class_id smallint not null ,
    name varchar(45) not null ,
    birthday date not null ,
    sex bool not null ,
    age int GENERATED ALWAYS AS (TIMESTAMPDIFF(YEAR, birthday ,CURDATE()))
);

To test the age column, you have to insert a row into the student table like this:

INSERT INTO student (class_id, name, birthday, sex) 
VALUES ('A001', 'Student Name', '1983-02-05', 1);

+----+----------+--------------+------------+-----+------+
| id | class_id | name         | birthday   | sex | age  |
+----+----------+--------------+------------+-----+------+
|  1 |        0 | Student Name | 1983-02-05 |   1 |   39 |
+----+----------+--------------+------------+-----+------+