0

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?

fjybiocs
  • 59
  • 5

1 Answers1

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 |
+----+----------+--------------+------------+-----+------+
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3733831
  • 2,886
  • 9
  • 36
  • 68
  • As far as I know, age can be added as a virtual column in SQL Server, which makes it easy to directly select the value of age. Isn't there any way in MySQL to achieve a similar effect? – fjybiocs Apr 10 '22 at 07:42
  • I see this in the documentation which would mean CURDATE() isn't allowed in a generated column expression: "Generated column expressions must adhere to the following rules. An error occurs if an expression contains disallowed constructs. Literals, deterministic built-in functions, and operators are permitted. A function is deterministic if, given the same data in tables, multiple invocations produce the same result, independently of the connected user. Examples of functions that are nondeterministic and fail this definition: CONNECTION_ID(), CURRENT_USER(), NOW()." – grantwparks Jul 17 '22 at 04:28