Insert default into not null column if value is null in MySQL?

You can use IFNULL() property or simple IF() with IS NULL property. The syntax is as follows −

INSERT INTO yourTableName(yourColumnName1,yourColumnName2)
VALUES('yourValue’',IF(yourColumnName1 IS NULL,DEFAULT(yourColumnName2),'yourMessage'));

To understand the above syntax, let us create a table. The query to create a table is as follows −

mysql> create table Post
   -> (
   -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,  
   -> UserName varchar(10),
   -> UserPostMessage varchar(50) NOT NULL DEFAULT 'Hi Good Morning !!!'
   -> );
Query OK, 0 rows affected (0.67 sec)

Now you can insert default into the not null column if the value is null. The query is as follows −

mysql> insert into Post(UserName,UserPostMessage)
   -> values('John',if(UserName IS NULL,DEFAULT(UserPostMessage),'Hello'));
Query OK, 1 row affected (0.21 sec)
mysql> insert into Post(UserName,UserPostMessage)
   -> values(NULL,if(UserName IS NULL,DEFAULT(UserPostMessage),'Hello'));
Query OK, 1 row affected (0.22 sec)
mysql> insert into Post(UserName,UserPostMessage)
   -> values('Carol',if(UserName IS NULL,DEFAULT(UserPostMessage),'Hello'));
Query OK, 1 row affected (0.14 sec)

Display all records from the table using a select statement. The query is as follows −

mysql> select *from Post;

The following is the output −

+----+----------+---------------------+
| Id | UserName | UserPostMessage     |
+----+----------+---------------------+
|  1 | John     | Hello               |
|  2 | NULL     | Hi Good Morning !!! |
|  3 | Carol    | Hello               |
+----+----------+---------------------+
3 rows in set (0.00 sec)

karthikeya Boyini

Updated on 30-Jul-2019 22:30:25

• Related Questions & Answers
• Insert NULL value into INT column in MySQL?
• Adding a column whose value is not null by default in MySQL?
• Java application to insert null value into a MySQL database?
• How to check whether column value is NULL or having DEFAULT value in MySQL?
• Display only NOT NULL values from a column with NULL and NOT NULL records in MySQL
• How to insert NULL keyword as a value in a character type column of MySQL table having NOT NULL constraint?
• Working with NULL and IS NOT NULL in MySQL
• How to insert NULL into char(1) in MySQL?
• Check for NULL or NOT NULL values in a column in MySQL
• Update one column data to another column in MySQL if the second column is NOT NULL?
• Sum if all rows are not null else return null in MySQL?
• Empty string in not-null column in MySQL?
• How to set default value to NULL in MySQL?
• Insert NULL value into database field with char(2) as type in MySQL?
• Removing NOT NULL restriction from column in MySQL?