Search code examples
mysqlmariadbinnodbinformation-schema

How to Find Generated Column Definition in MySQL

I have added a virtual generated column to the salaries table in the test employees database as follows:

ALTER TABLE salaries
    ADD COLUMN salary_k int AS (salary / 1000);

Now when I query INFORMATION_SCHEMA.COLUMNS the EXTRA column shows VIRTUAL GENERATED as expected, but how can I get the details of the generated column, i.e. in this case (salary / 1000)? COLUMN_DEFAULT shows NULL.

SHOW CREATE TABLE salaries shows the details in the result, but I want the result as part of a larger query of the INFORMATION_SCHEMA, so that doesn't work for me.

Solution

  • It's in the GENERATION_EXPRESSION column of INFORMATION_SCHEMA.COLUMNS.

    From the documentation:

    GENERATION_EXPRESSION
    For generated columns, displays the expression used to compute column values.