How to Find Generated Column Definition in MySQL

Question

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.

Answer 1

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.

Answer 2

Here is a more practical answer:

SELECT column_name, generation_expression
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'salaries'