CODEWITHSUNDEEP
mysqlmariadbinnodbinformation-schema
isapir
isapir

Reputation: 23483

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.

Upvotes: 4

Views: 1933

Answers (2)

caram
caram

Reputation: 1709

Here is a more practical answer:

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

Upvotes: 3

Barmar
Barmar

Reputation: 780798

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.

Upvotes: 7

Related Questions