Exclude records with empty binary column data

Question

I have a column with type binary(16) not null in a mysql table. Not all records have data in this column, but because it setup to disallow NULL such records have an all-zero value.

I want to exclude these all-zero records from a query.

So far, the only solution I can find is to HEX the value and compare that:

SELECT uuid
FROM example
WHERE HEX(uuid) != '00000000000000000000000000000000'

which works, but is there a better way?

Have you tried `select uuid from example where uuid is not null`? — Rahul Sharma, Mar 08 '16 at 20:16
when you look at the database, do you see blank in the column for these records or null? — Rahul Sharma, Mar 08 '16 at 20:19
@RahulSharma According to the docs `binary` columns are always padded with `\0`. So inserting an empty string `''` gives N times `\0`. I think that's the problem here. Nice riddle, though. Let's wait for Gordon... ;-) — PerlDuck, Mar 08 '16 at 20:20

Bohemian · Accepted Answer · 2016-03-08T20:40:43.887

5

To match a binary type using a literal, use \0 or \1 for the bits.

In your case with a binary(16), this is how to exclude only zeroes:

where uuid != '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0'

See SQLFiddle.

The advantage of using a plain comparison with a literal (like this) is that an index on the column can be used and it's a lot faster. If you invoke functions to make the comparison, indexes will not be used and the function(s) must be invoked on every row, which can cause big performance problems on large tables.

edited Mar 08 '16 at 20:40

answered Mar 08 '16 at 20:27

Bohemian

412,405
93
575
722

This is even prettier. (_whispers_ and sorry for mentioning Gordon in my comment above ;-)) – PerlDuck Mar 08 '16 at 20:49
I'm just curious: Won't `where uuid!=cast('' as binary(16));` use an index? I mean, it's a constant after all. – PerlDuck Mar 08 '16 at 21:00

Francesco de Guytenaere · Answer 2 · 2016-03-08T20:54:16.727

2

SELECT uuid FROM example WHERE TRIM('\0' FROM uuid)!='';

Note that Bohemians answer is a lot neater, I just use this when I am not sure about the length of the field (Which probably comes down to bad design on some level, feel free to educate me).

edited Mar 08 '16 at 20:54

answered Mar 08 '16 at 20:31

Francesco de Guytenaere

4,443
2
25
37

PerlDuck · Answer 3 · 2016-03-08T20:50:08.063

0

select uuid from example where uuid!=cast('' as binary(N));

where N is the length of the UUID column. Not pretty, but working.

edited Mar 08 '16 at 20:50

answered Mar 08 '16 at 20:30

PerlDuck

5,610
3
20
39

Exclude records with empty binary column data

3 Answers3