带CASE的MySQL例行INSERT语句 [英] MySQL Routine INSERT Statement with CASE

问题描述

MySQL例程:

DELIMITER $$

CREATE DEFINER=`root`@`%` PROCEDURE `INSERT_Employee_Info`(
IN lname varchar(64),
IN fname varchar(64),
IN mname varchar(64)
)
BEGIN
INSERT INTO tbl_employee_info(lname, fname, mname)
VALUES(lname, fname, mname);
END

有时候，值lname，fname和mname可能不包含任何内容，但不能为null，我希望insert语句插入NULL而不是''值.

Sometimes, the values lname, fname and mname may contain nothing but not null, I want the insert statement to insert NULL instead of the '' value.

我想到了以下几点，但我高度怀疑它会起作用:

I had the following in mind but I highly doubt it will work:

INSERT INTO tbl_employee_info(lname, fname, mname)
VALUES(CASE lname WHEN '' THEN NULL ELSE lname END,
CASE fname WHEN '' THEN NULL ELSE fname END,
CASE mname WHEN '' THEN NULL ELSE mname END);

推荐答案

它可以工作，但是您需要在case

it will work but you need to put parenthesis around case

INSERT INTO tbl_employee_info(lname, fname, mname)
VALUES
(
   (CASE lname WHEN '' THEN NULL ELSE lname END),
   (CASE fname WHEN '' THEN NULL ELSE fname END),
   (CASE mname WHEN '' THEN NULL ELSE mname END)
);

• SQLFiddle演示(添加了IF替代)
• SQLFiddle Demo (added IF alternative)

这篇关于带CASE的MySQL例行INSERT语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！