CODEWITHSUNDEEP
mysqldefault-value
Urbycoz
Urbycoz

Reputation: 7411

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.

Upvotes: 0

Views: 1271

Answers (4)

Paul Spiegel
Paul Spiegel

Reputation: 31772

SHOW CREATE TABLE Users

Will show you more than just that.

You can also query the information_schema

select c.COLUMN_DEFAULT
from information_schema.`COLUMNS` c
where c.TABLE_SCHEMA = 'MyDatabaseName'
  and c.TABLE_NAME = 'Users'
  and c.COLUMN_NAME = 'Active';

Upvotes: 2

Kedar Limaye
Kedar Limaye

Reputation: 1041

Check INFORMATION_SCHEMA.COLUMNS table, column COLUMN_DEFAULT . More info - dev.mysql.com/doc/refman/5.1/en/columns-table.html

Upvotes: 0

Mittal Patel
Mittal Patel

Reputation: 2762

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

Upvotes: 1

Alvaro Niño
Alvaro Niño

Reputation: 567

Type SHOW CREATE TABLE Users and you'll see an output of creation of table with default value for columns.

Regards

Upvotes: 0

Related Questions