Find_in_set returns an empty set?

Question

I have some questions about the set type in find_in_set here is the code:

create table set_test(id int,set_col SET('a','b','c','d'));
insert into set_test(id,set_col) values(1,'a,b'),(2,'a,b,b');
select * from set_test where find_in_set('a,b',set_col)

return empty set!!! why?

Answer 1

The documentation of function FIND_IN_SET():

FIND_IN_SET(str, strlist)

Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings. A string list is a string composed of substrings separated by “,” characters.

Its second argument is a string containing values separated by comma. The value you passed to FIND_IN_SET() (the column set_col having the type SET) matches this description. However, its first argument should be only one of the values contained in the list. a,b is not a valid value to pass as first argument to FIND_IN_SET() as it contains to values: a and b. You should pass it either 'a' or 'b' if you want it to find something.

This is also documented at the end of the paragraph which says:

This function does not work properly if the first argument contains a comma (“,”) character.