Exclude entries with empty binary column data

Question

I have a column with type binary(16) not nullin mysql table. Not all records have data in this column, but since it sets a ban on them NULL, such records have an all-zero value.

I want to exclude these all null entries from the request.

So far, the only solution I can find is HEXvalue and comparison:

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

which works, but is there a better way?

Answer 1

To match a type binaryusing a literal, use \0or \1for bits.

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

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

. SQLFiddle.

(, ) , , . , , .

Answer 2

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

, Bohemians , , (, , - , ).

Answer 3

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

where N is the length of the UUID column. Not beautiful, but it works.