Search code examples
mysqlalter-tablealter

Alter MySQL's SHOW COLUMS "Extra" value

Is there a way to change te value of the Extra column that is shown with the SHOW COLUMNS/DESCRIBE sentences?

The documentation about this column states the following:

Extra

Any additional information that is available about a given column. The value is nonempty in these cases:

  • auto_increment for columns that have the AUTO_INCREMENT attribute.

  • on update CURRENT_TIMESTAMP for TIMESTAMP or DATETIME columns that have the ON UPDATE CURRENT_TIMESTAMP attribute.

  • VIRTUAL GENERATED or VIRTUAL STORED for generated columns.

  • DEFAULT_GENERATED for columns that have an expression default value.

I have the next table columns information but I wish to remove the Extra value of the start_date column.

Is there a way to do this?

+--------------------+--------------------+------+-----+-------------------+-------------------+
|       Field        |        Type        | Null | Key |      Default      |       Extra       |
+--------------------+--------------------+------+-----+-------------------+-------------------+
| id_machine_product | "int(10) unsigned" | NO   | PRI | NULL              | auto_increment    |
| ...                | ...                | ...  | ... | ...               | ...               |
| start_date         | timestamp          | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+--------------------+--------------------+------+-----+-------------------+-------------------+

EDIT:

I have implemented a fingerprint validation method in PHP that diffs the DESCRIBE tables values, I have database versions in production that doesn't have that Extra value even though those columns have an expression default value, so currently, I wish to alter that value so I don't get errors from my implemented fingerprint validation method in my development environment.

The production databases are in Mysql < 8.0 so, as per Bill Karwin's answer, I'm having trouble with my MySQL development environment version that is 8.0

Solution

  • Topicstarters comment

    I have implemented a fingerprint validation method in PHP that diffs the DESCRIBE tables values, I have database versions in production that doesn't have that Extra value even though those columns have an expression default value, so currently, I wish to alter that value so I don't get errors from my implemented fingerprint validation method in my development environment.

    The more standard SQL method would be which also works in MySQL 8

    Query

    SELECT 
    information_schema.COLUMNS.COLUMN_NAME AS 'Field'
    , information_schema.COLUMNS.COLUMN_TYPE AS 'Type'
    , information_schema.COLUMNS.IS_NULLABLE AS 'Null'
    , information_schema.COLUMNS.COLUMN_KEY AS 'Key'
    , information_schema.COLUMNS.COLUMN_DEFAULT AS 'Default'
    , information_schema.COLUMNS.EXTRA AS 'Extra'
FROM 
    information_schema.TABLES
INNER JOIN
    information_schema.COLUMNS ON information_schema.TABLES.TABLE_NAME =  information_schema.COLUMNS.TABLE_NAME
WHERE
    information_schema.TABLES.TABLE_NAME = '<table>'

    This query should match the output of DESCRIBE Then you could use REPLACE() on information_schema.COLUMNS.EXTRA output to remove or edit the way you want.
    For example removing extra features like DEFAULT_GENERATED or VIRTUAL GENERATED (generated columns)