List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:September 10 1999 9:30pm
Subject:Re: SET question
View as plain text  
On Fri, 1999-09-10 15:30:11 -0400, James Manning wrote:
> If this is covered in the manual and I just overlooked it, please
> feel free to just point me to a section :)

Hah, for a change I won't try that, but answer immediately! ;-)

Let's make an example and just try ...

mysql> CREATE TABLE tmp ( x SET('a','b','c') );
mysql> INSERT INTO tmp VALUES (0),(1),(2),(3),(4),(5),(6),(7);
mysql> SELECT * FROM tmp;
+-------+
| x     |
+-------+
|       |
| a     |
| b     |
| a,b   |
| c     |
| a,c   |
| b,c   |
| a,b,c |
+-------+

Okay.

> 1) Can SET columns add members to the set and it will take the next MSB
>    in the 64-bit field?  ie (a,b,c) gets altered to (a,b,c,d) and the first
>    three will stay at their respective positions and d will simply take
>    bit 3 (fom a big-endian POV) for future queries?

mysql> ALTER TABLE tmp MODIFY x SET('a','b','c','d');
mysql> SELECT * FROM tmp;
+-------+
| x     |
+-------+
|       |
| a     |
| b     |
| a,b   |
| c     |
| a,c   |
| b,c   |
| a,b,c |
+-------+
Yes, first three elements stay at their respective positions.

mysql> INSERT INTO tmp VALUES (8);
mysql> SELECT * FROM tmp WHERE x=8;
+------+
| x    |
+------+
| d    |
+------+
Yes, d takes bit 3 (from a big-endian point of view).

> 2) Can members of a SET be renamed and match the same bit?
>    ie can (a,b,c,d) get altered to (a,b,foo,d) and both c (previous)
>    and foo (current) are bit 2 (big-endian)?

mysql> ALTER TABLE tmp MODIFY x SET('a','b','foo','d');
mysql> SELECT * FROM tmp;
+------+
| x    |
+------+
|      |
| a    |
| b    |
| a,b  |
|      |
| a    |
| b    |
| a,b  |
| d    |
+------+
UPS -- element 'c' has been deleted!  Quite logical, if I think about,
because so each single element keeps its identity.

So renaming most probably can't be done with ALTER.

But copying to a second table works, if the set values are
inserted as numerical values:
mysql> CREATE TABLE tmp2 ( x SET('a','b','foo','d') );
mysql> INSERT INTO tmp2 SELECT FLOOR(x) FROM tmp;
mysql> SELECT * FROM tmp2;
+---------+
| x       |
+---------+
|         |
| a       |
| b       |
| a,b     |
| foo     |
| a,foo   |
| b,foo   |
| a,b,foo |
| d       |
+---------+


> 3) What's the desired method for finding entries with either 
>    a) all members present of a given set, 
>       like FIND_IN_SET(set_col,'a' AND 'b' AND 'c');
>    b) any members present of a given set, 
>       like FIND_IN_SET(set_col,'a' OR 'b' OR 'c');
>    I know I could do these with compound WHERE's, but I get the feeling
>    (perhaps incorrectly) that doing the checking "further down" would
>    result in a speed increase.

Using the numerical values of sets and bit arithmetic is most
probably faster:
mysql> SELECT * FROM tmp WHERE (x & 7) = 7;
+-------+
| x     |
+-------+
| a,b,c |
+-------+
mysql> SELECT * FROM tmp WHERE (x & 7) > 0;
+-------+
| x     |
+-------+
| a     |
| b     |
| a,b   |
| c     |
| a,c   |
| b,c   |
| a,b,c |
+-------+


Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
SET questionJames Manning10 Sep
  • Re: SET questionMartin Ramsch10 Sep
    • daily reportManuel Coral A.11 Sep