Hi,

mysql version is a 5.5.

my script is as follow:

Code:
CREATE TABLE mycol2(
mycol2_int INT AUTO_INCREMENT PRIMARY KEY,
col SET ('a','b','c','d'),
mycol2_str VARCHAR(10)
);

START TRANSACTION;

INSERT INTO mycol2(col, mycol2_str) VALUES ( 'a,b', 'a b combinations');

INSERT INTO mycol2(col, mycol2_str) VALUES ( 'a,b', 'a b combinations 2');

INSERT INTO mycol2(col, mycol2_str) VALUES ( 'b,c', 'b c combinations');

INSERT INTO mycol2(col, mycol2_str) VALUES ( 'c,d', 'c d combinations');

INSERT INTO mycol2(col, mycol2_str) VALUES ( 'a,d', 'a d combinations');
Code:
root@localhost[certify]>SELECT * FROM mycol2 WHERE col & 1;
+------------+------+--------------------+
| mycol2_int | col  | mycol2_str         |
+------------+------+--------------------+
|          1 | a,b  | a b combinations   |
|          2 | a,b  | a b combinations 2 |
|          5 | a,d  | a d combinations   |
+------------+------+--------------------+
3 rows in set (0.00 sec)
based on http://dev.mysql.com/doc/refman/5.5/en/set.html,

The following statements also are permitted:

mysql> SELECT * FROM tbl_name WHERE set_col & 1;
mysql> SELECT * FROM tbl_name WHERE set_col = 'val1,val2';
The first of these statements looks for values containing the first set member.
I understand why the above will display the above rows.

Code:
root@localhost[certify]>SELECT * FROM mycol2 WHERE col & 2;
+------------+------+--------------------+
| mycol2_int | col  | mycol2_str         |
+------------+------+--------------------+
|          1 | a,b  | a b combinations   |
|          2 | a,b  | a b combinations 2 |
|          3 | b,c  | b c combinations   |
+------------+------+--------------------+
the above display element b which is the second element, which I agree.

Code:
root@localhost[certify]>SELECT * FROM mycol2 WHERE col & 3;
+------------+------+--------------------+
| mycol2_int | col  | mycol2_str         |
+------------+------+--------------------+
|          1 | a,b  | a b combinations   |
|          2 | a,b  | a b combinations 2 |
|          3 | b,c  | b c combinations   |
|          5 | a,d  | a d combinations   |
+------------+------+--------------------+
4 rows in set (0.00 sec)
While I understand why it display b,c. I don't understand why display other elements.

Code:
root@localhost[certify]>SELECT * FROM mycol2 WHERE col & 4;
+------------+------+------------------+
| mycol2_int | col  | mycol2_str       |
+------------+------+------------------+
|          3 | b,c  | b c combinations |
|          4 | c,d  | c d combinations |
+------------+------+------------------+
2 rows in set (0.00 sec)
from the above I also don't understand why b,c appears

could anyone explain why?

thanks a lot!