How to get rows which contains unknown values in SET column?

Question

Database is MySQL 5.6

CREATE TABLE set_t
(
set_r  SET ('a', 'b', 'c')
);

INSERT INTO set_t (set_r) VALUES ('a,b,c'), ('a,b');

In my case i know only 'a' and 'b'.

For example, need to select row where set_r is "a,b,c".

Value 'c' is unknown, cant use it in query.

Values order is unknown also. They are may be set_r SET ('c', 'b', 'a') or else.

How to select rows, which contains unknown values?

Answer 1

The easy way to do it for your sample data is with NOT IN:

SELECT *
FROM set_t
WHERE set_r NOT IN ('a', 'b', 'a,b')

But this does not scale well if you have larger set and you want to include more members than a and b in the list of known members.

Every member of a set is stored as a numeric value.
In your set the numeric values are:

SET Member  Decimal Value   Binary Value
'a'         1               001
'b'         2               010
'c'         4               100

So, a value like 'a,b' is stored as 3 (Binary 011)
and a value like 'a,b,c' is stored as 7 (Binary 111)

If you know the numeric values of the known members, which you can get by the order they are defined in the set, then you can use bit operations to get what you want:

SELECT *
FROM set_t
WHERE set_r | 3 <> 3 -- 3 is the numeric value of 'a,b'

or:

SELECT *
FROM set_t
WHERE set_r | 0b11 <> 0b11 -- 0b11 is the numeric binary value of 'a,b'

See the demo.

Answer 2

You can use find_in_set() to check for individual values. See SET documentation here

select * from set_t where find_in_set('a', set_r) and find_in_set('b', set_r)

This gives you

set_r
(a,b,c)
(a,b)

See this db<>fiddle

If you don't want the second row, you can add and not like 'a,b'.

Answer 3

The most efficient method is to use the binary comparisons. This assumes that you know the position of 'a' and 'b' in the set:

select *
from set_t
where (set_r | b'11') <> b'11';

You can also do this using string operations. This is a bit of a pain, because you need to handle the comma delimiter:

select *
from set_t
where replace(replace(replace(concat(',', set_r, ','), ',a,', ','), ',b,', ','), ',', '') <> ''

The logic is:

• Put delimiters at the beginning and end of set_r.
• Remove delimited elements, replacing them with the delimiter.
• Check if the result only has delimiters.

This version works regardless of the size of the set and the position of the elements you want to compare against.

Here is a db<>fiddle.