如果 MySQL 中的值为空，是否将默认值插入非空列?
mysqlmysqli database
您可以使用 IFNULL() 属性或带有 IS NULL 属性的简单 IF()。语法如下 −
INSERT INTO yourTableName(yourColumnName1,yourColumnName2)
VALUES('yourValue’',IF(yourColumnName1 IS NULL,DEFAULT(yourColumnName2),'yourMessage'));
为了理解上述语法，让我们创建一个表。创建表的查询如下 −
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)
现在，如果值为空，则可以将默认值插入到非空列中。查询如下 −
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)
使用 select 语句显示表中的所有记录。查询如下 −
mysql> select *from Post;
以下是输出 −
+----+----------+---------------------+ | Id | UserName | UserPostMessage | +----+----------+---------------------+ | 1 | John | Hello | | 2 | NULL | Hi Good Morning !!! | | 3 | Carol | Hello | +----+----------+---------------------+ 3 rows in set (0.00 sec)
