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

Published on 27-Feb-2019 08:06:49

Previous Page Print Page