How to Add a Default Value to a Column in MySQL

We can provide a default value for a column. If we don specify its value, it will automatically assign a default value.

What is the Default value in MySql

n MySQL, the default value is a predefined value that is assigned to a column if no explicit value is provided during the insertion of a new row.

It is a way to ensure that a column always has a value, even if it is not specified explicitly.

When a column is defined with a default value, if a new row is inserted into the table and the value for that column is not provided, the default value will be automatically assigned to the column.

Different Ways to specify default values

1. The default value on table creation
2. The default value on alter table
3. IFNULL() function on Insert update Command
4. Triggers

1. The syntax for adding default value

CREATE TABLE table_name (
    column_name data_type DEFAULT default_value,
    ...
);

sql add column with default value in MySql

CREATE TABLE users (
    id INT,
    name VARCHAR(50),
    age INT DEFAULT 0
);

In the above example, if you insert a row into the “users” table without specifying a value for the “age” column, it will automatically be set to 0.

However, if you do provide a value for the “age” column during the insertion, that value will be used instead of the default.

2. MySql Example to add Default value

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 the following output

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 row in the table if we don’t provide a value for a column that has a default value that its default value will be inserted for example

mysql> insert into product(name) values('Mobile');

We have inserted only the product name and the value of quantity, price, status, and add_date as the default value.

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) 

Alter Table command to Set default value in MySql

ALTER TABLE table_name
    ALTER COLUMN column_name SET DEFAULT default_value;

ALTER TABLE customers
    ALTER COLUMN city SET DEFAULT 'Unknown';

alter table add column default value

ALTER TABLE users
ADD COLUMN email VARCHAR(100) DEFAULT '[email protected]';

3. IFNULL() function on Insert update Command

1. This method is useful when you want to add a default value to an already existing column.
2. IFNULL() Function: The IFNULL() function can be used within an INSERT or UPDATE statement to provide a default value if the column value is NULL. Here’s an example:

IFNULL() Function Syntax to Insert Default value

INSERT INTO table_name (column_name)
VALUES (IFNULL(column_value, default_value));

INSERT INTO table_name (column_name)
VALUES (COALESCE(column_value, default_value));

If the column_value is NULL, the default_value will be used.

4. Triggers

Triggers allow you to execute custom logic when certain events occur, such as before or after an INSERT or UPDATE operation.

You can use triggers to set a default value for a column dynamically. Here’s a simplified example:

CREATE TRIGGER trigger_name
BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
    IF NEW.column_name IS NULL THEN
        SET NEW.column_name = default_value;
    END IF;
END;

Read More

What Is Innodb_lock_wait_timeout ?