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 question | James Manning | 10 Sep |

• Re: SET question | Martin Ramsch | 10 Sep |

• daily report | Manuel Coral A. | 11 Sep |