Search code examples
mysqloperatorsboolean-expression

In MySQL, what does the '+' operator do in the context of a boolean expression?

For example:

    IF (SELECT ((new.fieldA IS NULL) + (new.fieldB IS NULL) + (new.fieldC IS NULL)) <> 2)
    THEN
        do some stuff
    END IF;

I am coming to MySQL from PostgreSQL. I came across this syntax (in the select statement) and am having a hard time figuring out what exactly the '+' operators are doing here.

Thanks

Solution

  • In this case, the boolean expressions will be treated as integers, with 0 representing false and 1 representing true. Then, your select:

    SELECT ((new.fieldA IS NULL) + (new.fieldB IS NULL) + (new.fieldC IS NULL)) <> 2

    will be true if only one of the three A, B, C fields is non null, with the other two being null. If all three are null, all are not null, or two are not null, then the condition would be false.