Re: AW: SELECT IN

[Rene Fertig <rene_(at)_renux.de>]

Am Mit, 10 Jan 2001 schrieb Gloss Mathias:
> Hallo,
> 
> > ----------
> > Von: 	Christian Dassow[SMTP:c.dassow_(at)_webelefant.de]
> > 
> > select * from tabelle where:
> > >     subscription = '2' OR
> > >     subscription like '2,%' OR
> > >     subscription like '%,2' OR
> > >     subscription like '%,2,%'

Mal als Denkanstoss ein Auszug aus dem Manual:

7.2.7.4 The SET type

A SET is a string object that can have zero or more values, each of which must be chosen from a list of allowed values specified
when the table is created. SET column values that consist of multiple set members are specified with members separated by commas
(`,'). A consequence of this is that SET member values cannot themselves contain commas. 

For example, a column specified as SET("one", "two") NOT NULL can have any of these values: 

""
"one"
"two"
"one,two"

A SET can have a maximum of 64 different members. 

MySQL stores SET values numerically, with the low-order bit of the stored value corresponding to the first set member. If you
retrieve a SET value in a numeric context, the value retrieved has bits set corresponding to the set members that make up the column
value. If a number is stored into a SET column, the bits that are set in the binary representation of the number determine the set
members in the column value. Suppose a column is specified as SET("a","b","c","d"). Then the members have the
following bit values: 

[...]

If you assign a value of 9 to this column, that is 1001 in binary, so the
first and fourth SET value members "a" and "d" are selected and the
resulting value is "a,d". 

For a value containing more than one SET element, it does not matter what order the elements are listed in when you insert the
value. It also doesn't not matter how many times a given element is listed in the value. When the value is retrieved later, each
element in the value will appear once, with elements listed according to the order in which they were specified at table creation
time. For example, if a column is specified as SET("a","b","c","d"), then "a,d", "d,a" and "d,a,a,d,d" will all
appear as "a,d" when retrieved. 

SET values are sorted numerically. NULL values sort before non-NULL SET values. 

Normally, you perform a SELECT on a SET column using the LIKE operator or the FIND_IN_SET() function: 

mysql> SELECT * FROM tbl_name WHERE set_col LIKE '%value%';
mysql> SELECT * FROM tbl_name WHERE FIND_IN_SET('value',set_col)>0;

But the following will also work: 

mysql> SELECT * FROM tbl_name WHERE set_col = 'val1,val2';
mysql> SELECT * FROM tbl_name WHERE set_col & 1;

The first of these statements looks for an exact match. The second looks for values containing the first set member. 

If you want to get all possible values for an SET column, you should use: SHOW COLUMNS FROM table_name LIKE
set_column_name and parse the SET definition in the second column. 
----

Wenn das ominöse Subscription Feld nicht mehr als 64 verschiedene Einträge
hat, kann man das mit einem Set recht elegant machen.

Ansonsten kann die Funktion find_in_set recht hilfreich sein:

FIND_IN_SET(str,strlist) 
    Returns a value 1 to N if the string str is in the list strlist consisting of N substrings. A string list is a string composed of
    substrings separated by `,' characters. If the first argument is a constant string and the second is a column of type SET, the
    FIND_IN_SET() function is optimized to use bit arithmetic! Returns 0 if str is not in strlist or if strlist is the
    empty string. Returns NULL if either argument is NULL. This function will not work properly if the first argument contains a
    `,'. 

    mysql> SELECT FIND_IN_SET('b','a,b,c,d');
            -> 2

Ciao, Rene

-- 
----------------------------------------------------------------------
     mailto:rene_(at)_renux.de                     http://www.renux.de
----------------------------------------------------------------------

---
*** Weitere Infos zur Mailingliste und MySQL unter http://www.4t2.com/mysql