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?

Frontier

Have you tried select uuid from example where uuid is not null? – Selfabasement

These columns were never setup to allow NULL. – Frontier

when you look at the database, do you see blank in the column for these records or null? – Selfabasement

@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... ;-) – Asch

Answer 1

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.

Premier

This is even prettier. (whispers and sorry for mentioning Gordon in my comment above ;-)) – Asch

I'm just curious: Won't where uuid!=cast('' as binary(16)); use an index? I mean, it's a constant after all. – Asch

Answer 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).

Crispen

Answer 3

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

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

Asch