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]