自动将0或空字符串转换为NULL

[in MySQL]
Is there any way to constraint any field to not be 0, and if I got 0 value convert it to NULL.
For example, two products can't have the same serial number, but product can be without serial number at all.
I'm using right now this:(for few fields)

[在MySQL]有没有办法约束任何字段不是0,如果我有0值将其转换为NULL。例如,两个产品不能具有相同的序列号,但产品可能没有序列号。我正在使用这个:(少数几个领域)

CREATE TABLE products (
    sn DECIMAL(8,2),
    UNIQE (sn),
    ...........
) ENGINE=InnoDB;
CREATE TRIGGER prod_zero1 BEFORE UPDATE ON products FOR EACH ROW
    SET NEW.sn=NULLIF(NEW.sn,0);

Is there any shorter\faster way how to do it ?
Other problem is that when I try to use this for few fields in the same table, I got massege:

有没有更短\更快的方法怎么做?其他问题是,当我尝试在同一个表中的几个字段中使用它时,我得到了massege:

CREATE TRIGGER prod_zero2 BEFORE UPDATE ON products FOR EACH ROW
    SET NEW.name=NULLIF(NEW.name,"");
ERROR 1235 (42000): This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'

Thanks

2 个解决方案

#1

Do not use multiple triggers, use one trigger that takes care for all of the field

不要使用多个触发器,使用一个负责所有字段的触发器

CREATE TRIGGER prod_zero2 BEFORE UPDATE ON products FOR EACH ROW
BEGIN
SET NEW.sn=NULLIF(NEW.sn,0);
SET NEW.name=NULLIF(NEW.name,"");
END;

#2

You can do this in a single trigger.

您可以在一个触发器中执行此操作。

Here are the two columns from your example combined into one trigger. I just did an update triggers because that's what you had in your example, but you'll probably want another trigger to do the same thing for inserts, too.

以下是将您的示例中的两列合并为一个触发器。我只是做了一个更新触发器,因为这是你在你的例子中所拥有的,但是你可能还想要另一个触发器来为插入做同样的事情。

DELIMITER $$

DROP TRIGGER IF EXISTS tr_b_upd_products $$

CREATE TRIGGER tr_b_upd_products BEFORE UPDATE ON products FOR EACH ROW BEGIN
  IF (NEW.sn = 0)
  THEN
    SET NEW.sn=NULL;
  END IF;

  IF (NEW.name = '')
  THEN
    SET NEW.name=NULL;
  END IF;
END $$

DELIMITER ;

#1