Re: enum vs tinyint

[Margaret MacDonald]

If you're using a general language such as C or PHP as a front end to mysql, I'd use option 1 (and in fact I do!! :-)

I tried using the built-in enum, but it's treated in a way that's unlike the enum construct in other languages.

Given some enum FOO defined as ('bar', 'mumble', 'fratz', 'gorp'), you can't query WHERE FOO < 'fratz' and expect to get all records where FOO is either 'bar' or 'mumble'. MySQL does a *string* comparison--it doesn't map the strings onto their positional values first and then compare those integers, as other languages do. So you would only get records where FOO is 1 ('bar') even though 'fratz' maps to 3 and 'mumble' maps to 2.

It's a very strange and, to me, unsatisfactory way to do things.