Search code examples
mysqlinformation-schema

How to get a default column value from MySQL 5.7 information_schema.columns with appropriate quoting?

I maintain Beekeeper Studio, an open source SQL GUI.

I have a problem with information_schema.columns in MySQL 5.7.

when running:

select * from information_schema.columns where table_name = ?`

It returns the COLUMN_DEFAULT, but it doesn't seem to differentiate between variables (eg CURRENT_TIMESTAMP), and a string 'foo'.

default string vs variable

Is there a place I can get the 'real' default value with appropriate quoting?

What I expect:

CURRENT_TIMESTAMP 'foo'

Solution

  • Unfortunately, there's no metadata in the INFORMATION_SCHEMA that can tell you this in MySQL 5.7. You just have to know that for a DATETIME or TIMESTAMP column, CURRENT_TIMESTAMP is a special case.

    It is treated as a special case in the code, so if you use SHOW CREATE TABLE, it outputs the literal string "CURRENT_TIMESTAMP" instead of a quoted datetime value.

    https://github.com/mysql/mysql-server/blob/5.7/sql/sql_show.cc#L1402-L1411