Curtis Maurand wrote:
> :-)
>
> someflag enum('TRUE','FALSE');
>
> Not quite boolean, but it works.
>
> Curtis
>
> On Wed, 3 Mar 2004, Mark Warner wrote:
>
>
>>The thing which bothers me most about MySQL is the lack of a proper
>>boolean. I don't like having to abstract a tinyint(1) into true or
>>false. As much of my work involves building applications with Yes or No
>>questions, I think I am switching to PostgreSQL.
<SNIP>
To my mind, a boolean is something that can be used in a boolean
context. That is, if flag is a boolean, the following should work as
expected:
SELECT * FROM mytable WHERE flag; # rows with flag = TRUE
SELECT * FROM mytable WHERE NOT flag; # rows with flag = FALSE
If you have to compare the column's value to something, it isn't really
a boolean value.
MySQL, like most programming languages, treats 0 as FALSE and any other
number as TRUE. That means that boolean expressions are evaluated
numerically and compared to 0.
In a numeric context, ENUM columns return the value's position in the
list of allowed values, starting with 1. This means that with the
definition
someflag enum('TRUE','FALSE')
someflag evaluates as 1 when it is 'TRUE' and 2 when it is 'FALSE', both
of which are TRUE in boolean context. Hence, you cannot use someflag in
boolean context and get the expected results. In other words, someflag
looks like a boolean if you view the data, but doesn't behave as a
boolean in queries.
I don't quite know what Mark Warner means by "abstract a tinyint(1) into
true or false," but I personally find using tinyint for boolean to be a
simple solution. I define "someflag TINYINT", then set it to 0 for
FALSE and 1 (or any other number) for true. Of course, if you're not a
programmer it won't look like a boolean when viewing the data (is that
what you don't like, Mark?), but it will behave as one.
With tinyint you get some added flexibility, which may be an advantage,
depending on your application. For example, if I store the number of
children a person has in the tinyint kids, I can use kids in boolean
context:
SELECT * FROM persontable WHERE kids; #people who have children
SELECT * FROM persontable WHERE NOT kids; #people who don't
One more advantage of tinyints is that, with common programming
languages, they continue to function as booleans in your application.
Another option is to take advantage of the special error value in every
ENUM. If you insert a wrong value, mysql replaces it with '', which is
always 0 in numeric context. So, you could define
someflag ENUM ('TRUE', 'T');
If you insert 'FALSE' or 'F' (or 'false' or 'f') into someflag, it will
get the error value, 0 or ''. When you view the data, you will see
'TRUE' or 'T' for TRUE values and blanks for FALSE, and someflag will
behave as boolean in queries.
If your values are 'Y' and 'N', then, strictly speaking, you don't have
a boolean unless you've defined which is TRUE and which is FALSE.
Assuming you want to treat 'Y' as TRUE, you could define
yesflag ENUM ('YES', 'Y');
Inserting 'YES' or 'Y' (or 'yes' or 'y') will get you the corresponding
entries, which are TRUE in boolean context. Inserting 'NO' or 'N' (or
'no' or 'n') will get you blanks, which are FALSE in boolean context.
Michael
