[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