mysqldefault-value

How can I get the default value of a column for an empty table in MySQL?

I've got a table with two columns.

+-------------+---------------+
|    User     |    Active     |
+-------------+---------------+
|    Geoff    |       1       |
|    Bob      |       1       |
+-------------+---------------+

The Active column is a tinyint with a default value of 0. However sometimes the default value gets set to 1, so that all future users are automatically set as active.

I can set the default value using this query:

ALTER TABLE `Users` MODIFY COLUMN `Active` tinyint(1) NOT NULL DEFAULT '1';

I now need to write a query to detect what the default value of the column is.

Is this possible?

I would like the query to only show the default value, since I am going to use it in some code.

Solution 
  • SHOW COLUMNS FROM dbname.tablename;

    Output

    Field       Type            Null        Key     Default     Extra
col1        int(10)         NO          PRI                 auto_increment      
col2        smallint(10)    YES                 0

    Here, in the default column show if any default value is set or not