[thelist] A SQL request on a SET() field type

rudy r937 at interlog.com
Mon Jul 23 09:03:26 CDT 2001


> It's been a long time since I went near set or enum types,
> as they only cause pain.

oh, a SET type (had to look it up)

thanks, seb, for the gentle correction

but according to the manual, instead of

    WHERE 'UK' IN mysetfield;

the syntax is

    WHERE mysetfield LIKE '%UK%'

or

    WHERE FIND_IN_SET('UK',mysetfield)>0

however, damien was asking about the case where there are two values to
test against

suppose the set field is defined by  SET("FR","GB", "UK")

then  testing for the single value UK as above will evaluate true for any
of the following values of myseltfield

   "UK"
   "FR,UK"
   "GB,UK"
   "FR,GB,UK"

however, testing for both FR and UK is a lot trickier

first of all you have to ask yourself whether you mean 
FR OR UK, or FR AND UK

and if the latter, then you also have to ask yourself if you mean 
FR AND UK AND NOT GB, which is what you would with

    WHERE mysetfield = 'FR,UK'

me, i wouldn't use an equality test to distinguish between

   "FR,UK"
   "FR,GB,UK"

simply because it's too obscure -- casual readers of the source 
code will not see the difference

rather, i'd use 

   WHERE FIND_IN_SET('FR',mysetfield)>0
         AND FIND_IN_SET('UK',mysetfield)>0
      AND NOT FIND_IN_SET('GB',mysetfield)>0

and omit the NOT GB condition if that's what the situation calls for


by the way, seb's right, damien, about first normal form

you would never, ever, ever use a set type as a foreign key

however, i can certainly see the usefulness of this type of field

and one of the nice things about knowing data modelling rules 
is also knowing when to break them

that FIND_IN_SET function looks mighty, mighty handy 
if the values never need to be joined to anything -- ever



rudy








More information about the thelist mailing list