>Hi again Bob,
>
> >>I know it's not 'proper' database design and I should have
> >>normalized the one-to-many relationships to multiple tables.
> >>However, it was fast and gave me a result which worked quickly
> >>(and up against a mad deadline).
> >
> >I think you've just identified the most common use of the SET datatype.
> >
> >In your original posting, you said that a procedural solution
> >wouldn't scale well. If you database will never be more than 5,000
> >records, why are you concerned about scalability? Are you planning on
> >processing a hundred concurrent queries?
>
>I meant scalability in terms of the number of items in the SET
>(not the number of rows in the table). For example, consider a
>SET field with 26 items -
>eg. set_col SET ('A','B','C'..... 'Z')
>
>If I wish to find out which values of the SET have been used
>at least once, I have to either:
>a) Query for each member of the SET and find out which gives a result:
> SELECT * FROM my_table WHERE set_col LIKE ('%A%')
> SELECT * FROM my_table WHERE set_col LIKE ('%B%')
> etc.
>b) Select DISTINCT(set_col) and then parse the results
> for for each member of the set .. which is a worse method.
>
>I was hoping that since each member of the SET really
>represents a number, that there would be some bitwise
>operation that would painlessly produce all the members
>of the SET that appear at least once.
Sir, I apologize for taking so long to respond. I was moving some
furniture this weekend and dropped a desk on my keyboard.
You are right about SETs, and I am blind. The obvious solution is the
bitwise or. MySQL has a summary function called Bit_Or().
Unfortunately, it returns a decimal representation of the result, so
you have to massage it a little.
SELECT Bin(Bit_Or(column_name)) FROM your_table;
The bits are in reverse order; if your SET declaration includes a, b,
c, and d, and you columns include all but b, you'll get 1101 from the
SELECT statement. This is fine if you're going to process the bits in
your client. But if you want to display the bits in MySQL in the same
order as your SET declaration, you can use
Reverse(Binary(Bin(Bit_Or(column_name))))
which will give you 1011.
Bob Hall
Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
