Calculated field using MariaDB-CodePudding

I'm trying to calculate a person's age using MariaDB, the table is already created and I'm trying to alter the table to add the calculated column, this is my syntax:

ALTER TABLE user_details 
ADD Age AS TIMESTAMPDIFF(YEAR, dateOfBirth, CURDATE());

I've got no idea why I get a syntax error.

thanks for your help.

CodePudding user response：

MariaDB requires that the data type of the generated column (in your case presumably an INT) is specified, and that the expression be enclosed in parentheses. You need to use:

ALTER TABLE user_details 
ADD Age INT AS (TIMESTAMPDIFF(YEAR, dateOfBirth, CURDATE()))

Unlike MySQL, MariaDB (as of 10.2.1) supports non-deterministic built-in functions (in your case CURDATE) in generated columns, as long as the column is VIRTUAL (the default) and not indexed. See the manual.

Demo on dbfiddle

CodePudding user response：

There are 3 issues in your query:

The expression of generated column must be enclosed with parenthesis.
The column datatype must be specified - even when it is generated column.
The expression of generated column cannot use non-deterministic function CURDATE().

The latter problem cannot be fixed.

I'm trying to calculate a person's age using MariaDB

Do this in a query when you need in the age value really. Or create according view.

CodePudding user response：

You can try the method below. Then add an INSERT trigger for new rows.

ALTER TABLE user_details
ADD Age INT;

SET @@sql_safe_updates = 0;

UPDATE user_details
  SET Age = TIMESTAMPDIFF(YEAR, dateOfBirth, CURDATE());

SET @@sql_safe_updates = 1;