1

I have a trigger that should convert empty strings to null for a tinyint(1) boolean column:

CREATE TRIGGER convertToNull
BEFORE INSERT ON MyTable
FOR EACH ROW
SET new.myBoolean = nullif(new.myBoolean, '');

The problem is, it also converts 0 to null.

I checked with:

mysql> select nullif(0, '');
+---------------+
| nullif(0, '') |
+---------------+
|          NULL |
+---------------+
1 row in set (0.00 sec)

How to make nullif stricter, such that ONLY empty strings will be converted to null?

seven11
  • 895
  • 1
  • 7
  • 14
  • What are you trying to do? A tinyint is never `''` – Bohemian May 08 '18 at 01:50
  • I want the tinyint to be either 0, 1 or NULL. So I'm attempting to convert empty strings to NULL when inserting rows from a tsv file. – seven11 May 08 '18 at 01:57
  • 1
    but it will never be an empty string. By the time the trigger fires, the conversion from blank to `0` will have already happened. What you're attempting can't done., – Bohemian May 08 '18 at 02:01
  • Oh I see, I was under the impression triggers fired before mysql did anything. – seven11 May 08 '18 at 02:05

0 Answers0