如果值为null，则将default插入到not null列中 [英] Insert default into not null column if value is null

问题描述

我有一个表foo，其中有一个NOT NULL列，其默认名为message:

I have a table foo, which has a NOT NULL column with a default called message:

CREATE TABLE foo(
    id int PRIMARY KEY,
    message varchar(64) NOT NULL DEFAULT 'Hello'
)

有一个存储过程bar插入到foo中:

There is a stored procedure bar that inserts into foo:

CREATE PROCEDURE bar(
    i_id int,
    i_message varchar(64)
)
BEGIN

    -- other logic

    IF i_message IS NOT NULL THEN
        INSERT INTO foo (id, message) VALUES (i_id, i_message);
    ELSE
        INSERT INTO foo (id, message) VALUES (i_id, DEFAULT);
        -- could also be: INSERT INTO foo (id) VALUES (i_id);
    END IF;
 END;

您可以看到，如果i_message为null，我必须有条件地分支以便我的插入使用默认值.这仅适用于一列，但请考虑foo是否有更多的NOT NULL DEFAULT列-语法可能很笨拙.

You can see, I have to conditionally branch in order for my insert to use the default if i_message is null. That's fine for just one column, but consider if foo had many more NOT NULL DEFAULT columns - the syntax would be way to unwieldy.

是否可以使用一种语法指定插入的值(如果为null)应回退到默认值?我尝试了以下操作，但可以理解的是语法错误:

Is there a syntax I can use to specify that an inserted value should fall back to the default if null? I tried the following but understandably got syntax errors:

INSERT INTO foo (id, message) VALUES (i_id, COALESCE(i_message, DEFAULT));
INSERT INTO foo (id, message) VALUES (i_id, IFNULL(i_message, DEFAULT));

推荐答案

  INSERT INTO foo (id, message) 
  VALUES
  (i_id, IFNULL(i_message,DEFAULT(message)));

这篇关于如果值为null，则将default插入到not null列中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！