We can provide default value for a column. If we don specify its value then it will automatically assign a default value.
We can specify default value on table creation.
Syntax for adding default value is
1 | column_name datatype DEFAULT value; |
for example
1 2 3 4 5 6 7 | create table product( id int(11) NOT NULL AUTO_INCREMENT, name varchar(50) NOT NULL, quantity int(10) DEFAULT 100, price decimal(6,2) DEFAULT 10.22, status varchar(50) DEFAULT 'AVAILABLE', add_date timestamp DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(id)); |
this will produce following output
1 2 3 4 5 6 7 8 9 10 11 12 | mysql> desc product; +----------+--------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+-------------------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(50) | NO | | NULL | | | quantity | int(10) | YES | | 100 | | | price | decimal(6,2) | YES | | 10.22 | | | status | varchar(50) | YES | | AVAILABLE | | | add_date | timestamp | NO | | CURRENT_TIMESTAMP | | +----------+--------------+------+-----+-------------------+----------------+ 6 rows in set (0.06 sec) |
while inserting any rowin table if we dont provide value for column that have default value that its default value will be inserted for example
1 | mysql> insert into product(name) values('Mobile'); |
We have inserted only name of product and value of quantity,price,status and add_date is assigned as default value.
1 2 3 4 5 6 7 | mysql> select * from product; +----+--------+----------+-------+-----------+---------------------+ | id | name | quantity | price | status | add_date | +----+--------+----------+-------+-----------+---------------------+ | 1 | Mobile | 100 | 10.22 | AVAILABLE | 2017-04-05 17:10:42 | +----+--------+----------+-------+-----------+---------------------+ 1 row in set (0.00 sec) |