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?

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.

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