Michael,
I couldn't agree more! I didn't even realize there was a boolean issue
until now. I don't really understand the issue of using 0 as false, and
1 as true. The way I view it is that "true and false" are merely
abstract names for 0 and 1. When the database is accessed using Java, I
don't even see the 0 and 1. For example, using the JDBC API,
pStmt.setBoolean(1, true) command sets the field to 1, and
rs.getBoolean('booleanField') returns true if 1, and false if 0.
So again, I reiterate, what's the issue?
Kevin
On Wed, 2004-03-03 at 11:31, Michael Stassen wrote:
> 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
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
